topic Re: Support QueryTimeout in Oracle database components in Talend Studio

Support QueryTimeout in Oracle database components

IBuruleanu1666252610 — Fri, 15 Nov 2024 22:26:30 GMT

Hello Everyone,

We have an issue with Talend Oracle database components. For data manipulation components (such as tDbRow, tDbInput) we would like to be able to use the underlying's Java sql Statement instance options such as setQueryTimeout.

At the moment, we have to construct our own components using the tDbConnection and write the logic from scratch.

A simple example in a tJavaRow:

output_row.errorCode = null;

output_row.queryErrorMessage = null;

java.sql.Connection conn_tJavaRow = (java.sql.Connection)globalMap.get("conn_tDBConnection_1");

java.sql.Statement stmt_tJavaRow = conn_tJavaRow.createStatement();

stmt_tJavaRow.setQueryTimeout(context.query_timeout != null ? ((Long)context.query_timeout).intValue() : 0);

String query = "...";

try {

stmt_tJavaRow.execute(query);

log.info("tJavaRow - Execute the query: '" + query + "' has finished.");

} catch (java.lang.Exception e) {

log.error("tJavaRow - " + e.getMessage());

output_row.errorCode = ((java.sql.SQLException) e).getSQLState();

output_row.queryErrorMessage = e.getMessage();

} finally {

stmt_tJavaRow.close();

}

Is there any possibility to support this inside the components themselves?

Thanks

Re: Support QueryTimeout in Oracle database components

Anonymous — Fri, 21 Oct 2022 08:54:45 GMT

Hello,

If querytimeout is implemented via the connection string in oracle, you could try to add the parameter queryTimeout=X to the addtional JDBC parameter field in the advanced settings panel of tOracleInput component. eg:

"noDatetimeStringSync=true;queryTimeout=10".

Best regards

Sabrina

Re: Support QueryTimeout in Oracle database components

IBuruleanu1666252610 — Fri, 21 Oct 2022 09:26:25 GMT

Hi xdshi,

Unfortunately, I cannot find such option for tDbRow (with Oracle connection):

This is only visible in tDbConnection by the option additional JDBC Params, but we tested this out already and it does not work.

Thanks

Re: Support QueryTimeout in Oracle database components

Anonymous — Fri, 04 Nov 2022 08:51:01 GMT

Hello,

Because the query timeout is related to the Statement object itself.

According to the documentation it offers this functionality:

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)

It would be a feature request as setting this timeout requires access to the Statement object.

Best regards

Sabrina