jdbc cursor positioning - Mailing list pgsql-jdbc

From G.Nagarajan
Subject jdbc cursor positioning
Date
Msg-id NFBBIOPECKPCJJHHBOGJOELJDIAA.gnagarajan@dkf.de
Whole thread Raw
Responses Re: jdbc cursor positioning
List pgsql-jdbc
hi,
I am trying to implement "page wise" selection of rows
from a table. The table contains around 10000 rows and
I will be displaying only 10 rows at a time. At any time
I might have to display say the 5th page or 45th page etc.
After searching in the archives, I came across this solution:

<sample>
c.setAutoCommit (false);
Statement stmt = c.createStatement ();
stmt.execute ("declare " + CURSOR_NAME + " cursor for select * from
huge_table");
stmt.close ();
PreparedStatement fetch = c.prepareStatement ("fetch ? from " +
CURSOR_NAME);
PreparedStatement move  = c.prepareStatement ("move ? in " + CURSOR_NAME);

fetch.setInt (1, 100);
ResultSet rs = fetch.executeQuery ();
...
fetch.setInt (1, -100);
rs = fetch.executeQuery ();

move.setInt (200);
int actuallyMoved = move.executeUpdate ();

if (actuallyMoved < 200)
    System.out.println ("Less then 200 rows in cursor");
...
c.commit (); //Will also close the cursor(s)...
</sample>

The statement "move? ..." gives an error if executed. This command gives not
results and therefore I cannot use a executeUpdate() with it. It tells that
the update  count could not be determined. So my questions are

 1-> has the cursor been actually moved irrespective of the error?
 2-> are there any other ways of positioning the cursor.
 3-> is there any way by which i can know what is the current position?

Obviously i can give fetch() n times, but if the user selects the 400th
page then the driver will retrieve 400*10 records!

any suggestions are most welcome,

Regards,
Nagarajan.


pgsql-jdbc by date:

Previous
From: Chris Thompson
Date:
Subject: Re: jdbc problem with multiple connections
Next
From: Toby
Date:
Subject: Re: jdbc cursor positioning