I am forwarding this to the ibatis list. You sent it to me only:
Hi,
I am sorry for not being more implicit about the problem
encountered.
I'va a function in Oracle (stand-alone, not packaged) called
'Check_Unique'
and it takes a set of parameters and returns a boolean value (the
function
and corresponding procedure implementation works, see below). The
implemention from the XML map is shown below:
Problem 1:
-----------
<parameterMap id="checkUniqueParameters" class="map">
<parameter property="p_tname" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname1" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue1" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname2" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue2" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname3" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue3" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname4" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue4" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname5" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue5" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="no_pk" jdbcType="NUMERIC"
javaType="java.lang.Integer" mode="IN"/>
</parameterMap>
<procedure id="checkUnique" parameterMap="checkUniqueParameters"
resultClass="boolean">
{? = call check_unique(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
</procedure>
I used a HashMap in my SQLMapDao class for binding the parameter
values
into the Ibatis paramater map:
Boolean i = (Boolean) queryForObject("checkUnique", map);
Unfortunately, when I execute the above function, the above statement
fails, since Oracle cannot bind the RETURN value of the function(whether
the
value is integer or boolean). How to overcome this problem?
Problem 2:
-----------
Now for a second example, I've rewritten the Oracle function as
procedure,
with return value as an INOUT param:
<parameterMap id="checkUniqueParameters" class="map">
<parameter property="p_tname" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname1" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue1" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname2" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue2" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname3" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue3" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname4" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue4" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colname5" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="p_colvalue5" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
<parameter property="no_pk" jdbcType="NUMERIC"
javaType="java.lang.Integer" mode="IN"/>
<parameter property="ret_val" jdbcType="NUMERIC"
javaType="java.lang.Integer" mode="INOUT"/>
</parameterMap>
<procedure id="checkUnique" parameterMap="checkUniqueParameters"
resultClass="boolean">
{ call check_unique(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
</procedure>
This time there's no error calling the above statement, but now the
value
returned in the +ve/-ve test cases is the SAME before and after the
statement
execution.
Sample of SQLMapDao class:
Boolean i = new Boolean(true);
.
.
System.out.println("ret_val 1 " + i.toString());
Integer result = (Integer) queryForObject("checkUnique", map);
System.out.println("result " + result.toString());
Are my implementation / mappings OK for the procedure?
Could you please help me on these matters? Thank you anyway.
Regards,
Imtiaz
On Wed, 02 Mar 2005 13:19:27 +0400, Imtiaz <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I am sorry for not being more implicit about the problem
> encountered.
> I'va a function in Oracle (stand-alone, not packaged) called
> 'Check_Unique'
> and it takes a set of parameters and returns a boolean value (the
> function
> and corresponding procedure implementation works, see below). The
> implemention from the XML map is shown below:
>
> Problem 1:
> -----------
>
> <parameterMap id="checkUniqueParameters" class="map">
> <parameter property="p_tname" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname1" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue1" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname2" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue2" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname3" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue3" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname4" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue4" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname5" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue5" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="no_pk" jdbcType="NUMERIC"
> javaType="java.lang.Integer" mode="IN"/>
> </parameterMap>
>
> <procedure id="checkUnique" parameterMap="checkUniqueParameters"
> resultClass="boolean">
>
> {? = call check_unique(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
>
> </procedure>
>
> I used a HashMap in my SQLMapDao class for binding the parameter
> values
> into the Ibatis paramater map:
>
> Boolean i = (Boolean) queryForObject("checkUnique", map);
>
> Unfortunately, when I execute the above function, the above statement
> fails, since Oracle cannot bind the RETURN value of the function(whether
> the
> value is integer or boolean). How to overcome this problem?
>
> Problem 2:
> -----------
>
> Now for a second example, I've rewritten the Oracle function as
> procedure,
> with return value as an INOUT param:
>
> <parameterMap id="checkUniqueParameters" class="map">
> <parameter property="p_tname" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname1" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue1" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname2" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue2" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname3" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue3" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname4" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue4" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colname5" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="p_colvalue5" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN"/>
> <parameter property="no_pk" jdbcType="NUMERIC"
> javaType="java.lang.Integer" mode="IN"/>
> <parameter property="ret_val" jdbcType="NUMERIC"
> javaType="java.lang.Integer" mode="INOUT"/>
> </parameterMap>
>
> <procedure id="checkUnique" parameterMap="checkUniqueParameters"
> resultClass="boolean">
>
> { call check_unique(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
>
> </procedure>
>
> This time there's no error calling the above statement, but now the
> value
> returned in the +ve/-ve test cases is the SAME before and after the
> statement
> execution.
> Sample of SQLMapDao class:
>
> Boolean i = new Boolean(true);
> .
> .
> System.out.println("ret_val 1 " + i.toString());
> Integer result = (Integer) queryForObject("checkUnique", map);
> System.out.println("result " + result.toString());
>
> Are my implementation / mappings OK for the procedure?
> Could you please help me on these matters? Thank you anyway.
>
> Regards,
> Imtiaz
>
>
