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
