ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher.Mathru...@sybase.com
Subject RE: Sybase stored proc
Date Wed, 30 May 2007 13:47:41 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Sybase stored proc</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META boundary="----_=_NextPart_001_01C7A2A3.28CC8D8E" alternative; multipart 
Content-Type:>
<META Content-Type: ------_="_NextPart_001_01C7A2A3.28CC8D8E" html text 
quoted-printable Content-Transfer-Encoding:>
<META content="MSHTML 6.00.6000.16441" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2>Another way is to alter the sp and set it's mode to 
anymode.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2><STRONG><EM>sp_procxmode 'sp_name', 
'anymode'</EM></STRONG></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><STRONG><EM><FONT

face=Arial color=#0000ff size=2></FONT></EM></STRONG></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><STRONG><EM><FONT

face=Arial color=#0000ff size=2></FONT></EM></STRONG></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2>The other way that I have found that works is not executing 
the sp within the &lt;procedure&gt; 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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2><EM><STRONG>SET CHAINED 
OFF</STRONG></EM></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2><EM><STRONG>EXEC some_procedure ?, ?, 
?</STRONG></EM></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2><EM><STRONG></STRONG></EM></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=773573113-30052007><FONT face=Arial 
color=#0000ff size=2><EM><STRONG>SET CHAINED 
ON</STRONG></EM></FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT>&nbsp;</DIV>
<DIV><SPAN class=773573113-30052007></SPAN><FONT face=Arial><FONT

color=#0000ff><FONT size=2>T<SPAN class=773573113-30052007>his should work
but 
some procedures, base upon what they do still require the execution with CHAINED 
ON.</SPAN></FONT></FONT></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> "O'Toole, Joe" &lt;Joe.OToole@fmr.com&gt;

[mailto:"O'Toole, Joe" &lt;Joe.OToole@fmr.com&gt;] <BR><B>Sent:</B>
Wednesday, 
May 30, 2007 3:13 AM<BR><B>To:</B> "'user-java<BR><B>Subject:</B>
Sybase stored 
proc<BR></FONT><BR></DIV>
<DIV></DIV>
<META content="MS Exchange Server version 5.5.2658.34" name=Generator>
<P><FONT face=Arial size=2>Hi</FONT> </P>
<P><FONT face=Arial size=2>I am calling a sybase stored proc and am getting the

following error:</FONT> </P>
<P><B><I><FONT face=Arial size=2>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.</FONT></I></B></P>
<P><FONT face=Arial size=2>I have seen the issue documented while going through

the archived mails. </FONT></P>
<P><FONT face=Arial size=2>The following solution was documented in Dec 04. I
am 
wondering if a fix has been put in for this in the meantime</FONT> </P>
<P><FONT face=Arial size=2>Thanks</FONT> </P>
<P><FONT face=Arial size=2>Joe</FONT> </P>
<P><FONT face=Arial>Dec 04 Solution </FONT><BR><FONT face=Arial>You
have to run 
certain SYBASE stored procedures with AutoCommit=ON. </FONT><BR><FONT 
face=Arial>However, iBATIS does not support autocommit (by design).&nbsp; So, at 
this</FONT> <BR><FONT face=Arial>time you'll need to supply your own connection

(set autocommit=true)</FONT> <BR><FONT face=Arial>to 
.setUserConnection().</FONT> </P>
<P><FONT face=Arial>You can use the same datasource to get a new connection from

the</FONT> <BR><FONT face=Arial>SqlMapClient (.getDataSource()), then set

autocommit to true, then</FONT> <BR><FONT face=Arial>.setUserConnection().&nbsp;

Just be sure to manage it and close it</FONT> <BR><FONT 
face=Arial>properly.</FONT> </P>
<P><FONT face=Arial>Sorry for the roundabout solution, I'm looking for a better

one.&nbsp; Too</FONT> <BR><FONT face=Arial>bad Sybase does this to us.</FONT>

</P><BR></BODY></HTML>


Mime
View raw message