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

One Star

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

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

Accepted Solutions

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

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

All Replies
Community Manager

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

Hello
Iterate each table and truncate each one on tMysqlRow. for example:
tMysqlTableList--iterate---tMysqlRow
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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.
Four Stars

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

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?
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

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

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

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

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

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.
One Star

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

@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

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
One Star

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

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 Smiley Happy
Thanks again
Four Stars

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

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!
Seven Stars

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

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.
Four Stars

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

It worked!

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

that guy rbaldwin is creepy good.
Community Manager

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

thanks rbaldwin for giving us the solution! It is very simple.
Best regards
sHONG
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Seventeen Stars

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

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.
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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