ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dave Rodenbaugh" <drodenba...@WILDBLUECORP.COM>
Subject RE: Understanding stored procedure return types
Date Thu, 15 Mar 2007 15:47:18 GMT
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
 

Mime
View raw message