ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: queryForPaginatedList, stored procedures and output parameter s
Date Mon, 20 Dec 2004 23:08:33 GMT
Clinton and Larry

1) Isn't it possible we could make this a setting?
fetchOutputAfterResults="true"?

Eric

2) I'm not sure what you are expecting here. How would you code this
with straight JDBC? Are you saying that you want an implementation of
Forward-Only QueryForList that uses absolute to jump to a particular
result record instead of next?

Brandon


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