ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher.Mathru...@sybase.com
Subject RE: Retrieve stored proc return code
Date Mon, 21 May 2007 18:48:42 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META boundary="----=_Part_32882_21040119.1179762678820" alternative; multipart 
Content-Type:>
<META http-equiv=Content-Type content="text/html; charset=us-ascii" 
Content-Type: ------="_Part_32882_21040119.1179762678820" inline 
Content-Disposition: html; text 7bit Content-Transfer-Encoding:>
<META content="MSHTML 6.00.6000.16441" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN 
class=690274618-21052007>Are you setting the Sybase JConnect property 
IGNORE_DONE_IN_PROC=true? If not then you probably won't see the exception 
unless you process all of the returned resultSets. This is by design. If your 
only interested in the last resultSet, the one that actually contains the error, 
then you will need to set this property on the JConnect JDBC driver. You should 
refer to the JConnect documentation for more specifics.</SPAN></FONT></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> "Mikael Andersson" 
&lt;mail.micke@gmail.com&gt; [mailto:"Mikael Andersson" 
&lt;mail.micke@gmail.com&gt;] <BR><B>Sent:</B> Monday, May 21, 2007
8:51 
AM<BR><B>To:</B> user-java@ibatis.apache.org<BR><B>Subject:</B>
Re: Retrieve 
stored proc return code<BR></FONT><BR></DIV>
<DIV></DIV>Final question.<BR><BR>When I register an output parameter
and there 
is a foreign key constraint violation. I get an Exception, 
org.springframework.dao.DataIntegrityViolationException, which is 
fine.<BR><BR>But when I don't register an output parameter, there is no 
exception thrown and the statement fails silently. Is this the expected 
behaviour? <BR><BR>Thanks,<BR>&nbsp;Mike<BR><BR><BR>
<DIV><SPAN class=gmail_quote>On 21/05/07, <B class=gmail_sendername>Jeff

Butler</B> &lt;<A 
href="mailto:jeffgbutler@gmail.com">jeffgbutler@gmail.com</A>&gt; wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote 
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
  <DIV>The error is probably in your parameter map - you're likely trying to use 
  a non-standard JDBC type.&nbsp; Send your parameter map - that might 
  help.</DIV>
  <DIV>&nbsp;</DIV>
  <DIV>Jeff Bulter<BR><BR>&nbsp;</DIV>
  <DIV><SPAN class=e id=q_112af4be8fd1da64_1>
  <DIV><SPAN class=gmail_quote>On 5/21/07, <B class=gmail_sendername>Mikael

  Andersson</B> &lt;<A onclick="return top.js.OpenExtLink(window,event,this)"

  href="mailto:mail.micke@gmail.com" target=_blank>mail.micke@gmail.com </A>&gt;

  wrote:</SPAN> 
  <BLOCKQUOTE class=gmail_quote 
  style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Hi

    Jeff,<BR>when I try adding a parameter element, I get 
    this:<BR><BR>org.springframework.jdbc.UncategorizedSQLException : 
    SqlMapClient operation; uncategorized SQLException for SQL []; SQL state 
    [JZ0SL]; error code [0];&nbsp;&nbsp; <BR>--- The error occurred in 
    ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.&nbsp; <BR>--- The error occurred 
    while executing update procedure.&nbsp; <BR>--- Check the { ? = call 
    pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?) }.&nbsp; <BR>--- Check 
    the output parameters (register output parameters failed).&nbsp; <BR>--- 
    Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.; nested 
    exception is 
    com.ibatis.common.jdbc.exception.NestedSQLException:&nbsp;&nbsp; <BR>---
The 
    error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.&nbsp; <BR>--- The

    error occurred while executing update procedure.&nbsp; <BR>--- Check the { ?

    = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?) }.&nbsp; <BR>---

    Check the output parameters (register output parameters failed).&nbsp; 
    <BR>--- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type 
    -99999999.<BR>Caused by: 
    com.ibatis.common.jdbc.exception.NestedSQLException:&nbsp;&nbsp; <BR>---
The 
    error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.&nbsp; <BR>--- The

    error occurred while executing update procedure.&nbsp; <BR>--- Check the { ?

    = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?) }.&nbsp; <BR>---

    Check the output parameters (register output parameters failed).&nbsp; 
    <BR><BR>I tried both with and without an extra ? in the in the stored 
    procedure call.<BR><BR>Cheers,<BR>&nbsp;Mike 
    <DIV><SPAN><BR><BR>
    <DIV><SPAN class=gmail_quote>On 21/05/07, <B class=gmail_sendername>Jeff

    Butler</B> &lt;<A onclick="return top.js.OpenExtLink(window,event,this)"

    href="mailto:jeffgbutler@gmail.com" target=_blank> jeffgbutler@gmail.com 
    </A>&gt; wrote:</SPAN> 
    <BLOCKQUOTE class=gmail_quote 
    style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px
