topic Re: [resolved] mysql component to execute multiple sql statements at once in Talend Studio

[resolved] mysql component to execute multiple sql statements at once

Anonymous — Sat, 16 Nov 2024 13:29:49 GMT

Hi,
I have a bunch of tables I want to truncate, is there a mysql component I can call where I can truncate all the tables at once? Something like executing the truncate statement in a script:
truncate table A;
truncate table B;
....
I know I can use tMysqlOutput or tMysqlRow, but I would need to create one component per table.
Thanks
Sarah

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Mon, 22 Mar 2010 04:07:03 GMT

Hello
Iterate each table and truncate each one on tMysqlRow. for example:
tMysqlTableList--iterate---tMysqlRow
Best regards
Shong

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Mon, 22 Mar 2010 17:16:37 GMT

Thanks shong (you are quick with replies, much appreciated!)
This works for straightforward cases but the sqls I want to run any sql, not constrained by a single table. What I am trying to do here is to 're-initialize' a warehouse. To do so, for example, I need to truncate tables in certain order due to FK constraint, insert some seed data after truncate, call some stored procedures, etc. I don't think tMysqlTableList can do the trick.
Here's what I came up with, it's a bit ugly but it seems to work. Any suggestion on this is welcome.
The flow looks like this:
tFixedFlowInput --> tJavaFlex --> tMysqlRow
1. In the FixedFlowInput
a. create a dummy column
b. specify the number of rows - in this case the number of sql you want to execute
2. In tJavaFlex
a. Start code - put all your sql in a HashMap in the order you want to execute: example:
java.util.HashMap<Integer,String> sqlList = new java.util.HashMap<Integer,String>();
Integer index = 1;
sqlList.put(1,"TRUNCATE TABLE table1");
sqlList.put(2,"TRUNCATE TABLE table2");
sqlList.put(3,"<whatever sql>");
b. In the main code - put the current sql in a globalmap
globalMap.put("current_exec_sql",sqlList.get(index));
index++;
3. In tMysqlRow
a. In the query: (String)globalMap.get("current_exec_sql")

The good thing about this is that only one connection is opened for all sqls and you can basically specify any sql your want. The bad thing is, it's quite ugly.

Re: [resolved] mysql component to execute multiple sql statements at once

jkrfs — Fri, 15 Apr 2011 19:48:37 GMT

I am trying to do the same, but something more complex like this:

"CREATE TEMPORARY TABLE temp_security_id
AS SELECT DISTINCT security_id FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId +
" DELETE FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId +
" DELETE FROM security 
WHERE id IN (SELECT security_id FROM temp_security_id)
DROP TABLE temp_security_id"

However, its complaining about the syntax/mysql version. I am able to execute this outside of talend, but it does not work when using mysqlrow.
Any idea how I can do this?

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Mon, 18 Apr 2011 05:09:23 GMT

Hi jkrfs
tMysqlRow don't support multiple statement at a time, I suggest to create a store procedure and call the store procedure with tMysqlSP component.
Best regards
Shong

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Mon, 18 Apr 2011 15:58:47 GMT

you could also wrap your multiple statements in a BEGIN/END block to avoid having to use multiple tMysqlRow components or a SP.

Re: [resolved] mysql component to execute multiple sql statements at once

jkrfs — Wed, 20 Apr 2011 12:45:49 GMT

I currently chained 4 tMysqlRow statements to be able to do what I quoted above. However, I'd love to see an example of your method user JohnGarettMartin.
thanks

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Wed, 20 Apr 2011 15:23:19 GMT

in a tMysqlRow component:

"
BEGIN
CREATE TEMPORARY TABLE temp_security_id
AS SELECT DISTINCT security_id FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId + ";
DELETE FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId + ";
DELETE FROM security 
WHERE id IN (SELECT security_id FROM temp_security_id);
DROP TABLE temp_security_id;
END;"

you may need to adjust your syntax, but the general idea is that you wrap your statements with BEGIN/END to make them a single compound statement, allowing you to issue multiple statements with a single Row component.
The same strategy works with other databases as well.

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 05 May 2011 15:15:55 GMT

