One Star

execute sql

I've seen several request for this, but you guys seem resistent. We need a companent to execute some generic sql (more than one statement) after moving and massaging data. It does not need to be with each row and has nothing to do with the dataflow. Its just SQL that need to run at that time in the flow. I con't use tMysqlRow because there is no row. After this is executed I need to perform other actions. can you guys just give us a generic sql execute function. Virtually all etl tools have these and I've used them all.
11 REPLIES
Community Manager

Re: execute sql

Hello
What about the iterate link, it based on the time in each flow. for example:
tMysqlInput-->tLogRow
|
iterate
|
tMysqlRow--OnComponentOk-->do other action
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: execute sql

I've seen alot of work arounds for this. It seems like it would be yery easy to make a component just to executes a SQL string and takes parameters. Do you know of anyone that might have created one. Can I request a component?
The work arounds are good, but when some one looks at it, it isn't really representative of what it does and defeats the pupose having a graphical representation.
Sorry to whine. This is a very good tool. If it had this one capability it would really sove the problem of doing special massaging of data after it is loaded. Thanks!
Four Stars

Re: execute sql

I agree datarch.
I've run into several instances where this would be useful to have. So far, I've been able to work around it as others do by breaking up jobs into steps. After a load point, just put the lines of SQL that I want to run in a tMSSQLRow and it executes OnSubjobOk or OnComponentOk, even though it's not processing "rows". Usually to do some drops, truncates, etc. on temp tables, drop/recreate indexes...
But again, it's a workaround. I'm sure there will come a time when the workaround won't really be possible or fit into the job at hand.

- Brian
One Star

Re: execute sql

I agree, I start a job by truncating three tables. I have to put three tmysqlrow together. I would like to run all commands in one componet. Is there a better way to do what to do this?
One Star

Re: execute sql

No better way at the moment.
One other way to do these types of things would be to put them in a Stored Procedure and and call that.
I, too, find myself using tDB2SQLRow(s) as a standalone "Do some SQL" via sub-jobs and linked w/OnSubjobOK.
It's gotten so that I don't even think about it that much anymore. It used to bother me, too, in the beginning.
Community Manager

Re: execute sql

Hello guy
Can you report a new feature on our bugtracker?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: execute sql

I agree that the functionallity would be very useful and should behave very much like tJava component with the SQL parameters included and the SQL statement to execute. In the meantime the workaround works quite well. If you have multiple statements to execute just terminate each with a semicolon ";" and type the next.
Steve
One Star

Re: execute sql

Hello together,
if Steve's solution works, what would be the difference to tMySqlRow?
I had the same problem and using tMySqlRow works very fine. Yes I use three in a row too but a Steve's solution could solve this.
Even if the part "Row" is in the componentname the behavior depends on the designed job, may be a change to the possible links could be good (i did not take a look on the actual possible ones)
By the way: In another scenario where I have much more statements to execute I solve it the following way:
tFileInputRegex -> tMySqlRow
With this you can externalize (and centralize) your sql
Bye
Volker
One Star

Re: execute sql

Does someone have a working example of how I might implement the
tFileInputRegex -> tMySqlRow flow?
I've looked out in the components resource guide and there is no good example to demonstrate this flow.
In particular, I'm confused on how each line of the file (in the tFileInputRegex) is passed to the tMySqlRow. Is it an iteration? How is it done? Please show an example.
Thanks,
Community Manager

Re: execute sql

Hello bbeliaso
In particular, I'm confused on how each line of the file (in the tFileInputRegex) is passed to the tMySqlRow. Is it an iteration?

Yes, you can use a tFlowToIterate component, eg:
tFileInputRegex --row1---tFlowToIterate--iterate--> tMySqlRow
on tMysqlRow, you could get value of each column of tFileInputRegex like this: (String)globalMap.get("row1.columnName")
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: execute sql

Hi together,
This should be working too without the tFlowToIterate. But you must change the code to get the data.
I think it is something like: row1.myColumnWithSql
Bye
Volker