Return-Path: 
 <user-java-return-11009-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 67578 invoked from network); 15 Mar 2007 15:34:37 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 15 Mar 2007 15:34:37 -0000
Received: (qmail 27643 invoked by uid 500); 15 Mar 2007 15:34:42 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 27613 invoked by uid 500); 15 Mar 2007 15:34:41 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 27602 invoked by uid 99); 15 Mar 2007 15:34:41 -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 08:34:41 -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 08:34:30 -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 09:34:09 -0600
X-MimeOLE: Produced By Microsoft Exchange V6.5
Message-ID: <BBE59FE5495780478335AE53DA80579B2F3CD5@WBEXCH.WBADDC1.COM>
In-Reply-To: <df01c91b0703141715p366333e3q762f94e199c4ce77@mail.gmail.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Understanding stored procedure return types
Thread-Index: AcdmlyBRnXtiu5ywQ6Gqk1oefJopeQAfy7qA
References: <df01c91b0703141715p366333e3q762f94e199c4ce77@mail.gmail.com>
From: "Dave Rodenbaugh" <drodenbaugh@WILDBLUECORP.COM>
To: <user-java@ibatis.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org

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:

    <parameterMap id=3D'paramPartnerReferenceUnique' class=3D'map'>
        <parameter property=3D'returnvaluecolumn' jdbcType=3D'VARCHAR' =
javaType=3D'java.lang.String' mode=3D'OUT'/>
        <parameter property=3D'partnerSystemIdIn' jdbcType=3D'VARCHAR' =
javaType=3D'java.lang.String' mode=3D'IN'/>
        <parameter property=3D'partnerReferenceIdIn' =
jdbcType=3D'VARCHAR' javaType=3D'java.lang.String' mode=3D'IN'/>
        <parameter property=3D'trackingTypeIdIn' jdbcType=3D'NUMERIC' =
javaType=3D'java.lang.Long' mode=3D'IN'/>
    </parameterMap>
    <procedure id=3D'partnerReferenceUnique' =
parameterMap=3D'paramPartnerReferenceUnique'>
        {call ?:=3D CDS_OWNER.ORDSVC_APP.PARTNER_REFERENCE_UNIQUE(?, ?, =
?)}
    </procedure>

Java invocation:

        HashMap<String, Object> paramMap =3D new HashMap<String, =
Object>();
        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=20

-----Original Message-----
From: Collin Peters [mailto:cadiolis@gmail.com]=20
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

<snip>...

	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:

	<typeAlias alias=3D"UserVO" type=3D"ca.mcrt.intouch.objects.UserVO" />

	<resultMap id=3D"userResult" class=3D"UserVO" >
		<result property=3D"userID" column=3D"user_id"/>
		<result property=3D"enterpriseID" column=3D"enterprise_id"/>
		<result property=3D"firstName" column=3D"firstname"/>
		<result property=3D"lastName" column=3D"lastname"/>
		<result property=3D"username" column=3D"username"/>
		<result property=3D"password" column=3D"password"/>
	</resultMap

	<parameterMap id=3D"params-createUser" class=3D"UserVO" >
		<parameter property=3D"userID" jdbcType=3D"integer" mode=3D"IN"/>
		<parameter property=3D"enterpriseID" jdbcType=3D"integer" =
mode=3D"IN"/>
		<parameter property=3D"username" jdbcType=3D"text" mode=3D"IN"/>
		<parameter property=3D"password" jdbcType=3D"text" mode=3D"IN"/>
		<parameter property=3D"firstName" jdbcType=3D"text" mode=3D"IN"/>
		<parameter property=3D"lastName" jdbcType=3D"text" mode=3D"IN"/>
	</parameterMap>

	<procedure id=3D"createUser" parameterMap=3D"params-createUser" =
resultClass=3D"int" >
		{ call save_member_basic(?, ?, ?, ?, ?, ?) }
	</procedure

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

--=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