Issue calling stored procedure

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...
https://forum.hibernate.org/viewtopic.php?p=2202295&sid=ddbd36190a009e78a6fd51142e5af5fa
 
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!
Vass1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
I have an easy fix for you, but it is a bit dangerous because it means you might not be getting the transaction behaviour you expect.

Some background: CHAINED MODE is the ANSI standard, and it says you are always in a transaction whether you think you are or not. In CHAINED mode your client implicitly sends a "BEGIN TRAN" as the first command on connecting, and also sends one immediately following any ROLLBACK TRAN or COMMIT TRAN statement.

(I personally think that's like having implicit left parentheses added to everything, but requiring explicit right parentheses to close them... but anyway.)

This is a property you can set in your connection string... Sybase defaults to UNCHAINED.

Anyway, my fix: run

   exec sp_procxmode '[proc name]', 'anymode'

And the proc will no longer throw these errors. (I think it's "anymode", if that fails, try "any", I'm doing this from memory. :) )

Possibly dangerous because it means you might not be getting the transactional behaviour you think you are...
0
Vass1Author Commented:
Hi,

Thanks for the feedback.

I've just tried the following combinations:
1. Left "SERVER_INITIATED_TRANSACTIONS=false" on the JDBC URL and changed the proc as suggested i.e. EXEC sp_procxmode 'dbo.ExtInsertAdhocCharge','anymode'... I still receive the warning messages as above:

12:44:28,849 WARN  [JDBCExceptionReporter] SQL Warning: 0, SQLState: 010CP
12:44:28,849 WARN  [JDBCExceptionReporter] 010CP: AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed.

2. Removed the "SERVER_INITIATED_TRANSACTIONS=false" parameter from the JDBC URL and changed the proc as suggested i.e.  EXEC sp_procxmode 'dbo.ExtInsertAdhocCharge','anymode'... I now get issues further down the line with stored procedures that are being called by mine e.g.

12:48:38,757 WARN  [JDBCExceptionReporter] SQL Error: 7713, SQLState: ZZZZZ
12:48:38,757 ERROR [JDBCExceptionReporter] Stored procedure 'StatData_GetCodeId' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

Any other ideas greatly welcomed :)

Cheers
0
Joe WoodhousePrincipal ConsultantCommented:
You need to set all your procs to "anymode". :)

Well, all of the procs ever called by you or by any procs those call, etc etc...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Vass1Author Commented:
Oh right, that thought had crossed my mind but didn't particularly want to go down that road :)

Problem is that I don't have the ability to do it for the others as they're part of a huge third party back office application.  I wouldn't be overly confident on what effect this could potentially have on the back office application, bearing in mind I also don't have access to the code behind them (they're encrypted).

For info, reason I'm calling these other stored procedures is that I'm using them as a 'kind of API' for getting data into the database via a web application i.e. I don't need to re-invent the wheel :)
0
Joe WoodhousePrincipal ConsultantCommented:
Hmmm.

You can't really mix and match the proc transaction modes. You have to pick one and stick with it. If you have to leave the third party ones alone, then you need to reset these ones to match their mode... and find a connection string solution. :(
0
Vass1Author Commented:
Thanks for your further feedback.  Yeah, didn't think I could mix and match modes.

I have left the "SERVER_INITIATED_TRANSACTIONS=false" parameter on the jdbc URL and then added the following property to my persistence.xml deployment descriptor file for hibernate...

<property name="hibernate.connection.autocommit" value="false"/>

I still get warning messages in the logs ie...
12:44:28,849 WARN  [JDBCExceptionReporter] SQL Warning: 0, SQLState: 010CP
12:44:28,849 WARN  [JDBCExceptionReporter] 010CP: AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed.

But after much testing and debug, I am happy that these are not an issue for me and all the transaction handling is fine.  I have suppressed these messages in the log4j config file...

   <category name="org.hibernate.util.JDBCExceptionReporter">
      <priority value="ERROR"/>
   </category>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.