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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[jkrfs]

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?

[Anonymous]

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

[Anonymous]

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

[jkrfs]

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

[Anonymous]

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.

[Anonymous]

@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

[Anonymous · Accepted solution]

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