ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Williams <er...@sandiego.com>
Subject RE: queryForPaginatedList, stored procedures and output parameter s
Date Tue, 21 Dec 2004 16:43:44 GMT
Sounds good. Thanks Clinton!

Regards,
	Eric

-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Monday, December 20, 2004 8:59 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: queryForPaginatedList, stored procedures and output
parameter s


Well, I can't see any reason why we can't call getMoreResults() until
it returns false (as the JDBC API docs describe).  We'll try that in
2.0.9 and if it causes a performance problem for anyone, we'll make it
an option in the bug fix release after that.

Sound good?

Clinton


On Mon, 20 Dec 2004 17:34:10 -0800, Eric Williams <ericw@sandiego.com>
wrote:
> I'm debugging right now. I'm in
> com.ibatis.sqlmap.engine.execution.SQLExecutor, in executeQueryProcedure,
> line 282. iBATIS is about to retrieve output parameters with
> retrieveOutputParameters(cs, mappings, parameters);
> 
> Some variables:
> 
>         skipResults=10
>         maxResults=10
> 
> Here's what the resultset (your variable "rs") looks like:
> 
> rs= JtdsResultSet  (id=67)
>         cancelled= false
>         closed= false
>         columnCount= 18
>         columnMap= HashMap<K,V>  (id=70)
>         columns= ColInfo[18]  (id=73)
>         concurrency= 1007
>         currentRow= ColData[18]  (id=75)
>         direction= 1000
>         fetchDirection= 1000
>         fetchSize= 1
>         pos= 20
>         readAhead= true
>         resultSetType= 1003
>         rowsInResult= 20
>         statement= JtdsCallableStatement  (id=55)
>         wasNull= false
> 
> Now I'm about to get params with retrieveOutputParameters(cs, mappings,
> parameters);
> 
> And I get an error: java.sql.SQLException: Output parameters have not yet
> been processed. Call getMoreResults().
> 
> My belief is that jTDS can't get the output params here, because the
result
> set is open, sitting on a row. The output parameters come next, after the
> update counts. Yes, this is a driver/database issue, but that's why the
> compatibility recommendation exists. And surely I'm not the only one who
> uses jTDS/MS SQL Server.
> 
> I'm not trying to pick on your code here, or stir up a hornet's nest. And
> I'm surely no JDBC expert. I'm just trying to understand iBATIS because I
> want to use it. I *do* want to tell it what to do. But what do I need to
> tell it so that it doesn't fail here?
> 
> Regards,
>         Eric
> 
> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com]
> Sent: Monday, December 20, 2004 4:27 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: Re: queryForPaginatedList, stored procedures and output
> parameter s
> 
> 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