Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 47878 invoked from network); 28 Aug 2007 23:23:59 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 28 Aug 2007 23:23:59 -0000 Received: (qmail 58169 invoked by uid 500); 28 Aug 2007 23:23:52 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 58149 invoked by uid 500); 28 Aug 2007 23:23:52 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 58138 invoked by uid 99); 28 Aug 2007 23:23:52 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Aug 2007 16:23:52 -0700 X-ASF-Spam-Status: No, hits=1.0 required=10.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [216.129.90.209] (HELO aston.electric.net) (216.129.90.209) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Aug 2007 23:23:46 +0000 Received: from root by aston.electric.net with emc1-ok (Exim 4.67) (envelope-from ) id 1IQAP5-0002nZ-Ug for user-java@ibatis.apache.org; Tue, 28 Aug 2007 16:23:23 -0700 Received: by emcmailer; Tue, 28 Aug 2007 16:23:23 -0700 Received: from [204.50.7.202] (helo=) by aston.electric.net with esmtps (SSLv3:RC4-MD5:128) (Exim 4.67) (envelope-from ) id 1IQAP4-0002nI-Vm for user-java@ibatis.apache.org; Tue, 28 Aug 2007 16:23:22 -0700 Subject: Re: How to get both of return value and return result From: Hill_XIE@hsbc.ca Date: Tue, 28 Aug 2007 16:22:50 -0700 To: "user-java" Importance: Normal MIME-Version: 1.0 Message-ID: X-MIMETrack: Serialize by Router on MAIL02/HKBC(Release 7.0.1 HF183|May 12, 2006) at 08/28/2007 04:23:22 PM, Serialize complete at 08/28/2007 04:23:22 PM Content-Type: multipart/alternative; boundary="----=_Part_174091_16288213.1188337459857" X-Outbound-IP: 204.50.7.202 X-Env-From: Hill_XIE@hsbc.ca X-Virus-Status: Scanned by VirusSMART (s) X-Virus-Status: Scanned by VirusSMART (c) X-Disclaimer-Added: DONE X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_174091_16288213.1188337459857 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Thank you for reply. I am using nested result map. The result map works wel= l if the return value is not checked. For example if the syntax of the call= able statement is: =C2=A0 =C2=A0 =C2=A0 =C2=A0 {call TEST_GETUSERSBYID (?)}=20 Then the result set can be mapped to the object without problem. I will try= your scenario (using output parameter instead of return value) to see if i= t works for me. Maybe I should also try not to use the nested result map to= o. Hill ----- Original Message ----- From: "Jeff Butler" [jeffgbutler@gmail.com] Sent: 08/28/2007 02:44 PM To: user-java@ibatis.apache.org Subject: Re: How to get both of return value and return result This should be supported. I just ran a quick test with MySql and everythin= g was OK (a SP with OUT parameters and a result set). This is not exactly your scenario, but the idea is the same. You did not share your result map - so it's hard to tell if there might be = a problem there or not. There is a known bug if you're using only nested result maps, or in certain circumstances where there are nulls in the resul= t set. Jeff Butler On 8/28/07, Hill_XIE@hsbc.ca wrote: > > > Hi, > > I am very sorry to repost this, because my original thread was occupied. > Here is my problem: > > I have a DB2 stored procedure returns a single value and a result set at > the same time, like this: > > DECLARE DB2_SP_SQL1 CURSOR WITH RETURN FOR > Select * FROM USERS WHERE UserID =3D pUserID; > > OPEN DB2_SP_SQL1; > RETURN 100; > > I tried many times, I can only get the return value (100) back, while the > return result set is always empty. Here is the iBatis configuration and j= ava > code: > > 1. iBatis Configuration: > > > javaType=3D"java.lang.Integer" mode=3D"OUT"/> > java.lang.Integer" mode=3D"IN"/> > > > "userProfileResult"> > {? =3D call TEST_GETUSERSBYID (?)} > > > 2. Java Code: > > HashMap parameters =3D new HashMap(); > Integer returnCode =3D new Integer(-1); > parameters.put("ReturnCode", returnCode); > parameters.put("UserID", new Integer(userID)); > > UserProfile userProfile =3D > (UserProfile)getSqlMapClientTemplate().queryForObject("getUserProfile", > parameters); > > 3. Result: userProfile is null, the ReturnCode in the HashMap is 100. > > After I debugged into the iBatis code, I found the code does populate som= e > data (I am not sure what data it is) into the resultset object. But * > rs.next()*returns false, so the code ignores the resultset and doesn't do > any mapping for the result set. > > Is it unsupported by iBatis? > > Thanks, > ------------------------------------------------------ > Hill Xie > > *************************************************************** > This email may contain confidential information, and is intended only for > the named recipient and may be privileged. Distribution or copying of thi= s > email by anyone other than the named recipient is prohibited. If you are = not > the named recipient, please notify us immediately and permanently destroy > this email and all copies of it. Internet email is not private, secure, o= r > reliable. No member of the HSBC Group is liable for any errors or omissio= ns > in the content or transmission of this email. Any opinions contained in t= his > email are solely those of the author and, unless clearly indicated otherw= ise > in writing, are not endorsed by any member of the HSBC Group. > *************************************************************** > Ce courriel peut renfermer des renseignements confidentiels et privil=C3= =A9gi=C3=A9s > et s'adresse au destinataire d=C3=A9sign=C3=A9 seulement. La distribution= ou la copie > de ce courriel par toute personne autre que le destinataire d=C3=A9sign= =C3=A9 est > interdite. Si vous n'=C3=AAtes pas le destinataire d=C3=A9sign=C3=A9, veu= illez nous en > aviser imm=C3=A9diatement et d=C3=A9truire de fa=C3=A7on permanente ce co= urriel ainsi que > toute copie de celui-ci. La transmission de courriel par Internet ne > constitue pas un mode de transmission confidentiel, s=C3=A9curitaire ou f= iable. > Aucun membre du Groupe HSBC ne sera responsable des erreurs ou des omissi= ons > relatives au contenu ou =C3=A0 la transmission de ce courriel. L'auteur d= e ce > courriel est seul responsable des opinions =C3=A9mises dans ce courriel, > lesquelles, =C3=A0 moins d'un avis contraire fourni par =C3=A9crit, ne so= nt pas > endoss=C3=A9es par aucun membre du Groupe HSBC. > *************************************************************** > > "SAVE PAPER - THINK BEFORE YOU PRINT!" > > "=C3=89CONOMISEZ LE PAPIER =E2=80=93 PENSEZ-Y =C3=80 DEUX FOIS AVANT D'IM= PRIMER!" > *************************************************************** This email may contain confidential information, and is intended only for t= he named recipient and may be privileged. Distribution or copying of this e= mail by anyone other than the named recipient is prohibited. If you are not= the named recipient, please notify us immediately and permanently destroy = this email and all copies of it. Internet email is not private, secure, or = reliable. No member of the HSBC Group is liable for any errors or omission= s in the content or transmission of this email. Any opinions contained in t= his email are solely those of the author and, unless clearly indicated othe= rwise in writing, are not endorsed by any member of the HSBC Group. *************************************************************** Ce courriel peut renfermer des renseignements confidentiels et privil=E9gi= =E9s et s'adresse au destinataire d=E9sign=E9 seulement. La distribution = ou la copie de ce courriel par toute personne autre que le destinataire d= =E9sign=E9 est interdite. Si vous n'=EAtes pas le destinataire d=E9sign=E9= , veuillez nous en aviser imm=E9diatement et d=E9truire de fa=E7on permanen= te ce courriel ainsi que toute copie de celui-ci. La transmission de courri= el par Internet ne constitue pas un mode de transmission confidentiel, s=E9= curitaire ou fiable. Aucun membre du Groupe HSBC ne sera responsable des er= reurs ou des omissions relatives au contenu ou =E0 la transmission de ce co= urriel. L'auteur de ce courriel est seul responsable des opinions =E9mises= dans ce courriel, lesquelles, =E0 moins d'un avis contraire fourni par = =E9crit, ne sont pas endoss=E9es par aucun membre du Groupe HSBC. *************************************************************** "SAVE PAPER - THINK BEFORE YOU PRINT!" "=C9CONOMISEZ LE PAPIER =96 PENSEZ-Y =C0 DEUX FOIS AVANT D'IMPRIMER!" ------=_Part_174091_16288213.1188337459857 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Thank you for reply. I am using nested result map. The = result map works well if the return value is not checked. For example if th= e syntax of the callable statement is:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 {call= TEST_GETUSERSBYID (?)}
Then the result set can be mapped to the object= without problem. I will try your scenario (using output parameter instead = of return value) to see if it works for me. Maybe I should also try not to = use the nested result map too.

