JDBC timeout is not throwing SQLTimeoutException

Question

I am trying to set the query timeout on a JDBC Statement and expecting it to throw an SQLTimeoutException when it times out. But instead I am getting a generic SQLException with error code ORA-01013.

Any idea what I must be missing?

I am looking for a DB independent way to handle timeouts. And checking for DB specific error codes may not help there.

BTW, I am setting this property via Spring's JdbcTemplate.

myStatement.setQueryTimeout(1);

throws

java.sql.SQLException: ORA-01013: user requested cancel of current operation

Edit: Here is the stack trace for the error. I am using a thin driver (ojdbc6-11.2.0.1.0.jar).

java.sql.SQLException: ORA-01013: user requested cancel of current operation

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1259)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:211)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:219)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:233)
    at mytest.MyDAO.retrieve(MyDAO.java:12)

Answer 1

this is code used by driver to throw exception/error and it does not do anything to map exception to SQLTimeoutException. This is gap in oracle driver implementation

refer https://github.com/wenshao/OracleDriver10_2_0_2/blob/4af5a9295e0e9fef3f7e51ba7bf735fb81e9186a/src/oracle/jdbc/driver/T4CTTIoer.java

the snippet taken from below full method is

     DatabaseError.throwSqlException(this.meg.conv
                    .CharBytesToString(this.errorMsg, this.errorLength[0],
                            true), DatabaseError
                    .ErrorToSQLState(this.retCode), this.retCode);

The full method is this

/*     */
/*     */void processError(boolean paramBoolean,
        OracleStatement paramOracleStatement)
/*     */throws SQLException
/*     */{
    /* 303 */if (this.retCode != 0)
    /*     */{
        /* 311 */switch (this.retCode)
        /*     */{
        /*     */case 28:
            /*     */
        case 600:
            /*     */
        case 1012:
            /*     */
        case 3113:
            /*     */
        case 3114:
            /* 323 */this.connection.internalClose();
            /*     */}
        /*     */
        /* 328 */if (paramBoolean)
        /*     */{
            **/* 331 */
             *DatabaseError.throwSqlException(this.meg.conv
                    .CharBytesToString(this.errorMsg, this.errorLength[0],
                            true), DatabaseError
                    .ErrorToSQLState(this.retCode), this.retCode);*
            /*     */}
        /*     */else
        /*     */{
            /* 335 */return;
            /*     */}
        /*     */
        /*     */}
    /*     */
    /* 341 */if (!paramBoolean) {
        /* 342 */return;
        /*     */}
    /*     */
    /* 351 */if ((this.warningFlag & 0x1) == 1)
    /*     */{
        /* 353 */int i = this.warningFlag & 0xFFFFFFFE;
        /*     */
        /* 356 */if (((i & 0x20) == 32) || ((i & 0x4) == 4)) {
            /* 357 */throw DatabaseError.newSqlException(110);
            /*     */}
        /*     */}
    /*     */
    /* 361 */if ((this.connection != null)
            && (this.connection.plsqlCompilerWarnings))
    /*     */{
        /* 363 */if ((this.flags & 0x4) == 4)
            /* 364 */paramOracleStatement.foundPlsqlCompilerWarning();
        /*     */}
    /*     */}

Answer 2

Unfortunately JDBC drivers do not always implement everything required (or: suggested) by JDBC. The especially applies to 'newer' features. SQLTimeoutException was added in Java 6 / JDBC 4.

This has two implications:

1. It isn't available in older versions,
2. You need a driver that is for JDBC 4 or higher.

There are also further complications:

1. Given the specific error message ("ORA-01013: user requested cancel of current operation") in your question, it might simply be impossible for the driver to discern an actual user initiated cancellation of the statement from one initiated by a timeout. In that case it is better to throw the most generic exception type.

2. If a vendor supports multiple Java and JDBC versions, they might simply be taking shortcuts and only do the minimum to be (almost) JDBC-compliant. This might include only throwing SQLException to make it easier for them to use the same code-base with a minimum of fuss.