Hi,

I'm having problems calling third party Sybase (12.5) stored procedures from my web application.

I'm using Hibernate within the SEAM environment (JPA rather than direct to hibernate)...
[Version] Hibernate Annotations 3.3.0.GA
[Environment] Hibernate 3.2.4.sp1
[Version] Hibernate EntityManager 3.3.1.GA
JConnect version: JConnect 6

My stored proc call:
entityManager.createNativeQuery(
"exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
.setParameter("accountId", adhocFeeTransaction.getAccountId())
.setParameter("date", adhocFeeTransaction.getEffectiveOn())
.setParameter("amount", adhocFeeTransaction.getFees().get(0).getAmount())
.setParameter("reference", adhocFeeTransaction.getReference())
.setParameter("adviserId", adhocFeeTransaction.getFees().get(0).getOutletId())
.setParameter("ttypId", ttypId)
.executeUpdate();

The problem I am having with the call is that I get the following error...

14:52:43,822 ERROR [JDBCExceptionReporter] Stored procedure 'ExtInsertAdhocCharge' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
14:52:43,822 WARN [AccountDAO] exception during insert of adhoc fee transaction
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query

I therefore tried adding "SET CHAINED OFF" in various ways i.e.
- As part of the actual entityManager.createNativeQuery("SET CHAINED OFF exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
- Before the actual call to the stored proc i.e.
entityManager.createNativeQuery("SET CHAINED OFF").executeUpdate();
entityManager.createNativeQuery(
"exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
.setParameter("accountId", adhocFeeTransaction.getAccountId())
.setParameter("date", adhocFeeTransaction.getEffectiveOn())
.setParameter("amount", adhocFeeTransaction.getFees().get(0).getAmount())
.setParameter("reference", adhocFeeTransaction.getReference())
.setParameter("adviserId", adhocFeeTransaction.getFees().get(0).getOutletId())
.setParameter("ttypId", ttypId)
.executeUpdate();

In both cases I get the following error and warnings:
Hibernate:

SET
CHAINED OFF
14:56:38,558 WARN [JDBCExceptionReporter] SQL Error: 226, SQLState: ZZZZZ
14:56:38,558 ERROR [JDBCExceptionReporter] SET CHAINED command not allowed within multi-statement transaction.

14:56:38,558 WARN [AccountDAO] exception during insert of adhoc fee transaction
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query

Note that my stored procedure has the following: EXEC sp_procxmode 'ExtInsertAdhocCharge','unchained' when it's compiled into the database.

After further Googling I found the following on these forums...
viewtopic.php?p=2202295

Key point here being the use of the parameter "SERVER_INITIATED_TRANSACTIONS=false" on the JDBC URL. I tried this (without the SET CHAINED OFF) and it worked fine. However, I now get warning messages whenever the database is hit and a query run i.e. not just when my stored procedure is called:
15:09:03,719 WARN [JDBCExceptionReporter] SQL Warning: 0, SQLState: 010CP
15:09:03,719 WARN [JDBCExceptionReporter] 010CP: AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed.

Should I be concerned about this?

Also, is there an alternative to using the "SERVER_INITIATED_TRANSACTIONS=false" solution?

Any thoughts/suggestions greatly appreciated.

Thanks!