Hill

 ----- Original Message -----
  From: "Jeff = Butler" [jeffgbutler@gmail.com]
  Sent: 08/28/2007 02:4= 4 PM
  To: user-java@ibatis.apache.org
  Subject:= Re: How to get both of return value and return result
This should be supported.  I just ran a quick test with MySql and= everything was OK (a SP with OUT parameters and a result set).  This = is not exactly your scenario, but the idea is the same.
 
You did not share your result map - so it's hard to tell if there = might be a problem there or not.  There is a known bug if you're u= sing only nested result maps, or in certain circumstances where there are n= ulls in the result set.
 
Jeff Butler

 
On 8/28/07, = Hill_XIE@hsbc.ca <Hill_XIE@hsbc.ca> wrote:

Hi,

I am very = sorry to repost this, because my original thread was occupied. Here is my p= roblem:

I have a DB2 stored pr= ocedure returns a single value and a result set at the same time, like this= :

&n= bsp;       DECLARE DB2_SP_SQL1 CURSOR WITH= RETURN FOR
&nb= sp;         &nbs= p;    Select * FROM USERS WHERE UserID =3D pUserID;

  &n= bsp;    OPEN DB2_SP_SQL1;
       RETURN 100;=

I tri= ed many times, I can only get the return value (100) back, while the return= result set is always empty. Here is the iBatis configuration and java code= :
1. iBatis Configuration:

        <paramete= rMap id=3D "parameter" class=3D"java.util.HashMap&= quot; > <= br>  &n= bsp;            <parameter property=3D"pReturnCode" jdbcType=3D"INTEGER" javaType =3D"jav= a.lang.Integer" mode=3D "OUT"/>       =  
               <parameter property=3D "pUSERID" jdbcType=3D"INTEGER" javaType=3D"java.lang.Integer"= mode= =3D"IN&= quot;/>