@JohnGarrettMartin
I can't make this work. I have also tried this in phpmyadmin but it stil fails.
Does this need a specific mysql version??
This is my query:
BEGIN
ALTER TABLE thetable ADD INDEX (revision);
ALTER TABLE thetable ADD INDEX (id);
END;
I just get "There is an error near... " nothing that indicates the error.
My current mysql version is: 5.1.41

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 05 May 2011 16:36:43 GMT

Try this, I use it for mutiple SQL statement's
NOTE: SQL statements must be one line long.
eg.
SELECT *
FROM Test;
must be
SELECT * FROM Test;
Components:
tForEach -- iterate --> tMysqlRow

Code For tMysqlRow:

((String)globalMap.get("tForeach_1_CURRENT_VALUE"))

Don't Forget to change tForeach number to match component number.
EG.
tForeach_1_CURRENT_VALUE
tForeach_2_CURRENT_VALUE
tForeach_3_CURRENT_VALUE
Sorry also to add. In your tMysqlConnection - Advanced Settings - Tick AutoCommit

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 05 May 2011 18:14:38 GMT

Cool.
Thanks for this. Since I couldn't come to a solution using talend components, I used the tJava component and created code do perform the queries
Thanks again

Re: [resolved] mysql component to execute multiple sql statements at once

jkrfs — Wed, 11 May 2011 20:33:30 GMT

Tried this

DELIMITER $$
BEGIN
CREATE TEMPORARY TABLE temp_security_id
AS SELECT DISTINCT security_id FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId + ";
DELETE FROM trade_import
WHERE client_trade_import_id = " + context.clientTradeImportId + ";
DELETE FROM security
WHERE id IN (SELECT security_id FROM temp_security_id);
DROP TABLE temp_security_id;
END
$$
DELIMITER ;

With and without delimiter, but nothing!

Re: [resolved] mysql component to execute multiple sql statements at once

rbaldwin — Wed, 11 May 2011 22:09:57 GMT

It's actually much simpler than you're making it. The reason JohnGarrettMartin's approach - and many of the others - is because by default the mysql jdbc driver does two things:
1. Explicitly doesn't allow multiple queries in a single statement.
2. Stops executing after the first semi-colon ;.
It does this to alleviate the probability for SQL Injection. However, a configuration parameter allows you to get around this default behavior. Set your "Additional JDBC Parameters" to include "allowMultiQueries=true"
From there, you can simply put all of the queries you wish into the same tMysqlRow component.

See the screenshots attached below.

Re: [resolved] mysql component to execute multiple sql statements at once

jkrfs — Wed, 11 May 2011 22:20:14 GMT

It worked!

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 12 May 2011 03:10:34 GMT

that guy rbaldwin is creepy good.

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 12 May 2011 05:04:47 GMT

thanks rbaldwin for giving us the solution! It is very simple.
Best regards
sHONG

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 25 Apr 2013 16:10:15 GMT

hi,
urgent please
i created a tMysqlRow and i also added the "Additional JDBC Parameters" to include "allowMultiQueries=true" but when i run the job only one query is getting updated i have a list of query please help me to resolve this issue

thanks
caba

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Thu, 25 Apr 2013 20:46:03 GMT

Use the component tSQLScriptParser from Talend Exchange. This component is dedicated to run multiple SQL statements from a file or from the build-in SQL text field and use ONE tXXXXRow component do run each statement.
You can - unlike all Talend components - comment statements out with line comments and it understand also some SQLPlus commands like the / at the line start to end complex function statements.

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Fri, 26 Apr 2013 03:18:37 GMT

hi,
urgent please
i created a tMysqlRow and i also added the "Additional JDBC Parameters" to include "allowMultiQueries=true" but when i run the job only one query is getting updated i have a list of query please help me to resolve this issue
thanks
caba

Can you please show us your query?
Shong

Re: [resolved] mysql component to execute multiple sql statements at once

Anonymous — Fri, 26 Apr 2013 16:38:50 GMT

hi Shong,
I am not able to share the query since it is production one
but the query's look as below with the tMysqlRow with properties "Additional JDBC Parameters" to include "allowMultiQueries=true"
i have enclosed the list of querys in double quotes.

"create table query;
insert query;
update query;
update query;
update query;
update query;
update query;
"
only one query is getting executed please help me to resolve this issue.

Thanks
caba