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)

Evidence? Stack trace?

user207421
– user207421

2014-09-30 22:14:27 +00:00
Commented Sep 30, 2014 at 22:14 — user207421
– user207421, Commented Sep 30, 2014 at 22:14

Sandeep Vaid · Accepted Answer · 2014-09-30 23:26:52Z

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();
        /*     */}
    /*     */}

Thank you sandeep. I guess then I have to resort to the dirty way of making error code checks myself.

Mark Rotteveel · Accepted Answer · 2014-10-02 19:28:24Z

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:

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

There are also further complications:

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.
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.

Collectives™ on Stack Overflow

JDBC timeout is not throwing SQLTimeoutException

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related