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 Ian Zabel <ia...@ezabel.com> on 2008/08/12 23:41:04 UTC
Really Weird Oracle REF Cursor Behavior
Hello,
Using the latest build of iBATIS and Oracle REF Cursors, we're running
into a problem with certain rows not coming back in the ResultSet. It
seems to be certain data in the rows that makes them not return.
For example, we have a column called INTERNAL_INVOICE_NBR. With the
following values in that column, ROW 1 does _not_ return in the
ResultSet from the REF Cursor when using iBATIS.
ROW 1: mts - Sample Invoice, b2325
ROW 2: mts - New Invoice, b2330
However, if we update the column in ROW 1 with the value from ROW 2,
then the row returns fine.
Also, when using straight JDBC, the rows are returned.
I've tried disabling cacheModels, enhancement, and lazyLoading, to no
avail. When debugging into the iBATIS
SqlExecutor.retrieveOutputParameters method, the ResultSet that it
gets on line 399 does not include the row. In fact, if this row is the
only row returned by the query, the ResultSet has no results at all.
Any ideas? I'm really confused because it works fine with JDBC using
the same connection as iBATIS, and if we change the data somewhat,
then it returns fine.
Thanks,
Ian.
Re: Really Weird Oracle REF Cursor Behavior
Posted by Clinton Begin <cl...@gmail.com>.
Thanks for closing the loop. It's nice to not have potential sporadic
behavior.
On Fri, Aug 22, 2008 at 1:35 PM, Ian Zabel <ia...@ezabel.com> wrote:
> I finally got back to look at this, and discovered the problem.
>
> One of the <parameter>s in the <parameterMap> was defined in the xml
> as the incorrect JDBC and Java type.
>
> It was:
> <parameter property="invoiceNbr" javaType="Integer" jdbcType="NM"
> mode="IN"/>
>
> When it should have been:
> <parameter property="invoiceNbr" javaType="String" jdbcType="VARCHAR"
> mode="IN"/>
>
> Since the stored proc was actually doing some char operations on that
> input parameter, this seemed to have been screwing up the result set
> depending on the data. So the REF CURSOR was returning zero rows
> because the input data was somehow messed up based on the type
> information being sent from the iBATIS query. When I tested with JDBC,
> I was correctly calling stmt.setString() for that variable.
>
> Lesson learned: Always make sure you're defining the correct types for
> your parameters.
>
> I feel better now!
>
> Thanks,
> Ian.
>
> On Tue, Aug 12, 2008 at 5:41 PM, Ian Zabel <ia...@ezabel.com> wrote:
> > Hello,
> >
> > Using the latest build of iBATIS and Oracle REF Cursors, we're running
> > into a problem with certain rows not coming back in the ResultSet. It
> > seems to be certain data in the rows that makes them not return.
> >
> > For example, we have a column called INTERNAL_INVOICE_NBR. With the
> > following values in that column, ROW 1 does _not_ return in the
> > ResultSet from the REF Cursor when using iBATIS.
> > ROW 1: mts - Sample Invoice, b2325
> > ROW 2: mts - New Invoice, b2330
> >
> > However, if we update the column in ROW 1 with the value from ROW 2,
> > then the row returns fine.
> >
> > Also, when using straight JDBC, the rows are returned.
> >
> > I've tried disabling cacheModels, enhancement, and lazyLoading, to no
> > avail. When debugging into the iBATIS
> > SqlExecutor.retrieveOutputParameters method, the ResultSet that it
> > gets on line 399 does not include the row. In fact, if this row is the
> > only row returned by the query, the ResultSet has no results at all.
> >
> > Any ideas? I'm really confused because it works fine with JDBC using
> > the same connection as iBATIS, and if we change the data somewhat,
> > then it returns fine.
> >
> > Thanks,
> > Ian.
> >
>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Ian Zabel <ia...@ezabel.com>.
I finally got back to look at this, and discovered the problem.
One of the <parameter>s in the <parameterMap> was defined in the xml
as the incorrect JDBC and Java type.
It was:
<parameter property="invoiceNbr" javaType="Integer" jdbcType="NM" mode="IN"/>
When it should have been:
<parameter property="invoiceNbr" javaType="String" jdbcType="VARCHAR"
mode="IN"/>
Since the stored proc was actually doing some char operations on that
input parameter, this seemed to have been screwing up the result set
depending on the data. So the REF CURSOR was returning zero rows
because the input data was somehow messed up based on the type
information being sent from the iBATIS query. When I tested with JDBC,
I was correctly calling stmt.setString() for that variable.
Lesson learned: Always make sure you're defining the correct types for
your parameters.
I feel better now!
Thanks,
Ian.
On Tue, Aug 12, 2008 at 5:41 PM, Ian Zabel <ia...@ezabel.com> wrote:
> Hello,
>
> Using the latest build of iBATIS and Oracle REF Cursors, we're running
> into a problem with certain rows not coming back in the ResultSet. It
> seems to be certain data in the rows that makes them not return.
>
> For example, we have a column called INTERNAL_INVOICE_NBR. With the
> following values in that column, ROW 1 does _not_ return in the
> ResultSet from the REF Cursor when using iBATIS.
> ROW 1: mts - Sample Invoice, b2325
> ROW 2: mts - New Invoice, b2330
>
> However, if we update the column in ROW 1 with the value from ROW 2,
> then the row returns fine.
>
> Also, when using straight JDBC, the rows are returned.
>
> I've tried disabling cacheModels, enhancement, and lazyLoading, to no
> avail. When debugging into the iBATIS
> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
> gets on line 399 does not include the row. In fact, if this row is the
> only row returned by the query, the ResultSet has no results at all.
>
> Any ideas? I'm really confused because it works fine with JDBC using
> the same connection as iBATIS, and if we change the data somewhat,
> then it returns fine.
>
> Thanks,
> Ian.
>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Jeff Stahl <je...@earthlink.net>.
I haven't had a chance to test the 2.3.x release. Has the problem with
Oracle ref cursors been fixed then? It's not that we couldn't move
forward (we discovered that stored functions work just fine nearly a
year ago, and that's our standard approach); in fact we have 6 projects
using the same approach.
Jeff Stahl
Clinton Begin wrote:
> It shouldn't... :-/
>
> Can you provide a failing test case?
>
> On Tue, Aug 12, 2008 at 3:57 PM, Jeff Stahl <jestahl@earthlink.net
> <ma...@earthlink.net>> wrote:
>
> iBATIS seems to have a problem with handling RefCursors as OUT
> parameters in stored procedures. I don't know how you're
> attempting to use iBATIS in your project, but in house here we've
> found that stored functions (where the Cursor is returned from the
> function and not fetched into an out parameter) works best.
>
> Hope This Helps,
>
> Jeff Stahl
>
>
> Ian Zabel wrote:
>
> Hello,
>
> Using the latest build of iBATIS and Oracle REF Cursors, we're
> running
> into a problem with certain rows not coming back in the
> ResultSet. It
> seems to be certain data in the rows that makes them not return.
>
> For example, we have a column called INTERNAL_INVOICE_NBR.
> With the
> following values in that column, ROW 1 does _not_ return in the
> ResultSet from the REF Cursor when using iBATIS.
> ROW 1: mts - Sample Invoice, b2325
> ROW 2: mts - New Invoice, b2330
>
> However, if we update the column in ROW 1 with the value from
> ROW 2,
> then the row returns fine.
>
> Also, when using straight JDBC, the rows are returned.
>
> I've tried disabling cacheModels, enhancement, and
> lazyLoading, to no
> avail. When debugging into the iBATIS
> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
> gets on line 399 does not include the row. In fact, if this
> row is the
> only row returned by the query, the ResultSet has no results
> at all.
>
> Any ideas? I'm really confused because it works fine with JDBC
> using
> the same connection as iBATIS, and if we change the data somewhat,
> then it returns fine.
>
> Thanks,
> Ian.
>
>
>
>
>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Clinton Begin <cl...@gmail.com>.
It shouldn't... :-/
Can you provide a failing test case?
On Tue, Aug 12, 2008 at 3:57 PM, Jeff Stahl <je...@earthlink.net> wrote:
> iBATIS seems to have a problem with handling RefCursors as OUT parameters
> in stored procedures. I don't know how you're attempting to use iBATIS in
> your project, but in house here we've found that stored functions (where the
> Cursor is returned from the function and not fetched into an out parameter)
> works best.
>
> Hope This Helps,
>
> Jeff Stahl
>
>
> Ian Zabel wrote:
>
>> Hello,
>>
>> Using the latest build of iBATIS and Oracle REF Cursors, we're running
>> into a problem with certain rows not coming back in the ResultSet. It
>> seems to be certain data in the rows that makes them not return.
>>
>> For example, we have a column called INTERNAL_INVOICE_NBR. With the
>> following values in that column, ROW 1 does _not_ return in the
>> ResultSet from the REF Cursor when using iBATIS.
>> ROW 1: mts - Sample Invoice, b2325
>> ROW 2: mts - New Invoice, b2330
>>
>> However, if we update the column in ROW 1 with the value from ROW 2,
>> then the row returns fine.
>>
>> Also, when using straight JDBC, the rows are returned.
>>
>> I've tried disabling cacheModels, enhancement, and lazyLoading, to no
>> avail. When debugging into the iBATIS
>> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
>> gets on line 399 does not include the row. In fact, if this row is the
>> only row returned by the query, the ResultSet has no results at all.
>>
>> Any ideas? I'm really confused because it works fine with JDBC using
>> the same connection as iBATIS, and if we change the data somewhat,
>> then it returns fine.
>>
>> Thanks,
>> Ian.
>>
>>
>>
>>
>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Ian Zabel <ia...@ezabel.com>.
Very interesting. How do you map something like that?
Thanks,
Ian.
On Aug 12, 2008, at 5:57 PM, Jeff Stahl wrote:
> iBATIS seems to have a problem with handling RefCursors as OUT
> parameters in stored procedures. I don't know how you're attempting
> to use iBATIS in your project, but in house here we've found that
> stored functions (where the Cursor is returned from the function and
> not fetched into an out parameter) works best.
>
> Hope This Helps,
>
> Jeff Stahl
>
> Ian Zabel wrote:
>> Hello,
>>
>> Using the latest build of iBATIS and Oracle REF Cursors, we're
>> running
>> into a problem with certain rows not coming back in the ResultSet. It
>> seems to be certain data in the rows that makes them not return.
>>
>> For example, we have a column called INTERNAL_INVOICE_NBR. With the
>> following values in that column, ROW 1 does _not_ return in the
>> ResultSet from the REF Cursor when using iBATIS.
>> ROW 1: mts - Sample Invoice, b2325
>> ROW 2: mts - New Invoice, b2330
>>
>> However, if we update the column in ROW 1 with the value from ROW 2,
>> then the row returns fine.
>>
>> Also, when using straight JDBC, the rows are returned.
>>
>> I've tried disabling cacheModels, enhancement, and lazyLoading, to no
>> avail. When debugging into the iBATIS
>> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
>> gets on line 399 does not include the row. In fact, if this row is
>> the
>> only row returned by the query, the ResultSet has no results at all.
>>
>> Any ideas? I'm really confused because it works fine with JDBC using
>> the same connection as iBATIS, and if we change the data somewhat,
>> then it returns fine.
>>
>> Thanks,
>> Ian.
>>
>>
>>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Sundar Sankar <fa...@gmail.com>.
Could you send your Ibatis configs. I am having data like those successfully
processed.
On Tue, Aug 12, 2008 at 2:57 PM, Jeff Stahl <je...@earthlink.net> wrote:
> iBATIS seems to have a problem with handling RefCursors as OUT parameters
> in stored procedures. I don't know how you're attempting to use iBATIS in
> your project, but in house here we've found that stored functions (where the
> Cursor is returned from the function and not fetched into an out parameter)
> works best.
>
> Hope This Helps,
>
> Jeff Stahl
>
>
> Ian Zabel wrote:
>
>> Hello,
>>
>> Using the latest build of iBATIS and Oracle REF Cursors, we're running
>> into a problem with certain rows not coming back in the ResultSet. It
>> seems to be certain data in the rows that makes them not return.
>>
>> For example, we have a column called INTERNAL_INVOICE_NBR. With the
>> following values in that column, ROW 1 does _not_ return in the
>> ResultSet from the REF Cursor when using iBATIS.
>> ROW 1: mts - Sample Invoice, b2325
>> ROW 2: mts - New Invoice, b2330
>>
>> However, if we update the column in ROW 1 with the value from ROW 2,
>> then the row returns fine.
>>
>> Also, when using straight JDBC, the rows are returned.
>>
>> I've tried disabling cacheModels, enhancement, and lazyLoading, to no
>> avail. When debugging into the iBATIS
>> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
>> gets on line 399 does not include the row. In fact, if this row is the
>> only row returned by the query, the ResultSet has no results at all.
>>
>> Any ideas? I'm really confused because it works fine with JDBC using
>> the same connection as iBATIS, and if we change the data somewhat,
>> then it returns fine.
>>
>> Thanks,
>> Ian.
>>
>>
>>
>>
>
Re: Really Weird Oracle REF Cursor Behavior
Posted by Jeff Stahl <je...@earthlink.net>.
iBATIS seems to have a problem with handling RefCursors as OUT
parameters in stored procedures. I don't know how you're attempting to
use iBATIS in your project, but in house here we've found that stored
functions (where the Cursor is returned from the function and not
fetched into an out parameter) works best.
Hope This Helps,
Jeff Stahl
Ian Zabel wrote:
> Hello,
>
> Using the latest build of iBATIS and Oracle REF Cursors, we're running
> into a problem with certain rows not coming back in the ResultSet. It
> seems to be certain data in the rows that makes them not return.
>
> For example, we have a column called INTERNAL_INVOICE_NBR. With the
> following values in that column, ROW 1 does _not_ return in the
> ResultSet from the REF Cursor when using iBATIS.
> ROW 1: mts - Sample Invoice, b2325
> ROW 2: mts - New Invoice, b2330
>
> However, if we update the column in ROW 1 with the value from ROW 2,
> then the row returns fine.
>
> Also, when using straight JDBC, the rows are returned.
>
> I've tried disabling cacheModels, enhancement, and lazyLoading, to no
> avail. When debugging into the iBATIS
> SqlExecutor.retrieveOutputParameters method, the ResultSet that it
> gets on line 399 does not include the row. In fact, if this row is the
> only row returned by the query, the ResultSet has no results at all.
>
> Any ideas? I'm really confused because it works fine with JDBC using
> the same connection as iBATIS, and if we change the data somewhat,
> then it returns fine.
>
> Thanks,
> Ian.
>
>
>