1 Reply Latest reply on Dec 11, 2002 10:52 AM by Dipak Kothari

    SET CHAINED command not allowed within multi-statement

    Dipak Kothari Newbie


      Does anyone know why the LocalConnection class does not call the method 'checkTransaction()' to change the auto-commit value of the underlying connection before delegating the call to the underlying connection (for example getMetaData())? It does for all methods in LocalStatement.

      We're using the JDBC resource adapter for sybase (Local Transactions) shipped with JBoss 3.0.4. In our server we use the connection pool outside the container and so most of the time the auto-commit is set to true. However, we do have a muti-statement transaction in which we control the setting of auto-commit via the connection. The problem we are facing is that once the auto-commit value of underlying connection is set to the false, any statement that is executed after a "getMetaData" fails with the following exception:

      com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.

      at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2538)
      at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1922)
      at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
      at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1611)
      at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1594)
      at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
      at com.sybase.jdbc2.tds.Tds.setOption(Tds.java:1048)
      at com.sybase.jdbc2.jdbc.SybConnection.setAutoCommit(SybConnection.java:626)
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.checkTransaction(LocalManagedConnection.java:423)
      at org.jboss.resource.adapter.jdbc.local.LocalConnection.checkTransaction(LocalConnection.java:756)
      at org.jboss.resource.adapter.jdbc.local.LocalStatement.checkTransaction(LocalStatement.java:771)
      at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeQuery(LocalStatement.java:208)

      The reason (I think) is that the transaction is left open after getMetaData() call because auto-commit is set to false.

      I would be grateful if anyone could shed any light.