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 Thu, 31 May 2007 11:50:23 GMT
Hi 

Sorry for forwarding on same mail again, but its just under a lot of
pressure to get solution out.

Would really appreciate if someone could take another look at mail below. As
i said earlier, i know people spoke of a solution but would really
appreciate a litrle more help understanding how to implement the solution

Thanks

-----Original Message-----
From: O'Toole, Joe 
Sent: 30 May 2007 14:56
To: 'Christopher.Mathrusse@sybase.com'; 'user-java@ibatis.apache.org'
Subject: RE: Sybase stored proc


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
works.
 
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
 
Thanks
Joe

-----Original Message-----
From: Christopher.Mathrusse@sybase.com
[mailto: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.
 
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" <Joe.OToole@fmr.com> [mailto:"O'Toole, Joe"
<Joe.OToole@fmr.com>] 
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. 



Mime
View raw message