Talend: Run batch sql statements from internal resource script missing. The alternative: tSQLScriptParser

When you need to run multiple sql statements you commonly use sql scripts that are supplied to the SQL Engine. The Engine is optimized to digest those in one or multiple "batches". There is a solution for MySQL as the tMySQLRow component supports batching through the option "allowMultipleQueries". Not for tSqliteRow or other other components though.

From Talend we could invoke the Engine directly but at the time of this writingTalend lacks support for internal resources definition..

A custom component exists though, called tSQLScriptParser which can provide a work around. Here is how:

1. Download the component and install it.
2. Define your sql inside "SQL Script" field, for example for a sqlite table called person:

   "drop table if exists person;
   create table person (name varchar(32));
   insert into person values('Foo');
   insert into person values('Bar');"
   

3. Connect "iterate" output to a t{DB}Row component. In the picture as you can see we use tSqliteRow
   
4. In the SQL field for the t{DB}Row use the below:

   ((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL"))
   

5. Run it and you can confirm the statements have run. For our example:

   $ echo "select * from person;" | sqlite3 /tmp/sqlite_test.db
   Foo
   Bar