How to execute multiple sql statements in Talend

Firstly, Why would you want to execute multiple SQL statements in a single Component?

1. Ease of use and cleaner code. If all you are doing is a series of SQL statements, then you might be better off with a single SQLRow component than a train of Talend components making your job look messy. For Ex - If you are truncating the staging tables in a Data Warehouse environment, then a single Talend component is convenient
   
2. It might be better if you create a store procedure for this purpose but sometimes, you might not have create-store-procedure-privilege on your schema. Then executing a series of SQL statements in SQLRow component by changing the "Additional JDBC parameters" setting as shown above might be the right thing to do.

We need to supply additional JDBC parameters because MYSQL jdbc driver, by default permits running only one query per jdbc connection and terminates after finding the first semi-colon (;). Above is a screenshot of how to modify the jdbc parameters.

Happy Talending!
-Praveena

Talend ELT Components

Purpose of the ELT Talend Components :

The ELT Talend components (Input, Mapper and Output) are useful for bulk loading the tables by pushing the processing overhead to the Database server rather than iterating through the resultsets in memory.

Example ELT Components for MYSQL. 

The three components (input, map, output) are closely tied and have to be used together.

1. tELTMySqlInput
2. tELTMySqlMap
3. tELTMySqlOutput

 What can you do with ELT Components :

1. The main purpose of the ELT components is PERFORMANCE.
2. Bulk read/insert into tables by using different tables and all types of JOINS, WHERE clauses and GROUP BY and HAVING clauses.

What can you NOT do with ELT Components :

1. You cannot JOIN tables from different SCHEMAS which means that the tables should be sourced from the same database schema.

Things to keep in mind when using the ELT_Mapping component

It always helps to check the SQL generated for us in the "Generated SQL" tab of editor of the ELT_Mapping component for syntax errors.

Happy Talending!

-Praveena

Talend GOTCHA when connecting SubJobs

Novice Talend users must pay special attention when you are connecting sub-jobs. You might want to connect the SubJobs with "On Subjob OK", "On Component OK" but might accidentally select "iterate" option from "ROW" instead of "On Subjob OK" from "TRIGGER".

A picture could perhaps clarify the above blah-blah.

Happy Talending!
-Praveena

Commenting GOTCHA that a Talend Novice should keep in mind

COMMENTING in QUERY components of Talend can be a pain as the error messages that you get due to commenting in SQL style will not be intuitive.

JAVA commenting style -
// For single line commenting
/* .... */ Multi line commenting

SQL commenting style-
-- For single line commenting.
/* .... */ Multi line commenting

The QUERY in a query component like MySqlInput is JAVA string behind the scenes. So commenting using the hiphens(--) in SQL style will result in an error. So it is wise to choose /* */ multi line JAVA/SQL style comments  to be safe than sorry.

Happy Talending!
-Praveena