JDBC - CURSORS and scrolling throught large tables

Igor/Nigel,

We have hit this large file paging issue a lot.
We have implemented a similar technique for subfiling/paging as what Nigel
discussed.

Nigel,  There maybe a few potential issues with the approach you describe if
I'm following you correctly.

1. I don't think you can necessarily use a key value and change the SQL
selection criteria.  This may lead to issues such as:
a. Loss of result set integrity.  If you change mykey > 31 then when they
hit previous you could possibly backup and go through more records than
intended.
b. You don't necessarily know the key field.  For example,  I would get all
customers with an balance due which would be a SQL such as:
i. Select * from qiws.qcustcdt where baldue > 0    ;  In this case, I have
no key nor any assumed ordering
c. The user may change the sort field thus changing the records you  need to
return

Instead of the key field, we remember the original SQL and the sequence of
the last record then rerun the SQL using cursor positioning to scroll to the
next record in the result set and begin reading from that spot.  All this
information can be encapsulated into a Java bean and sent to a JSP for
rendering.  There are some deep issues to be concerned with such as cursor
sensitivity, connection pooling, retaining the resultset,
caching/performance, but the basic design is solid and we have used it in
hundreds of web applications.

Here is an example that shows the technique:
http://www.planetjavainc.com/wow63x/runApp?id=369&_pj_lib=wowsamp60

The design approach we use is shown here:  (navigate to the
"Subfiling/paging Problem" section:

http://www.planetjavainc.com/pres/AdvancedJavaDesignPatternsInWeb_files/fram
e.htm

HTH


Thanks,  Paul

Business: 760-432-0600
PlanetJ - Makers of WOW  (AKA... WebSphere on Steroids)
You Need Web?  You Need WOW!  www.planetjavainc.com
WOW!!!!



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 4/27/2005