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

SET CHAINED command not allowed within multi-statement

kothard Dec 10, 2002 9:49 AM

Hi,

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.

Thanks,
Dipak

1. Re: SET CHAINED command not allowed within multi-statement

kothard Dec 11, 2002 10:52 AM (in response to kothard)

I digged a little deeper and found out that it wasn't the getMetaData() which started the transaction. The problem (which seem to be resolved in 3.2.0beta) is that the cleanup() in LocalManagedConnection did not reset the autoComit value. By changing this it all works.
Actions