RE: Understanding stored procedure return types

[Dave Rodenbaugh]

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