Executing multiple SQL statements with one tMysqlRow component

Overview

Explains how to execute multiple SQL statements using a single tMysqlRow component.

 

Environment

This procedure was written with:

  • Talend Open Studio for DI 5.0-r72978
  • JDK version: Sun JDK build 1.6.0_26-b03
  • Operating system: Windows XP SP3
  • Mysql 5.0.67-community-nt

Talend verified this procedure to be compatible with:

  • Data Integration releases: 4.2.3, 4.2.4, 5.0.0, 5.0.1, 5.0.2, 5.1.1, 5.1.2, 5.2.0

 

Procedure

By default, the MySQL JDBC driver does not allow multiple SQL statements in a single statement. Execution stops after the first semi-colon. However, a configuration parameter allows you to modify the default behavior. Update Additional JDBC Parameters to include "allowMultiQueries=true".

The following example Job demonstrates how to execute multiple SQL statements in a tMysqlRow component. The detailed job settings and explanation are as follows:

 1_025.png

 

 2_023.png

Version history
Revision #:
2 of 2
Last update:
‎06-22-2017 03:03 PM
Updated by:
 
Labels (1)
Comments
cascador84

thanks for your post.
Suppose I have only the "update" statement in your example. How I could get the number of rows impacted ? I try to add select ROW_COUNT() as nbrows and check the "propagate Query" option, but the component (or following tparserecordset) get the error :

Can not issue data manipulation statements with executeQuery()

I try to add a tmysqlinput after, with row_count statement, but result is still 0.

I would like to avoid to add a component before with count(*). So is there another possibility to get the number of rows affected by the tmysqlrow ?

 

thanks in advance.
Regards

 

agershenson

Hi cascador84,

 

You will get more response to your question if you post it in one of the Community discussion boards. Or for technical help, please contact our Support team at https://www.talend.com/services/technical-support/.

 

HTH,

Alyce