• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
nazzy khan
Ranch Hand
Posts: 39
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Sunil Tiwari
Ranch Hand
Posts: 49
Clojure Redhat Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
nazzy khan
Ranch Hand
Posts: 39
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic