Stored procedure 'XXX' may be run only in unchained transaction mode error in JBOSS 7 server/JCONN driver4

Question

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.

Answer 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.

Answer 2

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).