You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Collin Peters <ca...@gmail.com> on 2007/03/15 01:15:36 UTC
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
RE: Understanding stored procedure return types
Posted by Dave Rodenbaugh <dr...@WILDBLUECORP.COM>.
Well, glad that it works...Personally, I wouldn't expect that to be a cross-DB friendly solution, nor does it seem to be the "least astonishing method" of accomplishing the goal. But hey, if you're cool with it, that's what matters I guess.
-D
-----Original Message-----
From: Collin Peters [mailto:cadiolis@gmail.com]
Sent: Thursday, March 15, 2007 10:22 AM
To: user-java@ibatis.apache.org
Subject: Re: Understanding stored procedure return types
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 <dr...@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 <dr...@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
>
>
--
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
Re: Understanding stored procedure return types
Posted by Collin Peters <ca...@gmail.com>.
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 <dr...@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 <dr...@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
>
>
RE: Understanding stored procedure return types
Posted by Dave Rodenbaugh <dr...@WILDBLUECORP.COM>.
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 <dr...@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
Re: Understanding stored procedure return types
Posted by Collin Peters <ca...@gmail.com>.
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 <dr...@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
>
>
RE: Understanding stored procedure return types
Posted by Dave Rodenbaugh <dr...@WILDBLUECORP.COM>.
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