RE: Sybase stored proc

[Christopher . Mathrusse]

Title: Sybase stored proc

There are a few solutions to your problem. One it to ensure that you have a transaction started before you invoke the sp. All sp's have a default mode. The default is usually Unchained. If it is then it is required that the sp be executed within a transaction. You can uses Spring to manage your transactions declaratively within an XML file or with Annotations.

 

Another way is to alter the sp and set it's mode to anymode.

sp_procxmode 'sp_name', 'anymode'

 

This will change the mode of the sp and allow it to be executed without a transaction. You will need to be logged in as dbo to perform this action.

 

The other way that I have found that works is not executing the sp within the <procedure> element but rather within a select, update or insert element. Then you can execute the sp as any other SQL. But before executing the sp you will need to set the chained mode off.

 

SET CHAINED OFF

EXEC some_procedure ?, ?, ?

 

SET CHAINED ON

 

This should work but some procedures, base upon what they do still require the execution with CHAINED ON.

---

From: "O'Toole, Joe" <[EMAIL PROTECTED]> [mailto:"O'Toole, Joe" <[EMAIL PROTECTED]>]
Sent: Wednesday, May 30, 2007 3:13 AM
To: "'user-java
Subject: Sybase stored proc

Hi

I am calling a sybase stored proc and am getting the following error:

Stored procedure may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

I have seen the issue documented while going through the archived mails.

The following solution was documented in Dec 04. I am wondering if a fix has been put in for this in the meantime

Thanks

Joe

Dec 04 Solution
You have to run certain SYBASE stored procedures with AutoCommit=ON.
However, iBATIS does not support autocommit (by design).  So, at this
time you'll need to supply your own connection (set autocommit=true)
to .setUserConnection().

You can use the same datasource to get a new connection from the
SqlMapClient (.getDataSource()), then set autocommit to true, then
.setUserConnection().  Just be sure to manage it and close it
properly.

Sorry for the roundabout solution, I'm looking for a better one.  Too
bad Sybase does this to us.