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: '[EMAIL PROTECTED]'; '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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[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.
