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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > Sent: Sunday, December 19, 2004 6:30 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > Sent: Friday, December 17, 2004 12:10 PM > To: Eric Williams > Cc: [EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] > > Sent: Wednesday, December 15, 2004 12:13 PM > > To: [EMAIL PROTECTED] > > 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 > > >
