ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "O'Toole, Joe" <Joe.OTo...@fmr.com>
Subject RE: Sybase stored proc
Date Wed, 30 May 2007 13:56:14 GMT
Thanks for getting back. I should have pointed out in my previous mail that
changes to the stored proc is not a runner unfortunately.
The solution proposed on the wiki I have been trying to follow:
. Use your own Connection and .setAutoCommit(true). Pass this connection
into the SqlMapClient.setUserConnection(Connection) method. You're
responsible for closing the connection afterwards. If you like, you can get
the Connection from the same DataSource by calling
SqlMapClient.getDataSource(). Although this seems a bit "roundabout", it
I would really appreciate if someone could elaborate on the solution
described, i.e. exactly which classes that need to be changed and any config
changes to get this up and going. E.g. when it states to pass the connection
in to the SQLMapClient.setUserConnection(Connection) method - where do i do
this from? etc

-----Original Message-----
From: Christopher.Mathrusse@sybase.com
Sent: 30 May 2007 14:48
To: O'Toole, Joe; 'user-java@ibatis.apache.org'
Subject: RE: 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.
EXEC some_procedure ?, ?, ?
This should work but some procedures, base upon what they do still require
the execution with CHAINED ON.


From: "O'Toole, Joe" <Joe.OToole@fmr.com> [mailto:"O'Toole, Joe"
Sent: Wednesday, May 30, 2007 3:13 AM
To: "'user-java
Subject: Sybase stored proc


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 



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 

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

View raw message