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 C....@ads.it on 2006/10/02 14:09:38 UTC
Stored procedures (package functions) returning a row
The function
function get_key_2
return Tipo_Dato.t_PK;
of the ut_Tipo_Dato package returns a row (specifically a row composed by a
single value).
A very simple JavaBean class has been created ChiaveBean and the XML has
been written as follows
<resultMap id="mapChiave"
class="it.finmatica.gpj.aa.frontebd.ChiaveBean">
<result property="id" column="TIPO_DATO_ID"/>
</resultMap>
<procedure id="get_key_2" resultMap="mapChiave">
{call ut_Tipo_Dato.get_key_2}
</procedure>
The Java code calls the queryForObject() to get a newly allocated
ChiaveBean object, the Java homologous for
the Tipo_Dato.t_PK record type, accordingly to
http://opensource.atlassian.com/confluence/oss/pages/diffpages.action?pageId=39&originalId=5835
ChiaveBean lChiaveBean = (ChiaveBean)
lSqlMapClient.queryForObject( "get_key_2", null );
Analysing the exception stack it would seem that calling queryForObject()
wrongly instructs the DB to call a package procedure instead of correctly
call a package function
(http://pls-00221.ora-code.com/)
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
it/finmatica/gpj/aa/frontebd/DizionarioTipiDatoTest.xml.
--- The error occurred while applying a parameter map.
--- Check the get_key_2-InlineParameterMap.
--- Check the statement (update procedure failed).
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(
GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(
GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
SqlMapExecutorDelegate.java:561)
1)
Is the interpretation of the diagnostic corerct?
2)
If so, what is wrong?
3)
How should be written both the XML config file and the Java code to run a
package function that returns a row? Is there a better way to call
get_key_2() ?
Thanks in advance for any help
ciao
Cesare
Re: Stored procedures (package functions) returning a row
Posted by Jeff Butler <je...@gmail.com>.
The developer's guide does refer you to the CallableStatement documentation
- and that is where the proper syntax is detailed (because it is standard
syntax in JDBC). iBATIS isn't doing anything magical, it is a fairly thin
wrapper over JDBC. So some JDBC knowledge will help a lot.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html
Jeff Butler
On 10/3/06, C.Zecca@ads.it <C....@ads.it> wrote:
>
> "Jeff Butler" <je...@gmail.com> scritti il 03/10/2006 14:59:55
>
> > There are two different issues here.
>
> [...]
>
> > 2. Only use queryForObject if the function returns a result set. If
> > it is just returning a single value, then call it with syntax like this:
> >
> > {? = call myFunction}
> >
> > Register an output parameter - not a resultMap - then call it with
> > the "update" method.
>
> I will investigate asap the doc and the list for the suggested approach
> (a search about "output parameter" and "? =" in the pdf Developer guide
> has just reported no occurrences :/)
> Anyway, please note that the returned value is actually a record type (a
> "row").
> I hope that it's possible to get, via output parameters, not only simple
> SQL types but record types (rows) as well.
>
>
> > Unfortunatley, the iBATIS stored procedure support is hard to
> > understand - it makes sense if you really understand JDBC, but
> > probably doesn't otherwise. The most important thing to remember is
> > this - when iBATIS uses the term "result" (resultMap, resultClass,
> > etc.) it ALWAYS refers to a result set. A single value returned
> > from a procedure or function is NOT a result in iBATIS - it is an
> > output parameter.
>
> Right, Jeff!
> I have no experience (or very little) about JDBC.
> I trusted some critics and colleagues that suggest iBatis because "very
> useful
> to approach a DB from Java code without concern for many tedious details
> that you have to face with direct
> programming of JDBC".
> Does it help (I mean for teh documentation)? I mean that ... there is one
> user, at least, that directly passed JDBC by.
> :)
>
>
> Let'me experiment.
> I will report any result as soon as possible
>
> ciao
>
Re: Stored procedures (package functions) returning a row
Posted by C....@ads.it.
C.Zecca@ads.it scritti il 03/10/2006 15:26:03
> "Jeff Butler" <je...@gmail.com> scritti il 03/10/2006 14:59:55
>
> > There are two different issues here.
>
> [...]
>
> > 2. Only use queryForObject if the function returns a result set. If
> > it is just returning a single value, then call it with syntax like
this:
> >
> > {? = call myFunction}
> >
> > Register an output parameter - not a resultMap - then call it with
> > the "update" method.
[...]
The Developer guide reports, about update() that
These methods are specifically meant for update statements (a.k.a.
non-query).
That said, it’s not impossible to execute an update statement using one
of the query methods below,
however this is an odd semantic and obviously driver dependent.
Its signature
public int update(String statementName, Object parameterObject)
throws SQLException
clearly indicates that is NOT possible to get any "row" via return value.
The only way might be via the Object parameterObject.
> I hope that it's possible to get, via output parameters, not only
> simple SQL types but record types (rows) as well.
There are some topics and related post about output parameters in the
mailing list
and calls of the form
{ ? = call functionName }
This one
http://www.mail-archive.com/user-java@ibatis.apache.org/msg00998.html
reports uses of the queryForObject (I guess that a result set is handled
via a Map)
not of the update method()
Furthermore, the definition fo the result is mapped onto a simple SQL type
<parameterMap id="getStringParameterMap" class="java.util.Map">
<parameter property="result" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT" />
Re: Stored procedures (package functions) returning a row
Posted by C....@ads.it.
"Jeff Butler" <je...@gmail.com> scritti il 03/10/2006 14:59:55
> There are two different issues here.
[...]
> 2. Only use queryForObject if the function returns a result set. If
> it is just returning a single value, then call it with syntax like this:
>
> {? = call myFunction}
>
> Register an output parameter - not a resultMap - then call it with
> the "update" method.
I will investigate asap the doc and the list for the suggested approach
(a search about "output parameter" and "? =" in the pdf Developer guide has
just reported no occurrences :/)
Anyway, please note that the returned value is actually a record type (a
"row").
I hope that it's possible to get, via output parameters, not only simple
SQL types but record types (rows) as well.
> Unfortunatley, the iBATIS stored procedure support is hard to
> understand - it makes sense if you really understand JDBC, but
> probably doesn't otherwise. The most important thing to remember is
> this - when iBATIS uses the term "result" (resultMap, resultClass,
> etc.) it ALWAYS refers to a result set. A single value returned
> from a procedure or function is NOT a result in iBATIS - it is an
> output parameter.
Right, Jeff!
I have no experience (or very little) about JDBC.
I trusted some critics and colleagues that suggest iBatis because "very
useful
to approach a DB from Java code without concern for many tedious details
that you have to face with direct
programming of JDBC".
Does it help (I mean for teh documentation)? I mean that ... there is one
user, at least, that directly passed JDBC by.
:)
Let'me experiment.
I will report any result as soon as possible
ciao
Re: Stored procedures (package functions) returning a row
Posted by Jeff Butler <je...@gmail.com>.
There are two different issues here.
1. Oracle can't find your function. I don't know how to resolve that one,
but you have to figure that out first. Maybe the function is not really in
the package/schema you've specified?
2. Only use queryForObject if the function returns a result set. If it is
just returning a single value, then call it with syntax like this:
{? = call myFunction}
Register an output parameter - not a resultMap - then call it with the
"update" method.
Unfortunatley, the iBATIS stored procedure support is hard to understand -
it makes sense if you really understand JDBC, but probably doesn't
otherwise. The most important thing to remember is this - when iBATIS uses
the term "result" (resultMap, resultClass, etc.) it ALWAYS refers to a
result set. A single value returned from a procedure or function is NOT a
result in iBATIS - it is an output parameter.
Jeff Butler
On 10/3/06, C.Zecca@ads.it <C....@ads.it> wrote:
>
> The log generated by log4j confirms that the PreparedStatement is
> correctly prepared
>
> DEBUG [main] - Created connection 22664464.
> DEBUG [main] - {conn-100000} Connection
> DEBUG [main] - {pstm-100001} PreparedStatement: {call
> ut_Tipo_Dato.ut_setup}
> DEBUG [main] - {pstm-100001} Parameters: []
> DEBUG [main] - {pstm-100001} Types: []
> DEBUG [main] - Returned connection 22664464 to pool.
> DEBUG [main] - Checked out connection 22664464 from pool.
> DEBUG [main] - {conn-100002} Connection
> DEBUG [main] - {pstm-100003} PreparedStatement: {call *
> ut_Tipo_dato.get_key_2*}
> DEBUG [main] - {pstm-100003} Parameters: []
> DEBUG [main] - {pstm-100003} Types: []
> DEBUG [main] - Returned connection 22664464 to pool.
> *com.ibatis.common.jdbc.exception.NestedSQLException*:
> --- The error occurred in
> it/finmatica/gpj/aa/frontebd/DizionarioTipiDatoTest.xml.
> --- The error occurred while applying a parameter map.
> --- Check the get_key_2-InlineParameterMap.
> --- Check the statement (update procedure failed).
> --- Cause: *java.sql.SQLException*: ORA-06550: line 1, column 7:
> PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> Caused by: *java.sql.SQLException*: ORA-06550: line 1, column 7:
> PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (*GeneralStatement.java:185*)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject
> (*GeneralStatement.java:104*)
> [...]
>
>
> Something is wrong on the stored code? It wouldn't seem.... A mini test
> harness written on-th-gfly runs correctly.
> The Oracle diagnostic does not mention 'UT_TIPO_DATO.GET_KEY_2' (instead
> it reports 'GET_KEY_2' without mentioning its package)
>
> Why does the diagnostic refer a parameterMap / the inline parameter of
> get_key_2?
> --- The error occurred while applying a parameter map.
> --- Check the get_key_2-InlineParameterMap.
>
> The XML config file does NOT mention any parameter
>
> <resultMap id="mapChiave" class="it.finmatica.gpj.aa.frontebd.ChiaveBean">
> <result property="id" column="TIPO_DATO_ID"/>
> </resultMap>
>
> <procedure id="get_key_2" resultMap="mapChiave">
> {call ut_Tipo_dato.get_key_2}
> </procedure>
>
> If, I've understood well the Jeff's suggestions
>
> http://opensource.atlassian.com/confluence/oss/pages/diffpages.action?pageId=39&originalId=5835
> a call to queryForObject is suitable when you get *only one* object as
> result.
>
> What is wrong?
> I'm confused.
>
Re: Stored procedures (package functions) returning a row
Posted by C....@ads.it.
The log generated by log4j confirms that the PreparedStatement is correctly
prepared
DEBUG [main] - Created connection 22664464.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement: {call
ut_Tipo_Dato.ut_setup}
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - Returned connection 22664464 to pool.
DEBUG [main] - Checked out connection 22664464 from pool.
DEBUG [main] - {conn-100002} Connection
DEBUG [main] - {pstm-100003} PreparedStatement: {call
ut_Tipo_dato.get_key_2}
DEBUG [main] - {pstm-100003} Parameters: []
DEBUG [main] - {pstm-100003} Types: []
DEBUG [main] - Returned connection 22664464 to pool.
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
it/finmatica/gpj/aa/frontebd/DizionarioTipiDatoTest.xml.
--- The error occurred while applying a parameter map.
--- Check the get_key_2-InlineParameterMap.
--- Check the statement (update procedure failed).
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(
GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(
GeneralStatement.java:104)
[...]
Something is wrong on the stored code? It wouldn't seem.... A mini test
harness written on-th-gfly runs correctly.
The Oracle diagnostic does not mention 'UT_TIPO_DATO.GET_KEY_2' (instead it
reports 'GET_KEY_2' without mentioning its package)
Why does the diagnostic refer a parameterMap / the inline parameter of
get_key_2?
--- The error occurred while applying a parameter map.
--- Check the get_key_2-InlineParameterMap.
The XML config file does NOT mention any parameter
<resultMap id="mapChiave"
class="it.finmatica.gpj.aa.frontebd.ChiaveBean">
<result property="id" column="TIPO_DATO_ID"/>
</resultMap>
<procedure id="get_key_2" resultMap="mapChiave">
{call ut_Tipo_dato.get_key_2}
</procedure>
If, I've understood well the Jeff's suggestions
http://opensource.atlassian.com/confluence/oss/pages/diffpages.action?pageId=39&originalId=5835
a call to queryForObject is suitable when you get only one object as
result.
What is wrong?
I'm confused.