[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Issue calling stored procedure

Posted on 2010-03-29
6
Medium Priority
?
6,528 Views
Last Modified: 2013-11-24
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!
0
Comment
Question by:Vass1
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 29085042
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
 

Author Comment

by:Vass1
ID: 29089122
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 29089380
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Vass1
ID: 29090732
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 29091533
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
 

Accepted Solution

by:
Vass1 earned 0 total points
ID: 29927332
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
The viewer will learn how to implement Singleton Design Pattern in Java.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question