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.
