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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> 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
