setQueryTimeout not working

  Ruben hesselb  2012-06-11 19:03:37

Does setQueryTimeout work in the latest JDBC driver ?

We have a tool that executes some queries, but the first couple of queries
timeout just after the connection has been established.
Each query is tried 3 times and each time the QueryTimeout is doubled (300
seconds default), and everything below happens in the same synchronized
block.

The first 3 columns are DATE TIME and ms, so how can the JDBC driver claim
that the query timed out within the same milisecond ?
Totally during the 3 retries 2100 seconds should have been used, but as you
can see in the logs only 78ms were actually used

040803 145706 0632 EV report connectToSQLServer Connected to SQL server
succesfully ##[
040803 145706 0695 ER report executeQuery caught exception while
executing [select * from TABLE where 1=2] with timeout
[300]java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Execution timeout expired.
##[pi=Tomcat,ti=HttpProcessor[80][4],cr=platform.report.util.CoreLogAdapter.
log(CoreLogAdapter.java:74)
040803 145706 0695 EV report executeQuery Setting new timeout [600] ##[
040803 145706 0710 ER report executeQuery caught exception while
executing [select * from TABLE where 1=2] with timeout
[300]java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Execution timeout expired.
##[pi=Tomcat,ti=HttpProcessor[80][4],cr=platform.report.util.CoreLogAdapter.
log(CoreLogAdapter.java:74)
040803 145706 0710 EV report executeQuery Setting new timeout [1200] ##[
040803 145706 0710 ER report executeQuery caught exception while
executing [select * from TABLE where 1=2] with timeout
[300]java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Execution timeout expired.
##[pi=Tomcat,ti=HttpProcessor[80][4],cr=platform.report.util.CoreLogAdapter.
log(CoreLogAdapter.java:74)
040803 145706 0710 EV report executeQuery Setting new timeout [2400] ##[
040803 145706 0710 ER report executeQuery failed after 3 retries (cause
exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Execution timeout expired.)

Here is the code

for (int retry = 1; retry <= retries; retry += 1) { try { statement.setQueryTimeout(timeout); rs = statement.executeQuery(sql); return rs; } catch (SQLException e) { if (rs != null) { try { while (rs.next()); } catch (SQLException ex) { } finally { try { rs.close(); } catch (SQLException ex) { } } rs = null; } log.error("executeQuery", "caught exception while executing ["+sql+"] with timeout ["+timeout+"]" + e); // double sqltimeout for next try timeout = timeout * 2; log.event("executeQuery", "Setting new timeout [" + timeout +"]"); } } Hope someone can help /Ruben

  Suepurkis 2012-06-21 04:29:12

[the_ad_placement id="add-en-post-1"]
Rueben, I used your code snippet in one of my programs and it worked as expected. Can you share the rest of your code and connection options, so I can compare what I have with what you have? Thanks. Sue Purkis DataDirect Technologies.