RE: queryForPaginatedList, stored procedures and output parameter s

[Eric Williams <er...@sandiego.com>]

I've been investigating this problem further, and I've found more
information but I'm still somewhat lost as to what to do. This may or may
not be a bug in iBATIS.

Re: queryForPaginatedList, stored procedures and output parameter s

[Brandon Goodin <br...@gmail.com>]

Perhaps we could make this an optional setting for using PaginatedList
in concert with a Stored Procedure that has return parameters.
Although the performance gain that you are receiving from the
PaginatesList is diminished by having to traverse the whole resultset
in order to retrieve your output parameters. Why not use a
queryForList and handle the paging manually for now?

Brandon


On Fri, 17 Dec 2004 11:04:40 -0800, Eric Williams <er...@sandiego.com> wrote:
> I've been investigating this problem further, and I've found more
> information but I'm still somewhat lost as to what to do. This may or may
> not be a bug in iBATIS.
> 
> From what I can tell, JDBC expects (or at least recommends) that all rows in
> a result set be processed before output parameters are grabbed. As stated in
> the java.sql.CallableStatement documentation: "For maximum portability, a
> call's ResultSet objects and update counts should be processed prior to
> getting the values of output parameters."
> (http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html)
> 
> Please correct me if I'm wrong, but my debugging indicates that iBATIS does
> not do this. When using queryForPaginatedList, a scrollable result set is
> created, n results are grabbed for the first "page", then output parameters
> are processed before the end of the result set has been reached, while it is
> still open (this assumes, of course, that you have more than one "page" of
> results, which I do).
> 
> I'm using the jTDS driver for SQL Server. Its FAQ discusses this very issue.
> After quoting the JDBC documentation as I have above, it states: "Although
> this means that a 'good' driver could 'fix' this behavior, fixing it would
> imply caching the whole server response, equaling a huge performance drop.
> The exception is thrown because it's better to warn the user that the output
> parameters are not yet set instead of leaving the impression that all is ok
> and cause other, harder to detect problems."
> (http://jtds.sourceforge.net/faq.html#callableStatementOutput)
> 
> The JDBC docs do mention "portability", and maybe its the case that SQL
> Server is the issue here because it packages up its response in this order:
> ResultSets, update counts, output parameters, return values.
> 
> So, I assume either:
>        1) You have a driver that processes all results and returns the
> output parameters, even if you're trying to use a scrollable result set,
> which as the jTDS documentation asserts could result in a huge performance
> loss, or
>        2) You have a driver like jTDS that won't allow this and throws an
> error (my problem), or
>        3) You don't use output parameters (not an option for me), or
>        4) You use another driver/database combo (not happening)
> 
> I don't know if this is a bug in iBATIS, per se, but it does appear that
> iBATIS does not follow the letter of the law (or is it simply a
> recommendation?) when it tries to get output parameters before it has
> reached the end of a result set.
> 
> Any ideas on this? Even if I don't use iBATIS (which if I can't solve this
> problem will very unfortunately be the case), I'm going to have to deal with
> this in my own JDBC code. Basically, it means implementing paging inside my
> stored procedures, which is a less elegant solution than scrollable result
> sets (at least for my application).
> 
> Regards,
>        Eric
> 
> -----Original Message-----
> From: Eric Williams [mailto:ericw@sandiego.com]
> Sent: Wednesday, December 15, 2004 12:13 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: queryForPaginatedList, stored procedures and output parameters
> 
> I'm having trouble building PaginatedLists from stored procedures that use
> output parameters. If I don't use output parameters, the problems don't
> occur.
> 
> I'm getting this error:
> 
>        java.sql.SQLException: Output parameters have not yet been
> processed. Call getMoreResults()
> 
> I assume IBatis must be calling getMoreResults() when I use queryForObject
> because I use procs with output params there, too. Only
> queryForPaginatedList fails.
> 
> Any help with this issue would be much appreciated. Thanks!
> 
> Regards,
>        Eric
>