Re: JDBC and processing large numbers of rows

Thanks Oliver and Sean. I was just trying to do it the hard way using plain
SQL. It's great that the ResultSet can handle this for me. I'll give that
a try.

David

----- Original Message -----
From: "Oliver Jowett" <oliver(at)opencloud(dot)com>
To: "David Wall" <d(dot)wall(at)computer(dot)org>
Cc: <pg(at)fastcrypt(dot)com>; <pgsql-jdbc(at)postgresql(dot)org>
Sent: Tuesday, May 11, 2004 6:07 PM
Subject: Re: [JDBC] JDBC and processing large numbers of rows

> David Wall wrote:
> > Thanks, Dave. Does anybody have any simple examples of the series of
JDBC
> > calls used to declare, open, fetch and close a cursor in PG? In Oracle?
I
> > know this is a PG list, so if no Oracle examples, can anybody at least
> > confirm that using cursors with Oracle and standard JDBC is possible?
> >
> > There's nothing like having to write custom code to implement what Java
> > purports to be write once, run anywhere! It seems that the JDBC spec
would
> > have to be severely lacking if you can't do something as simple (and
old) as
> > use cursors in a standard way.
>
> I don't know -- that seems more a criticism of SQL than of JDBC. I don't
> think DECLARE as a query statement exists in the SQL specs at all? The
> DECLARE docs say:
>
> > The SQL standard only makes provisions for cursors in embedded SQL. The
> > PostgreSQL server does not implement an OPEN statement for cursors; a
> > cursor is considered to be open when it is declared. However, ECPG, the
> > embedded SQL preprocessor for PostgreSQL, supports the standard SQL
> > cursor conventions, including those involving DECLARE and OPEN
> > statements.
>
> Anyway, back to your original question ..
>
> JDBC *does* provide a standard way of using cursors to page through
> result data. It's called ResultSet. Take a look at the ResultSet row
> movement primitives -- they look suspiciously like cursor movement
> primitives, don't they?
>
> I'd suggest using an appropriate resultset type (SCROLLABLE_* or
> FORWARD_ONLY, depending on your access patterns) and use setFetchSize()
> and setFetchDirection() to hint to the driver about what you're doing.
> Then just run your unmodified, cursor-less query. With a good driver
> implementation you should get paging of the resultset transparently.
> This is why all the different resultset types and fetch hints are there
> in the first place..
>
> The current postgresql driver will page results from the backend if you
> use FORWARD_ONLY, a non-zero fetchsize, and autocommit off. It isn't
> quite there yet for scrollable resultsets, but it's close (see the
> recent patches from Andy Zeneski). Note that using a scrollable
> resultset can be more expensive as the backend can only provide
> scrollable cursors for free in some cases -- in other cases, it has to
> materialize the whole resultset. This is a cost you'll end up paying
> regardless of whether you're using cursors via ResultSet, cursor
> manipulation directly, or LIMIT/OFFSET tricks (at least assuming you
> actually want to (eventually) process all the data from a query and not
> just a subset).
>
> Then we just need holdable resultset support (for the autocommit case --
> there's a parameter in JDBC3 for controlling this as holdable cursors
> aren't free, especially if you have very large result sets) and it'd be
> all transparent.
>
> I don't know how Oracle handles all this, but hiding the cursor work
> inside the ResultSet seems like the Right Way to do it.
>
> -O