ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Problem implementing storeed function
Date Wed, 02 Mar 2005 15:42:42 GMT
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 <iahseek@sil.intnet.mu> 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
> 
>

Mime
View raw message