Hi All
I have been looking into the issue of EQSL paging no longer working with Oracle since upgrading Cocoon from 2.0.3 to 2.1.4 and although I can't see any direct reason in the code for this problem I have noticed that if I use jdbc as the limit clause instead of our default setting of auto the paging seems to work again and the <esql:more-results> is output.
Can anyone (hopefully Chris) expand on this issue and advise on any further suggestions on how to fix this as I would prefer not to have to visit all our XSP's and change the limit clause setting to be JDBC as we plan on supporting more than just Oracle sometime in the future.
I believe the problem is burried in the code that tries to determine the
number of rows in the result set. JDBC2 does not allow to ask the result
set the size. Most database management systems don't know the exact result set size before having delivered all rows. One way is to use a scrollable result set and try to position the cursor at a position that would require another result page ("more-results"). Another is to use another query, that just returns the number of resulting rows.
IMHO both solutions suck from a performance POV.
OTOH, paging through results makes most sense with ordered results, so the DBMS would need to have retrieved all rows anyway, so additional costs could be transfer over the network if the select is not limited or locks preventing concurrent queries / additional overhead to maintain the scrollable resultset.
Retrieving just one row too many is fine but you'll know only at the end
that there's still more data to be displayed -- thus you couldn't put the "more" button at the top of the page unless you're using eg XSLT
to move the button up. You probably wouldn't want this for large results :-)
Back to your question: You'll need to look at AbstractEsqlQuery, JdbcEsqlQuery, and OracleEsqlQuery at the getQueryString() method.
Pick the one model that suits you best and works with your Oracle. We'll happily incorporate a patch that allows Oracle to use the "more-results" feature again :-))
Good luck.
Chris.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