solid">
      <P>It sounds like you're not registering an output parameter.&nbsp; Here's

      an example: </P>
      <P>&lt;sqlMap namespace="sqlMap"&gt;<BR>&nbsp; &lt;parameterMap

      class="map" id="theParameterMap"&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;parameter

      property="rc" javaType="java.lang.Integer" jdbcType="INTEGER" mode="OUT" 
      /&gt; <BR>&nbsp;&nbsp;&nbsp; &lt;parameter property="parm1"

      javaType="java.math.Integer" jdbcType="INTEGER" mode="IN" 
      /&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;parameter property="parm2"

      javaType="java.math.Integer " jdbcType="INTEGER" mode="IN" /&gt;<BR>&nbsp;

      &lt;/parameterMap&gt;</P>
      <P>&nbsp; &lt;procedure id="adder" 
      parameterMap="theParameterMap"&gt;<BR>&nbsp;&nbsp;&nbsp; {? =
call 
      some_proc (?,?)}<BR>&nbsp; &lt;/procedure&gt;<BR>&lt;/sqlMap&gt;</P><SPAN>
      <P>Jeff Butler&nbsp;</P></SPAN>
      <DIV><SPAN>
      <P>&nbsp;</P><BR><BR>
      <DIV><SPAN class=gmail_quote>On 5/21/07, <B class=gmail_sendername>Mikael

      Andersson</B> &lt;<A 
      onclick="return top.js.OpenExtLink(window,event,this)" 
      href="mailto:mail.micke@gmail.com" target=_blank>mail.micke@gmail.com 
      </A>&gt; wrote:</SPAN> 
      <BLOCKQUOTE class=gmail_quote 
      style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px
solid">Hi,<BR>I'm 
        running into problems getting the return code from a sybase stored 
        procedure as well. <BR><BR>I have tried the {? = call someproc(?,?)} 
        construct, but it gives me the following error message :<BR>Cause: 
        java.sql.SQLException : JZ0SC: Callable Statement: attempt to set the 
        return status as an input parameter.; <BR><BR>I'm no DB guru, quite the

        opposite, and I've tried various apporaches without success.<BR>Tried 
        defining a parameter element in the parameterMap, got an error message. 
        <BR><BR>I have read through what I think are the relevant bits in 
        "iBatis in Action", but haven't found a solution yet. <BR><BR>Does the

        stored procedure have to be created in a certain way to allow me to grab 
        a hold of the status code? <BR><BR>Help greatly appreciated,<BR>Mike

        <DIV><SPAN><BR><BR><BR><BR><BR>
        <DIV><SPAN class=gmail_quote>On 19/05/07, <B class=gmail_sendername>Jeff

        Butler</B> &lt;<A onclick="return top.js.OpenExtLink(window,event,this)"

        href="mailto:jeffgbutler@gmail.com" target=_blank>jeffgbutler@gmail.com 
        </A>&gt; wrote: </SPAN>
        <BLOCKQUOTE class=gmail_quote 
        style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204)
1px solid">
          <DIV>By return code, do you mean something that can be obtained like 
          this:</DIV>
          <DIV>&nbsp;</DIV>
          <DIV>{? = call someproc(?,?)}</DIV>
          <DIV>&nbsp;</DIV>
          <DIV>If so, then you can use the above syntax and register an output 
          parameter.</DIV><SPAN>
          <DIV>&nbsp;</DIV>
          <DIV>Jeff Butler<BR><BR>&nbsp;</DIV></SPAN>
          <DIV><SPAN>
          <DIV><SPAN class=gmail_quote>On 5/18/07, <B 
          class=gmail_sendername>BenBaril</B> &lt;<A 
          onclick="return top.js.OpenExtLink(window,event,this)" 
          href="mailto:benjamin.baril@fbn.ca" 
          target=_blank>benjamin.baril@fbn.ca</A> &gt; wrote:</SPAN> 
          <BLOCKQUOTE class=gmail_quote 
          style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: rgb(204,204,204)
1px solid"><BR>Hi 
            All,<BR><BR>I know this issue has come up before, but I'm curious
if 
            there is a<BR>solution. I am in the process of evaluating iBatis 
            (and we have pretty much <BR>decided on it) as the sql mapper for 
            our IT-Revitalization project. We have<BR>over 1000 Stored 
            Procedures in a Sybase database. So far we have gotten <BR>around 
            issues such as not having a handle on the connection to retrieve 
            <BR>RAISERROR messages from a stored procedure as well as limiting 
            the number of<BR>results.<BR><BR>Our problem right now is that
we 
            cannot retrieve the return code from an SP. <BR>We rely heavily on 
            return codes to determine why a stored procedure may have 
            <BR>failed, and we need these codes to propagate back up the Java 
            stack so we<BR>can have a handle on them.<BR><BR>Is there a
way to 
            do this using iBatis right now? <BR><BR>Thank you,<BR>Benjamin

            Baril<BR>--<BR>View this message in context: <A 
            onclick="return top.js.OpenExtLink(window,event,this)" 
            href="http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634"

            target=_blank>http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634

            </A><BR>Sent from the iBATIS - User - Java mailing list archive at

            <A onclick="return top.js.OpenExtLink(window,event,this)" 
            href="http://nabble.com/" 
          target=_blank>Nabble.com</A>.<BR><BR></BLOCKQUOTE></DIV><BR></SPAN></DIV></BLOCKQUOTE></DIV><BR></SPAN></DIV></BLOCKQUOTE></DIV><BR></SPAN></DIV></BLOCKQUOTE></DIV><BR></SPAN></DIV></BLOCKQUOTE></DIV><BR></SPAN></DIV></BLOCKQUOTE></DIV><BR></BODY></HTML>


Mime
View raw message