wood burning stoves 2.0*
The moose likes JDBC and the fly likes com.sybase.jdbc2.SybConnection: SET CHAINED command not allowed within multi-statement transaction Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "com.sybase.jdbc2.SybConnection: SET CHAINED command not allowed within multi-statement transaction" Watch "com.sybase.jdbc2.SybConnection: SET CHAINED command not allowed within multi-statement transaction" New topic
Author

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

nazzy khan
Ranch Hand

Joined: May 06, 2010
Posts: 39

Hi,

I'm getting below error while using JTA transaction. I have two datasources one for sybase database and other is for Oracle.

Also, I'm using jotm for JTA transaction. Server is Tomcat.

com.sybase.jdbc2.SybConnection@12833ff) start:javax.transaction.xa.XAException:
com.sybase.jdbc2.SybConnection: SET CHAINED command not allowed within multi-statement transaction.
(error code=0) --com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.


Any idea why I'm getting below error on sybase?

I'm using Sybase thin driver com.sybase.jdbc2.jdbc.SybDriver and org.enhydra.jdbc.pool.StandardXAPoolDataSource.

Thanks & Regards,
Nazz
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30356
    
150

Set chained is a transaction setting. Since you already have a transaction managed separately you are giving a conflicting command and the driver is throwing an error. The solution would be to not use "set chained" since XA is taking care of transactions for you.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sunil Tiwari
Ranch Hand

Joined: Sep 19, 2006
Posts: 49

Are you trying set the auto commit to false (in a loop for multiple transactions)? :


if so, remember that once you are done with your insert/update statements, you need to either commit or rollback AND set the auto commit to true again.
Failing to do so, can lead to the exception.

~ Neil (SCJP)
nazzy khan
Ranch Hand

Joined: May 06, 2010
Posts: 39

Hi Both,

Thanks for your reply : ).

Jeanne Boyarsky wrote: The solution would be to not use "set chained" since XA is taking care of transactions for you.


Jeanne: Before calling the actual database call, I have explicitly called the query:

but now the code is breaking at this line with the same exception. Sorry I didn't get you on that line.

Hi Sunil,

Are you trying to say I should explicitly call setAutoCommit(true) on connection. If yes, where exactly should I call. Should I extend TransactionProxyFactoryBean or JtaTransactionManager class and override doBegin() method and there change the auto commit behaviour.

I'm posting my Spring bean configuration and datasource bean.

Is there any class which is creating problem with Sybase may be Driver class (com.sybase.jdbc2.jdbc.SybDriver) jconn2.jar or jotm or enhydra XADataSource. I'm only getting this error on Sybase and not on Oracle database.

Spring Service Bean Configuration:



Data Source Configuration:

nazzy khan
Ranch Hand

Joined: May 06, 2010
Posts: 39

I have managed to resolve the issue.

I have changed my sybase driver from com.sybase.jdbc2.jdbc.SybDriver to com.sybase.jdbc3.jdbc.SybDriver. With jconn3.jar, there is no exception and JTA transaction is working like a charm : )

Thanks & Regards,
Nazz
 
Don't get me started about those stupid light bulbs.
 
subject: com.sybase.jdbc2.SybConnection: SET CHAINED command not allowed within multi-statement transaction