ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Collin Peters" <cadio...@gmail.com>
Subject Re: Understanding stored procedure return types
Date Thu, 15 Mar 2007 15:43:45 GMT
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
>
>

Mime
View raw message