Setting the fetchsize property value for MySQL or Oracle SQL statements

Question

The JDBC SQL statement fetchsize property allows you to specify the row numbers fetched for each round trip between database server and client.

 

How can I set this property when using a Job that accesses a MySQL or Oracle database server?

 

Answer

You can pass the fetchsize property in the Talend connection components tOracleConnection and tMysqlConnection, and the related Additional JDBC Parameters field available in these components.

  • Oracle-specific database:

    • Use the tOracleConnection component and the "defaultRowPrefetch" parameter.

      For example, when using "defaultRowPrefetch=33", the fetchsize is 33.

  • MySQL-specific database:

    • Use the tMysqlConnection component and the "defaultFetchSize" parameter.

      For example, when using "defaultFetchSize=33", the fetchsize is 33.

Note: These components and parameters are database-specific, so if you are using a different database, you must refer to the appropriate database documentation for the specific parameter name to pass.

 

This article uses a MySQL database to pass the defaultFetchSize value, as shown below:

fetchsize.png

 

To verify that the fetchsize reflects the value passed in the Additional JDBC Parameters field, use the following code in a tJava component:

System.out.println("Mysql ");
java.sql.Connection c = (java.sql.Connection ) globalMap.get("conn_tMysqlConnection_1");
System.out.println("Mysql connection = " + c);
java.sql.Statement st = c.createStatement(); System.out.println("Mysql fetchsize = " + st.getFetchSize());

When executing this code with "defaultFetchSize=33", the corresponding output is shown below:

"
Mysql 
Mysql connection = com.mysql.jdbc.JDBC4Connection@641147d0
Mysql fetchsize = 33
"
Version history
Revision #:
8 of 8
Last update:
‎09-29-2018 12:19 AM
Updated by: