jdbc cursor positioning

From: "G(dot)Nagarajan" <gnagarajan(at)dkf(dot)de>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: jdbc cursor positioning
Date: 2002-10-06 14:50:07
Message-ID: NFBBIOPECKPCJJHHBOGJOELJDIAA.gnagarajan@dkf.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Toby 2002-10-06 19:27:13 Re: jdbc cursor positioning
Previous Message Chris Thompson 2002-10-06 13:30:08 Re: jdbc problem with multiple connections