Re: JDBC and processing large numbers of rows

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.

From what little I can gather, it seems that in PG, I'd do something like:

ps = connection.prepareStatement("DECLARE mycursor CURSOR FOR SELECT a,b
FROM mytable;");
ps.execute();
ps = connection.prepareStatement("FETCH 100 FROM mycursor;");
ResultSet rs = ps.executeQuery();
...process the resultset....possibly doing more FETCHes and getting more
resultsets...
ps = connect.prepareStatement("CLOSE mycursor;"); // is that needed, or
will it close on commit?
connection.commit();

Is that even close?

In Oracle, this seems even more questionable because the FETCH semantics
appear to want to use host variables, so I'm not even sure what the FETCH
statement would look like to get the data back in a ResultSet.

ps = connection.prepareStatement("DECLARE CURSOR mycursor FOR SELECT a,b
FROM mytable; END;");
ps.execute();
ps = connection.prepareStatement("FOR 100 FETCH mycursor [INTO????];");
ResultSet rs = ps.executeQuery();
...process the resultset....possibly doing more FETCHes and getting more
resultsets...
ps = connect.prepareStatement("CLOSE mycursor;"); // is that needed, or
will it close on commit?
connection.commit();

Does anybody out there have real experience doing any of this?

Thanks,
David

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

> Well, if all else fails you may have to write a wrapper around them to
> deal with the discrepancies between oracle and postgres.
>
> One thing though, be warned holdable cursors in postgres have to be
> materialized, so you may end up running out of server memory. This means
> that you need to be inside a transaction to get a non-holdable cursor.
>
> --dc--
>
> On Tue, 2004-05-11 at 16:32, David Wall wrote:
> > > Use cursors to page through really large result sets
> >
> > Well, I've thought about that, but that just led me to my 3rd question
in my
> > previous inquiry:
> >
> > > > 3) How do people use cursors in JDBC? Being able to FETCH seems
like a
> > nice
> > > > way to handle question #2 above in a batch program, since only a
subset
> > of
> > > > rows needs to be retrieved from the db at a time. Cursors probably
> > don't
> > > > work for question #1 above since keeping a transaction alive across
page
> > > > views is generally frowned upon and even hard to accomplish since it
> > means
> > > > locking up a connection to the db for each paging user.
> >
> > The question for me is how do you portably use cursors so that you can
work
> > with Oracle and PG seamlessly? I presume there might be some
(hopefully)
> > slight variations, like there are with BLOBs, but it would be nice if
using
> > cursors was standardized enough to make it using standard JDBC.
> >
> > It seems that the issues are with defining a cursor, executing it,
fetching
> > against it, then release it when done. Is there a standard way to do
this?
> > Any examples?
> >
> > Thanks,
> > David
> >
> >
> >
> > !DSPAM:40a138a962802251020430!
> >
> >
> --
> Dave Cramer
> 519 939 0336
> ICQ # 14675561
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings