Thread: Cursors from Java?

Cursors from Java?

From
"Daniel T. Staal"
Date:
Hi all.  :)

I've got a little problem that I'm thinking a cursor would help me with,
but I'm not sure, and I'm not sure a cursor is avalible.

First off: The situation.  I have a table of transactions and a web
interface to allow users to view their transactions, sorted by various
criteria.  This particular table is created to speed up the web access, so
it stores every transaction twice: once for the reciever and once for the
sender.  There is a field to tell who we are looking at here.  There is
also a count field, specifing *for each user* what number transaction this
is since some rollover date.

The web page display is paged using this count.  Any sorting is done
within one page's worth of the count only at the moment.  This allows us
to not pull the full transaction list for a particular user for every page
they display; only a certain range is pulled and sorted.

As a new feature, we want to put in the ablity to filter what transactions
they see, based on a couple user critera.  Doing this the current way,
with one three-position filter, means we need to create two more
(semi-redundant) tables.  (Putting in more filter points obviously
invloves an exponetial increase in counted tables.)

So, what I'm looking at here is the idea of setting up a cursor at the
SQL, and using it instead of the count.  (So I can tell the cursor to
fetch me rows 201-400 for instance.)

The web application is written in very basic Java, using JDBC.  Is this
possible?  Any pointers on how?  Note that every web page call is a
*seperate* transaction, even if the user just wants the next page.

One last thing: are there any portablity concerns?  We are currently
evaluating switching to PostgreSQL (and it is the database I'm testing
agaist), but our code is tested to support three databases.

Sorry for the dump, but I'm trying to be sure I'm describing any relevent
info.  If there is a better place to ask the question, just tell me.

Daniel T. Staal

------------------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you are
expressly allowed to retransmit, quote, or otherwise use the contents
for non-commercial purposes.  This copyright will expire 5 years after
the author's death, or in 30 years, whichever is longer, unless such a
period is in excess of local copyright law.
------------------------------------------------------------------------