From user-java-return-11015-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org Thu Mar 15 16:25:37 2007 Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 88026 invoked from network); 15 Mar 2007 16:25:37 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 15 Mar 2007 16:25:37 -0000 Received: (qmail 53801 invoked by uid 500); 15 Mar 2007 16:25:42 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 53789 invoked by uid 500); 15 Mar 2007 16:25:42 -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 53778 invoked by uid 99); 15 Mar 2007 16:25:42 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 Mar 2007 09:25:42 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: neutral (herse.apache.org: local policy) Received: from [12.109.229.243] (HELO WBEXCH.WBADDC1.COM) (12.109.229.243) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 Mar 2007 09:25:31 -0700 Content-class: urn:content-classes:message Subject: RE: Understanding stored procedure return types MIME-Version: 1.0 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable Date: Thu, 15 Mar 2007 10:25:09 -0600 X-MimeOLE: Produced By Microsoft Exchange V6.5 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Understanding stored procedure return types Thread-Index: AcdnHiLC5iAdRSk6SB6FHOUAKq3sIQAACENg References: From: "Dave Rodenbaugh" To: X-Virus-Checked: Checked by ClamAV on apache.org Well, glad that it works...Personally, I wouldn't expect that to be a = cross-DB friendly solution, nor does it seem to be the "least = astonishing method" of accomplishing the goal. But hey, if you're cool = with it, that's what matters I guess. -D=20 -----Original Message----- From: Collin Peters [mailto:cadiolis@gmail.com]=20 Sent: Thursday, March 15, 2007 10:22 AM To: user-java@ibatis.apache.org Subject: Re: Understanding stored procedure return types I just tried this and it works (I am using PostgreSQL where you can use SELECT syntax to execute some SPs) wrote: > That's a question for the authors. I found this way to be the only = one that worked to invoke SPs. The docs pretty much spell it out that = way. > > I'm not sure how you'd invoke the SP in a ? > This seems much more efficient to me as I can take advantage of > sending a VO as a parameter without having to create custom classes > for each stored proc. > > On 3/15/07, Dave Rodenbaugh wrote: > > Hi Collin, > > > > I struggled with this for a bit as well...Here's how we're doing it = (and it works, which is always a bonus). > > > > SQLMap fragment: > > > > > > > > > > > > > > > > > > {call ?:=3D CDS_OWNER.ORDSVC_APP.PARTNER_REFERENCE_UNIQUE(?, = ?, ?)} > > > > > > Java invocation: > > > > HashMap paramMap =3D new HashMap(); > > paramMap.put("returnvaluecolumn", new String()); > > paramMap.put("partnerSystemIdIn", partnerSystemIdIn); > > paramMap.put("partnerReferenceIdIn", partnerReferenceIdIn); > > paramMap.put("trackingTypeIdIn", trackingTypeIdIn); > > > > //Invoke the SP > > queryForObject("partnerReferenceUnique", paramMap); > > return (java.lang.String)paramMap.get("returnvaluecolumn"); > > > > There's nothing magic in 'returnvaluecolumn'--that's just a name we = picked to be obvious. No need for ResultMap stuff, since there's only = one value coming back... > > > > Hope that helps, > > -Dave > > > > -----Original Message----- > > From: Collin Peters [mailto:cadiolis@gmail.com] > > Sent: Wednesday, March 14, 2007 6:16 PM > > To: user-java@ibatis.apache.org > > Subject: Understanding stored procedure return types > > > > Hi all, > > > > I am trying to figure out return types in stored procedures. I am > > using PostgreSQL and have a simple function called saveUser. Here = is > > the important parts of the stored procedure: > > > > CREATE OR REPLACE FUNCTION save_member_basic(in_user_id integer, > > in_enterprise_id integer, in_username text, in_password text, > > in_firstname text, in_lastname text) > > RETURNS integer AS > > $BODY$ > > DECLARE > > > > ... > > > > return _user_id; > > > > END > > $BODY$ > > LANGUAGE 'plpgsql' VOLATILE; > > > > So it is a stored procedure that has 6 arguments, and a single = integer > > return value. I have been able to successfully call the function = with > > this sqlmap: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > { call save_member_basic(?, ?, ?, ?, ?, ?) } > > > > > So this successfully calls the stored procedure, but seems to ignore > > the 'resultClass=3D"int"' attribute. So reading up on things I see = it > > should look like: > > { ? =3D call save_member_basic(?, ?, ?, ?, ?, ?) } > > But this seems to mean I need to have an extra value in my > > parameterMap, which would then mean I won't be able to send in my > > UserVO class as the parameter. Unless I add a return value variable > > to it or something. This seems to be a backwards way of doing = things. > > > > How come I can't use a resultClass with the procedure tag? = Something > > to do with being locked into how JDBC does it? This concept of an > > INOUT parameter is a bit foreign to me, I have never created a = stored > > procedure where the parameters matched the return value. I can see > > the value in that, but it doesn't apply to this situation. > > > > Collin > > > > -- > > No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM > > > > > > -- > > No virus found in this outgoing message. > > Checked by AVG Free Edition. > > Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM > > > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM > > --=20 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM =20 --=20 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.11/723 - Release Date: = 3/15/2007 11:27 AM =20