Re: [S] Re: Problems with non scrollable cursors

On Mon, 24 Jan 2005, Daniele Bufarini wrote:

> Actually using the cursor multiple time is what I was thinking...
> In fact, I can keep an open database connection only for large result
> set and use a one time connection for all the others.
> My problem is that I cannot scroll back using a cursor... I'm wondering
> If I have to modify the jdbc driver or the back end or if there are
> other solutions, less time consuming...

The easiest thing to do is to forgo parts of the JDBC interface and do it
manually.

Statement.execute("DECLARE mycursor SCROLL CURSOR FOR ...");

Then:

Statement.execute("MOVE ABSOLUTE 2000 IN mycursor");
ResultSet = Statement.executeQuery("FETCH FORWARD 10 FROM mycursor");

To fix this in the driver there are two avenues of attack here. The 7.4
driver used explicit DECLARE and FETCH calls for cursors. The driver
would need to be modified to add a SCROLL keyword and then handle moving
back and forth. A patch was produced to do this, which I had some
concerns about, that the poster never responded to:

http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00164.php

The 8.0 driver has been changed to use protocol level portals which are
pretty much the same as cursors. The problem is that protocol only
has support for forward cursors in both declaration and fetching. To fix
things along these lines would require pretty much all of the work in the
driver as the 7.4 path, but would also require some backend work and more
importantly, convincing people to change the frontend/backend protocol.

Kris Jurka