• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 42
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 49
Spring Redhat Clojure
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 42
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 42
Android Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
This is my favorite tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic