You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Fabiano Ferrari <fc...@gmail.com> on 2008/08/21 03:22:44 UTC
How to run a stored procedure with iBATIS.
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
Re: How to run a stored procedure with iBATIS.
Posted by Moorthy GT <gt...@moorthyhome.com>.
Check this out for a sample
http://www.moorthyhome.com/blog/entry/ibatis_oracle_stored_procedure
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
>
>
--
View this message in context: http://www.nabble.com/How-to-run-a-stored-procedure-with-iBATIS.-tp19081003p26002021.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: How to run a stored procedure with iBATIS.
Posted by Mikel sanchez <mi...@gmail.com>.
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 <fc...@gmail.com>:
>
> 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
>