Calling Oracle function failure

[David Balažic]

Hi!

I am trying to get Oracle stored functions to work with mybatis 3.0.4,
without success.

I started from the example at
http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibatis/submitted/refcursor

And come up with this:

<select id="selectByPrimaryKey" resultMap="BaseResultMap"
parameterType="java.math.BigDecimal" >
<!-- this is @mbggenerated - I am trying to get the same function
by a stored function -->
select
<include refid="Base_Column_List" />
from USER1.EVENTS
where EVENT_ID = #{eventId,jdbcType=DECIMAL}
</select>
<update id="selectByPrimaryKeySP" parameterType="map"
statementType="CALLABLE">

{ #{order,jdbcType=CURSOR,mode=OUT,resultMap=BaseResultMap,javaType=java.sql.ResultSet}
=
call USER1.loadEventHB(#{eventId,jdbcType=DECIMAL,mode=IN}) }
</update>


The calling code is:
Map<String, Object> parameter = new HashMap<String, Object>();
parameter.put("eventId", new java.math.BigDecimal(23));

int res = sqlSession.update("foo.EventsMapper.selectByPrimaryKeySP",
parameter);

The log and exceptions are:
00:47:00.840 [main] DEBUG java.sql.Connection - ooo Connection Opened
00:47:01.199 [main] DEBUG java.sql.PreparedStatement - ==> Executing:
{ ? = call USER1.loadEventHB(?) }
00:47:01.199 [main] DEBUG java.sql.PreparedStatement - ==> Parameters:
23(BigDecimal)
00:47:01.215 [main] DEBUG java.sql.Connection - xxx Connection Closed
00:47:01.215 [main] DEBUG o.a.i.d.pooled.PooledDataSource - Returned
connection 15842168 to pool.
Exception in thread "main"
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.sql.SQLException: Malformed
SQL92 string at position: 6. Expecting "call"
### The error may involve foo.EventsMapper.selectByPrimaryKeySP-Inline
### The error occurred while setting parameters

The same SQL ( "{ ? = call USER1.loadEventHB(?) }" ) works fine when
used with hibernate or from Oracle sqlPlus tool.

Any idea what is going on?
I suspect the function return parameter (the first question mark) is
not set up properly.

Regards,
David

[David Balažic]

Mistery solved.

The SQL must be all in same line, then it works.
It's a long line, but it cant be divided if the newline is put inside
the mybatis parameters:

<update id="selectByPrimaryKeySP" parameterType="map"
statementType="CALLABLE">
{ #{order,jdbcType=CURSOR,mode=OUT,
resultMap=BaseResultMap,
javaType=java.sql.ResultSet
} = call USER1.loadEventHB(#{eventId,
jdbcType=DECIMAL,mode=IN}) }
</update>

One question remains: why does the example code use update instead of
select?
Because select can't handle the call syntax?

Regards,
David

[Jeff Butler]

You can call SP with all statement types (insert, update, select,
delete) . I use <select> for SPs that return result sets (not
refcursors), I use <update> for all others. That's just my
preference.

Another consideration is the MyBatis generally won't commit statements
called with <select> - so that might enter into your considerations
too.

Jeff Butler

[David Balažic]

AFAIK Oracle returns refcursor and never a result set as such.
You think it would work with a select statement?
Does is have any benefit?
(besides the commit)

Regards,
Davud

On Jan 27, 3:08 am, Jeff Butler <jeffgbut...@gmail.com> wrote:
> You can call SP with all statement types (insert, update, select,
> delete) .  I use <select> for SPs that return result sets (not
> refcursors), I use <update> for all others.  That's just my
> preference.
>
> Another consideration is the MyBatis generally won't commit statements
> called with <select> - so that might enter into your considerations
> too.
>
> Jeff Butler
>

[Jeff Butler]

It will work. One strange thing about using <select> is that MyBatis
expects there to be a result set returned - so you'll need to write
your mapper method like this:

Object callProc(Parameter parm);

And just ignore the returned value which will be null. If you try to
make it a void function, you'll get a weird error message about
returning null for a primitive return type.

Jeff Butler

[David Balažic]

And if I call it the "old way" : selectList("statement name",
parameter) ?

On Jan 27, 4:52 pm, Jeff Butler <jeffgbut...@gmail.com> wrote:
> It will work.  One strange thing about using <select> is that MyBatis
> expects there to be a result set returned - so you'll need to write
> your mapper method like this:
>
> Object callProc(Parameter parm);
>
> And just ignore the returned value which will be null.  If you try to
> make it a void function, you'll get a weird error message about
> returning null for a primitive return type.
>
> Jeff Butler
>