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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > Sent: Monday, December 20, 2004 4:27 PM > To: [EMAIL PROTECTED] > 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 <[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 > > > > > >
