How to run a stored procedure with iBATIS.

[Fabiano Ferrari]

Hi, all.

I need some help to run a stored procedure with iBATIS. Sorry if this
question has already circulated in list.

When trying to run the procedure, I run into a problem. My database is
Oracle 10g Express Edition and I'm currently using iBATIS 2.0 Beta 4.

This is the output I get when invoking the procedure within a JUnit test case:

  1) testExecuteQueryProcedure(com.ibatis.sqlmap.engine.execution.SqlExecutorTest)com.ibatis.common.jdbc.exception.NestedSQLException:
  --- The error occurred in com/ibatis/sqlmap/maps/OracleProc-modified.xml.
  --- The error occurred while applying a parameter map.
  --- Check the getAccountEmail.
  --- Check the parameter mapping for the 'email' property.
  --- Cause: java.sql.SQLException: Cannot perform fetch on a PLSQL
statement: next


-------------------------------------------------

My JavaCode is:

  Map param = new HashMap();
  param.put("id",    new Integer(1));
  param.put("email", new String());

  String email = (String) sqlMap.queryForObject(
"getAccountEmailViaProcedure", param);



My SqlMap is:

  <parameterMap id="getAccountEmail" class="map" >
    <parameter property="id"    jdbcType="INTEGER"
javaType="java.lang.Integer" mode="INOUT"/>
    <parameter property="email" jdbcType="VARCHAR"
javaType="java.lang.String"  mode="OUT"/>
  </parameterMap>

  <procedure id="getAccountEmailViaProcedure" parameterMap="getAccountEmail">
     {call get_account_email ( ? ,? )}
  </procedure>


My Oracle procedure (which works fine when manually invoked) is:

  PROCEDURE get_account_email
    (id IN INTEGER, email OUT VARCHAR)
  IS
  BEGIN
    SELECT acc_email
    INTO email
    FROM account2
    where ACC_ID = id;
  END;

-------------------------------------------------

Thanks for any help.

  Fabiano

[Mikel sanchez]

Hi

 

Since you're only updating a parameter, I think you should use the update method for the sqlmap client:

 

sqlMap.update("getAccountEmailViaProcedure", param);

 

And then get the updated parameter from the parameter map itself:

 

String email = (String) param.get("email");

 

And the parameter "id" should be declared as IN, as it is not to be modified:

 

<parameter property="id"    jdbcType="INTEGER"
javaType="java.lang.Integer" mode="IN"/>

 

Hope it helps.

Calling stored procedures has been a real pain to me for a long time, no to mention custom typed parameters, handlers... :(

 

2008/8/21, Fabiano Ferrari <fcferrari@...>:

Hi, all.

I need some help to run a stored procedure with iBATIS. Sorry if this
question has already circulated in list.

When trying to run the procedure, I run into a problem. My database is
Oracle 10g Express Edition and I'm currently using iBATIS 2.0 Beta 4.

This is the output I get when invoking the procedure within a JUnit test case:

1) testExecuteQueryProcedure(com.ibatis.sqlmap.engine.execution.SqlExecutorTest)com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ibatis/sqlmap/maps/OracleProc-modified.xml.
--- The error occurred while applying a parameter map.
--- Check the getAccountEmail.
--- Check the parameter mapping for the 'email' property.
--- Cause: java.sql.SQLException: Cannot perform fetch on a PLSQL
statement: next


-------------------------------------------------

My JavaCode is:

Map param = new HashMap();
param.put("id",    new Integer(1));
param.put("email", new String());

String email = (String) sqlMap.queryForObject(
"getAccountEmailViaProcedure", param);



My SqlMap is:

<parameterMap id="getAccountEmail" class="map" >
   <parameter property="id"    jdbcType="INTEGER"
javaType="java.lang.Integer" mode="INOUT"/>
   <parameter property="email" jdbcType="VARCHAR"
javaType="java.lang.String"  mode="OUT"/>
</parameterMap>

<procedure id="getAccountEmailViaProcedure" parameterMap="getAccountEmail">
    {call get_account_email ( ? ,? )}
</procedure>


My Oracle procedure (which works fine when manually invoked) is:

PROCEDURE get_account_email
   (id IN INTEGER, email OUT VARCHAR)
IS
BEGIN
   SELECT acc_email
   INTO email
   FROM account2
   where ACC_ID = id;
END;

-------------------------------------------------

Thanks for any help.

Fabiano

[Moorthy GT]

Check this out for a sample

http://www.moorthyhome.com/blog/entry/ibatis_oracle_stored_procedure

Fabiano Ferrari wrote:

Hi, all.

I need some help to run a stored procedure with iBATIS. Sorry if this
question has already circulated in list.

When trying to run the procedure, I run into a problem. My database is
Oracle 10g Express Edition and I'm currently using iBATIS 2.0 Beta 4.

This is the output I get when invoking the procedure within a JUnit test case:

  1) testExecuteQueryProcedure(com.ibatis.sqlmap.engine.execution.SqlExecutorTest)com.ibatis.common.jdbc.exception.NestedSQLException:
  --- The error occurred in com/ibatis/sqlmap/maps/OracleProc-modified.xml.
  --- The error occurred while applying a parameter map.
  --- Check the getAccountEmail.
  --- Check the parameter mapping for the 'email' property.
  --- Cause: java.sql.SQLException: Cannot perform fetch on a PLSQL
statement: next


-------------------------------------------------

My JavaCode is:

  Map param = new HashMap();
  param.put("id",    new Integer(1));
  param.put("email", new String());

  String email = (String) sqlMap.queryForObject(
"getAccountEmailViaProcedure", param);



My SqlMap is:

  <parameterMap id="getAccountEmail" class="map" >
    <parameter property="id"    jdbcType="INTEGER"
javaType="java.lang.Integer" mode="INOUT"/>
    <parameter property="email" jdbcType="VARCHAR"
javaType="java.lang.String"  mode="OUT"/>
  </parameterMap>

  <procedure id="getAccountEmailViaProcedure" parameterMap="getAccountEmail">
     {call get_account_email ( ? ,? )}
  </procedure>


My Oracle procedure (which works fine when manually invoked) is:

  PROCEDURE get_account_email
    (id IN INTEGER, email OUT VARCHAR)
  IS
  BEGIN
    SELECT acc_email
    INTO email
    FROM account2
    where ACC_ID = id;
  END;

-------------------------------------------------

Thanks for any help.

  Fabiano