&nb= sp;       </paramet= erMap>

=         <procedure id =3D"get= UserProfile" parameterMap=3D<= font face=3D"Courier New" color=3D"#4200ff" size=3D"2"> "parameter" resultMap=3D<= font face=3D"Courier New" color=3D"#4200ff" size=3D"2">"userProfileRes= ult" >
    &n= bsp;          {? =3D call TEST_GETUSERSBYID (?)}
       </procedure>

2. Java Code:

  =      HashMap parameters =3D new HashMap();
       Integer returnCode =3D new Integer(-1);
  =      parameters.put("ReturnCode", returnCode);
       parameters.put("UserID", new Integ= er(userID));                =

 = ;      UserProfile userProfile =3D (UserProfile)getSqlMapCli= entTemplate().queryForObject("getUserProfile", parameters);
3. Result:  userProfile is null, the ReturnCode in the HashMap is 10= 0.
=        
After I debugged into the iBatis code, I fou= nd the code does populate some data (I am not sure what data it is) into th= e resultset object. But=20 rs.next()returns false, so the code ignores the result= set and doesn't do any mapping for the result set.

Is it unsupported by iBatis= ?
<= br>Thanks,
------------------------------------------------------
Hill Xie

************= ***************************************************
This email may conta= in confidential information, and is intended only for the named recipient a= nd may be privileged. Distribution or copying of this email by anyone other= than the named recipient is prohibited. If you are not the named recipient= , please notify us immediately and permanently destroy this email and all c= opies of it. Internet email is not private, secure, or reliable. No member = of the HSBC Group is liable for any errors or omissions in the content or t= ransmission of this email. Any opinions contained in this email are solely = those of the author and, unless clearly indicated otherwise in writing, are= not endorsed by any member of the HSBC Group.
***************************************************************
Ce c= ourriel peut renfermer des renseignements confidentiels et privil=C3=A9gi= =C3=A9s et s'adresse au destinataire d=C3=A9sign=C3=A9 seulement. La di= stribution ou la copie de ce courriel par toute personne autre que le desti= nataire d=C3=A9sign=C3=A9 est interdite. Si vous n'=C3=AAtes pas le des= tinataire d=C3=A9sign=C3=A9, veuillez nous en aviser imm=C3=A9diatement et = d=C3=A9truire de fa=C3=A7on permanente ce courriel ainsi que toute copie de= celui-ci. La transmission de courriel par Internet ne constitue pas un mod= e de transmission confidentiel, s=C3=A9curitaire ou fiable. Aucun membre du= Groupe HSBC ne sera responsable des erreurs ou des omissions relatives au = contenu ou =C3=A0 la transmission de ce courriel. L'auteur de ce courri= el est seul responsable des opinions =C3=A9mises dans ce courriel, lesquell= es, =C3=A0 moins d'un avis contraire fourni par =C3=A9crit, ne sont pas= endoss=C3=A9es par aucun membre du Groupe HSBC.
***************************************************************

