new-connection-sql / check-valid-connection-sql transaction
marcma Jul 13, 2004 6:50 AMHi All.
We use a very simple query for new-connection-sql and check-valid-connection-sql. (select 1 or select @@servername).
We get the following exception all the time:
com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2884)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2206)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1702)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1685)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:115)
at com.sybase.jdbc2.tds.Tds.setOption(Tds.java:1206)
at com.sybase.jdbc2.jdbc.SybConnection.setAutoCommit(SybConnection.java:986)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:425)
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:757)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:205)
If we disable new-connection-sql and check-valid-connection-sql
everything does fine.
This was no issue with jboss-3.2.3.
We use:
JBoss-3.2.5
Sybase ASE 12.5.2 / JConn2 5.5
JDK-1.4.2_05
*-ds.xml looks like this:
<datasources> <local-tx-datasource> <jndi-name>jdbc/XYZ</jndi-name> <connection-url>jdbc:sybase:Tds:XYZ:5555/xyz</connection-url> <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class> <user-name>xyz</user-name> <password>xyz</password> <connection-property name="APPLICATIONNAME">TX_bla</connection-property> <connection-property name="HOSTPROC">TX_bla</connection-property> <connection-property name="PACKETSIZE">512</connection-property> <connection-property name="DYNAMIC_PREPARE">True</connection-property> <min-pool-size>5</min-pool-size> <max-pool-size>15</max-pool-size> <blocking-timeout-millis>5000</blocking-timeout-millis>> <idle-timeout-minutes>3</idle-timeout-minutes> <prepared-statement-cache-size>100</prepared-statement-cache-size> <track-statements>False</track-statements> <new-connection-sql>select 1</new-connection-sql> <check-valid-connection-sql>select 1</check-valid-connection-sql> <exception-sorter-class-name>SybaseExceptionSorter</exception-sorter-class-name> </local-tx-datasource> <no-tx-datasource> <jndi-name>jdbc/XYZ_NOTX</jndi-name> <connection-url>jdbc:sybase:Tds:XYZ:5555/xyz</connection-url> <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class> <user-name>xyz</user-name> <password>xyz</password> <connection-property name="APPLICATIONNAME">NX_bla</connection-property> <connection-property name="HOSTPROC">NX_bla</connection-property> <connection-property name="PACKETSIZE">512</connection-property> <connection-property name="DYNAMIC_PREPARE">True</connection-property> <min-pool-size>5</min-pool-size> <max-pool-size>15</max-pool-size> <blocking-timeout-millis>5000</blocking-timeout-millis>> <idle-timeout-minutes>3</idle-timeout-minutes> <prepared-statement-cache-size>100</prepared-statement-cache-size> <track-statements>False</track-statements> <new-connection-sql>select 1</new-connection-sql> <check-valid-connection-sql>select 1</check-valid-connection-sql> <exception-sorter-class-name>SybaseExceptionSorter</exception-sorter-class-name> </no-tx-datasource> </datasources>
Our ExceptionSorter has no effect on the things said above. Neither positive nor negative. We can turn it on or off and the exception occurs either way. If you are interested in the sorter you will get it.
What could this be? Any suggestions, pointers or solutions?
For my 2 cents it may have something to do with the pool using the setAutoCommit inappropriate in terms of sybase.
Cheers