Return-Path: 
 <user-java-return-11014-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 87551 invoked from network); 15 Mar 2007 16:22:33 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 15 Mar 2007 16:22:33 -0000
Received: (qmail 46864 invoked by uid 500); 15 Mar 2007 16:22:39 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 46620 invoked by uid 500); 15 Mar 2007 16:22:38 -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 46609 invoked by uid 99); 15 Mar 2007 16:22:38 -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:22:38 -0700
X-ASF-Spam-Status: No, hits=-0.0 required=10.0
	tests=SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: domain of cadiolis@gmail.com designates
 66.249.92.174 as permitted sender)
Received: from [66.249.92.174] (HELO ug-out-1314.google.com) (66.249.92.174)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 Mar 2007 09:22:28 -0700
Received: by ug-out-1314.google.com with SMTP id m2so409522ugc
        for <user-java@ibatis.apache.org>;
 Thu, 15 Mar 2007 09:22:04 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed;
        d=gmail.com; s=beta;
        h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=ZDh41rClDyuLyKiCUojqWIn4gdCQvR8MOoCmySdW6jbC10Yz3/QE3xM/Tz2ZQCMAuIpNTPGcbYvy7I12YSrcM+9C2VYOe+UwmsyL5LfxQ2uCXDLvj4zbvICYTH+TGRgc+S1Gkx3aE5Lo8M32rYc6OE6mmNZP4AAAd4+0AHaqMQE=
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=ZBcp0sufRhi0OxWUo4uf6R7v/ngXePsg+W7TuomHe2NOlg4juD4IZxv5/V1OqG2YxnW4lK/zQKt5qPyenduRot0JjD/KJmg0vhRRWpfSyMP6C/mldxK5amNS2eS/c6wBPar/6Rz5zgOz3gelgeoNkSW6vA2+62fwBLV6ZUOhtJ4=
Received: by 10.115.14.1 with SMTP id r1mr278912wai.1173975722722;
        Thu, 15 Mar 2007 09:22:02 -0700 (PDT)
Received: by 10.114.37.14 with HTTP; Thu, 15 Mar 2007 09:22:02 -0700 (PDT)
Message-ID: <df01c91b0703150922x7f76c8a1j21d30aefecc18311@mail.gmail.com>
Date: Thu, 15 Mar 2007 09:22:02 -0700
From: "Collin Peters" <cadiolis@gmail.com>
To: user-java@ibatis.apache.org
Subject: Re: Understanding stored procedure return types
In-Reply-To: <BBE59FE5495780478335AE53DA80579B2F3CDD@WBEXCH.WBADDC1.COM>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <df01c91b0703141715p366333e3q762f94e199c4ce77@mail.gmail.com>
	 <BBE59FE5495780478335AE53DA80579B2F3CD5@WBEXCH.WBADDC1.COM>
	 <df01c91b0703150843m6d76f2bei9cb67fca06593398@mail.gmail.com>
	 <BBE59FE5495780478335AE53DA80579B2F3CDD@WBEXCH.WBADDC1.COM>
X-Virus-Checked: Checked by ClamAV on apache.org

I just tried this and it works (I am using PostgreSQL where you can
use SELECT syntax to execute some SPs)

	<parameterMap id="createUserParam" class="UserVO">
		<parameter property="userID" />
		<parameter property="enterpriseID" />
		<parameter property="username" />
		<parameter property="password" />
		<parameter property="firstName" />
		<parameter property="lastName" />
	</parameterMap>

	<resultMap id="hashMapResult" class="java.util.HashMap">
		<result property="result" column="result" />
	</resultMap>

	<select id="createUser" parameterMap="createUserParam"
resultMap="hashMapResult">
		SELECT	save_member_basic as result
		FROM		save_member_basic(?, ?, ?, ?, ?, ?);
	</select

On 3/15/07, Dave Rodenbaugh <drodenbaugh@wildbluecorp.com> 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 <select> tag anyhow, since the select is supposed to map into JDBC as a PreparedStatement.  SPs are special in that they must be called as CallableStatements, so I'm guessing that's the reason for two distinct tags (procedure vs. select).
>
> If your SP is just doing a select anyhow, why are you using an SP?  :)
>
> -D
>
> -----Original Message-----
> From: Collin Peters [mailto:cadiolis@gmail.com]
> Sent: Thursday, March 15, 2007 9:44 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Understanding stored procedure return types
>
> Thanks Dave.  I now understand what must be done to accomplish this,
> but I still don't understand why it needs to be done this way.  Is it
> considered bad practice to execute a stored procedures as a <select>?
> 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 <drodenbaugh@wildbluecorp.com> 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:
> >
> >     <parameterMap id='paramPartnerReferenceUnique' class='map'>
> >         <parameter property='returnvaluecolumn' jdbcType='VARCHAR' javaType='java.lang.String' mode='OUT'/>
> >         <parameter property='partnerSystemIdIn' jdbcType='VARCHAR' javaType='java.lang.String' mode='IN'/>
> >         <parameter property='partnerReferenceIdIn' jdbcType='VARCHAR' javaType='java.lang.String' mode='IN'/>
> >         <parameter property='trackingTypeIdIn' jdbcType='NUMERIC' javaType='java.lang.Long' mode='IN'/>
> >     </parameterMap>
> >     <procedure id='partnerReferenceUnique' parameterMap='paramPartnerReferenceUnique'>
> >         {call ?:= CDS_OWNER.ORDSVC_APP.PARTNER_REFERENCE_UNIQUE(?, ?, ?)}
> >     </procedure>
> >
> > Java invocation:
> >
> >         HashMap<String, Object> paramMap = 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
> >
> > -----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
> >
> > <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="UserVO" type="ca.mcrt.intouch.objects.UserVO" />
> >
> >         <resultMap id="userResult" class="UserVO" >
> >                 <result property="userID" column="user_id"/>
> >                 <result property="enterpriseID" column="enterprise_id"/>
> >                 <result property="firstName" column="firstname"/>
> >                 <result property="lastName" column="lastname"/>
> >                 <result property="username" column="username"/>
> >                 <result property="password" column="password"/>
> >         </resultMap
> >
> >         <parameterMap id="params-createUser" class="UserVO" >
> >                 <parameter property="userID" jdbcType="integer" mode="IN"/>
> >                 <parameter property="enterpriseID" jdbcType="integer" mode="IN"/>
> >                 <parameter property="username" jdbcType="text" mode="IN"/>
> >                 <parameter property="password" jdbcType="text" mode="IN"/>
> >                 <parameter property="firstName" jdbcType="text" mode="IN"/>
> >                 <parameter property="lastName" jdbcType="text" mode="IN"/>
> >         </parameterMap>
> >
> >         <procedure id="createUser" parameterMap="params-createUser" resultClass="int" >
> >                 { call save_member_basic(?, ?, ?, ?, ?, ?) }
> >         </procedure
> >
> > So this successfully calls the stored procedure, but seems to ignore
> > the 'resultClass="int"' attribute.  So reading up on things I see it
> > should look like:
> >                 { ? = 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
>
>