[
http://issues.apache.org/jira/browse/IBATIS-109?page=comments#action_63394 ]
Patrick Idem commented on IBATIS-109:
-------------------------------------
You are right that I meant to use nextPage() instead of next().
With DB2 drivers the problem is consistent and easily reproducible.
Just to give you a better idea of how I am using the PaginatedList, here is
what I am doing:
When the user does a search I create a PaginatedList for the search results. I
put the PaginatedList into the session so that when the user chooses "Next
Page" or "Previous Page" I call the appropriate functions on the same paginated
list by looking up the paginated list in the session.
I have set up logging so that I see when iBATIS goes to the database and the
results that are returned. I clearly see that when the user is on the next to
the last page that iBATIS has already retrieved all of the results for the
ResultSet. I notice now that ResultSets are not reused, but when stepping
through the iBATIS source code I do see that iBATIS is going to the database
even though it has already retrieved all of the results. In the example above
where there are 11 records total and a page size of 10 is used, I see that when
going to the second page it skips over the 11 records and then calls rs.next().
But since it has already skipped over all 11 results rs.next() throws a "Result
set closed" exception.
I believe there are two solutions to this problem:
1) While skipping results and rs.next() returns false you should return rather
than break since you know that all results have been skipped and there are no
records to handle.
2) Have the paginated list recognize that there are no more pages and not go to
the database when nextPage() has been called and all of the results have been
retrieved.
The second solution is better since it avoids an unnecassary query to the
database.
Hope that helps. I still believe this is a bug that is masked by the fact that
most database drivers simply have ResultSet.next() return false even though the
end of the ResultSet has already been reached as opposed to the DB2 drivers
throwing an Exception.
Hope that helps.
Here is the relevant stack trace for the error:
com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
at com.ibm.db2.jcc.a.cj.bl(cj.java:3347)
at com.ibm.db2.jcc.a.cj.c(cj.java:292)
at com.ibm.db2.jcc.a.cj.next(cj.java:279)
at
org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:196)
at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.ibatis.common.jdbc.logging.ResultSetLogProxy.invoke(ResultSetLogProxy.java:47)
at $Proxy5.next(Unknown Source)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:353)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:179)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:201)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:169)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:119)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:109)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:81)
at
com.ibatis.sqlmap.engine.mapping.statement.PaginatedDataList.getList(PaginatedDataList.java:138)
at
com.ibatis.sqlmap.engine.mapping.statement.PaginatedDataList.pageForward(PaginatedDataList.java:53)
at
com.ibatis.sqlmap.engine.mapping.statement.PaginatedDataList.nextPage(PaginatedDataList.java:145)
at
com.fcb.inv.actions.SoftwareAction.nextSoftwarePage(SoftwareAction.java:78)
> PaginatedList bug
> -----------------
>
> Key: IBATIS-109
> URL: http://issues.apache.org/jira/browse/IBATIS-109
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.8, 2.0.9, 2.0.9b, 2.1.0, 2.1.1, 2.1.2
> Environment: Any Java environment
> Reporter: Patrick Idem
> Priority: Critical
>
> Using DB2 database drivers cases an exception to be thrown when calling
> paginatedList.next() and the current page is the page before the last page.
> From what I can determine the PaginatedList class retrieves future records in
> order to determine if there are any more pages. An Exception will be caused
> in the following situation:
> A database table has 11 records.
> First issue a command to get a paginated list of all the records with a page
> size of 10:
> PaginatedList list = queryForPaginatedList("getAllRecords", null, 10)
> Since the paginated list reads ahead it will retrieve all 11 records and
> reach the end of the ResultSet.
> Now if we want to view the second page (which only consists of 1 record) we
> would then do the following:
> list.next()
> Now, calling list.next() should not have to go to the database since we had
> already reached the end of the ResultSet but it does! This is where the
> problem happens. DB2 drivers automatically close the ResultSet when you read
> in the last row so the ResultSet has been closed.
> I am suspecting that this problem is only serious when using DB2 drivers but
> it is still a problem. The paginated list object should be smart enough to
> know that there are no more results and not try to hit the database that last
> time.
> The actually DB2 exception that is thrown is the following:
> com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
