[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
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
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 ;
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.
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
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.
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
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.
