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