ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: queryForPaginatedList, stored procedures and output parameter s
Date Tue, 21 Dec 2004 00:27:14 GMT
All right, how about we clear this up.

>>1.) iBATIS ignores Sun's recommendation for compatibility 
>> grab output parameters before processing result sets. The response from MS

THIS IS ABSOLUTELY UNTRUE.  iBATIS does exactly what you ask of it, no
more no less.  It does in fact grab the output parameters AFTER it
processes results (see General Statement lines 167 - 173).   The only
exception is if you request more rows than you plan on processing.  In
which case, OF COURSE we're not going to iterate over it, as it would
be a huge performance impact if we did.  To deal with this, iBATIS
supports standard scrollable result sets.  If your driver doesn't
support it, there's NOTHING we can do about it.  iBATIS requires JDBC
2.0 compliance.  We're not about to fix every JDBC driver problem out
there.

> 2.) Either jTDS or iBATIS or both have trouble creating scrollable result
> sets when I use queryForList, and the forward only cursor iBATIS uses

iBATIS doesn't "create" scrollable result sets, it can only request
them of the driver.  If the driver doesn't implement them, then we
can't do anything about that.

Sorry we can't be of more help to you.

Clinton


On Mon, 20 Dec 2004 14:32:58 -0800, Eric Williams <ericw@sandiego.com> wrote:
> That's not the design approach we wanted to use, but I guess we have no
> choice when you consider the following factors:
> 
> 1.) iBATIS ignores Sun's recommendation for compatibility when it tries to
> grab output parameters before processing result sets. The response from MS
> SQL Server, for example, packages output parameters *after* result sets and
> row counts, so you can't grab an output param when you're sitting on a
> result set. I believe the logic behind queryForList could be modified,
> though it seems queryForPaginatedList could not.
> 
> 2.) Either jTDS or iBATIS or both have trouble creating scrollable result
> sets when I use queryForList, and the forward only cursor iBATIS uses
> requires manually moving over each skipped record. With the amount of data
> we'll be handling, this is a show-stopper.
> 
> Thanks to everyone who offered suggestions on this problem. I appreciate it.
> 
> Regards,
>     Eric
> 
> -----Original Message-----
> From: Larry Meadors [mailto:Larry.Meadors@plumcreek.com]
> Sent: Sunday, December 19, 2004 6:30 AM
> To: ibatis-user-java@incubator.apache.org; ericw@sandiego.com
> Subject: RE: queryForPaginatedList, stored procedures and output
> parameter s
> 
> Maybe I am being naive here.
> 
> If you are using a stored procedure already...why not make it return
> just the data you want, one page at a time?
> 
> Every sql-based language i have used allows this, and it would be
> faster, lighter-weight, clearer, and just plain easier for everyone.
> 
> Larry
> 
> >>> Eric Williams <ericw@sandiego.com> 12/17/04 3:20 PM >>>
> queryForList has the same problem unless I pull the whole list at once,
> which I can't (huge result sets). But it seems to me that the behavior
> of
> queryForList could be modified to call getMoreResults, since the result
> set
> is no longer needed once you get your last row.
> 
> Even so, it looks like the result set that is created when using
> queryForList is not forward only, so in SqlExecutor.handleResults it
> can't
> use rs.absolute(skipResults) to jump ahead, and has to go one-by-one
> over
> the rows I want to skip (which could be a very large number of rows).
> I'm
> not sure why the result set is not forward only, or what I can do to
> ensure
> that it is. But again, if iBATIS won't go to the end of the result set
> or
> call getMoreResults before trying to get output parameters, queryForList
> is
> no better than queryForPaginatedList.
> 
> Is there anything else I can do? I really like iBATIS and want to move
> away
> from our home-grown JDBC code.
> 
> Regards,
>         Eric
> 
> -----Original Message-----
> From: Brandon Goodin [mailto:brandon.goodin@gmail.com]
> Sent: Friday, December 17, 2004 12:10 PM
> To: Eric Williams
> Cc: ibatis-user-java@incubator.apache.org
> Subject: Re: queryForPaginatedList, stored procedures and output
> parameter s
> 
> 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 <ericw@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
> >
>

Mime
View raw message