= "SAVE PAPER - THINK BEFORE YOU PRINT!"

"=C3=89CONOMIS= EZ LE PAPIER =E2=80=93 PENSEZ-Y =C3=80 DEUX FOIS AVANT D'IMPRIMER!"= ;


***************************************************************
This email may contain confidential information, and is intended only for t= he named recipient and may be privileged. Distribution or copying of this e= mail by anyone other than the named recipient is prohibited. If you are not= the named recipient, please notify us immediately and permanently destroy = this email and all copies of it. Internet email is not private, secure, or = reliable. No member of the HSBC Group is liable for any errors or omission= s in the content or transmission of this email. Any opinions contained in t= his email are solely those of the author and, unless clearly indicated othe= rwise in writing, are not endorsed by any member of the HSBC Group.
***************************************************************
Ce courriel peut renfermer des renseignements confidentiels et privil=E9gi= =E9s et s'adresse au destinataire d=E9sign=E9 seulement. La distribution = ou la copie de ce courriel par toute personne autre que le destinataire d= =E9sign=E9 est interdite. Si vous n'=EAtes pas le destinataire d=E9sign=E9= , veuillez nous en aviser imm=E9diatement et d=E9truire de fa=E7on permanen= te ce courriel ainsi que toute copie de celui-ci. La transmission de courri= el par Internet ne constitue pas un mode de transmission confidentiel, s=E9= curitaire ou fiable. Aucun membre du Groupe HSBC ne sera responsable des er= reurs ou des omissions relatives au contenu ou =E0 la transmission de ce co= urriel. L'auteur de ce courriel est seul responsable des opinions =E9mises= dans ce courriel, lesquelles, =E0 moins d'un avis contraire fourni par = =E9crit, ne sont pas endoss=E9es par aucun membre du Groupe HSBC.
***************************************************************

"SAVE PAPER - THINK BEFORE YOU PRINT!"

"=C9CONOMISEZ LE PAPIER =96 PENSEZ-Y =C0 DEUX FOIS AVANT D'IMPRIMER!"
------=_Part_174091_16288213.1188337459857--