1

I am currently migrating apps from JBOSS 5 to JBOSS 7.0.4. I have also changed the Sybase driver from Jconn3 to Jconn4 as part of migration. I am getting the below error while executing a Sybase procedure in JBoss 7 with jconn4.jar:

2017.05.26 05:53:17 ERROR stderr(71): Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Stored procedure 
'sp_update_trade_data' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
2017.05.26 05:53:17 ERROR stderr(71): 
2017.05.26 05:53:17 ERROR stderr(71):   at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4131)
2017.05.26 05:53:17 ERROR stderr(71):   at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3247)
2017.05.26 05:53:17 ERROR stderr(71):   at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
2017.05.26 05:53:17 ERROR stderr(71):   at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:294)
2017.05.26 05:53:17 ERROR stderr(71):   at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:276)

I am explicitly calling SET CHAINED OFF command in the application data layer before the procedure is invoked. But I am still getting the below error.

I suspect some settings in JBOSS 7 is overriding SET CHAINED OFF command called from application

The procedure executes fine without errors in JBOSS 5 with Jconn3.jar driver.
Can you please resolve this error.

Karthik
  • 1,302
  • 5
  • 25
  • 56

2 Answers2

1

Assuming no changes to the stored proc's execution mode (via sp_procxmode), I'd suggest taking a closer look at the differences in the jconn3 and jconn4 connection profiles.

I'm guessing jconn3 had autocommit=true (run in unchained transaction mode), while jconn4 has autocommit=false (run in chained transaction mode).

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • Where can I set/configure chained off mode in JBOSS 7? – Karthik May 30 '17 at 06:18
  • I don't work with JBOSS so I don't know the exact location ... but a quick google search on "jboss autocommit" brings up quite a few hits that likely have some useful info. – markp-fuso May 30 '17 at 12:26
1

I resolved this issue by changing 'unchained mode' to 'anymode' in the sybase procedure.
For example: EXEC sp_procxmode '','anymode'.
This will allow the transaction for the stored procedure to be controlled by external clients (ie the JBoss container).
It will also work with local clients as well, thus the "Any" mode.

Karthik
  • 1,302
  • 5
  • 25
  • 56