Thread: jdbc cursor positioning
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.
as i understand it, postgres doesn't yet support cursors. however, with the LIMIT and OFFSET modifiers in a SELECt you can get the same behaviour.
SELECT <fields> FROM <table> ORDER BY <field> LIMIT <recsPerPage> OFFSET <offset>
<fields> is the fields to select
<table> is the table you're selecting from
<recsPerPage> is an integer 1..n for the number of records per page.
<offset> is calculated as ((page - 1) * recsPerPage) where the page number is 1..n
make sure you order your select by something, else the records in a page may not quite make sense.
toby
SELECT <fields> FROM <table> ORDER BY <field> LIMIT <recsPerPage> OFFSET <offset>
<fields> is the fields to select
<table> is the table you're selecting from
<recsPerPage> is an integer 1..n for the number of records per page.
<offset> is calculated as ((page - 1) * recsPerPage) where the page number is 1..n
make sure you order your select by something, else the records in a page may not quite make sense.
toby
No, postgres fully supports cursors, the problem must be somewhere else. Dave On Sun, 2002-10-06 at 15:27, Toby wrote: > as i understand it, postgres doesn't yet support cursors. however, with the > LIMIT and OFFSET modifiers in a SELECt you can get the same behaviour. > > SELECT <fields> FROM <table> ORDER BY <field> LIMIT <recsPerPage> > OFFSET <offset> > > <fields> is the fields to select > <table> is the table you're selecting from > <recsPerPage> is an integer 1..n for the number of records per page. > <offset> is calculated as ((page - 1) * recsPerPage) where the page number > is 1..n > > make sure you order your select by something, else the records in a page > may not quite make sense. > > toby
> No, postgres fully supports cursors, the problem must be somewhere else. How do you make use of them from JDBC? David
This is a sample program which uses cursors
con = getConnection();
con.setAutoCommit( false );
Statement stmt = con.createStatement();
int result = stmt.executeUpdate( "declare test cursor for select *
from orders" );
System.out.println( "Created cursor, result is " + result );
result = stmt.executeUpdate( "move 0 in dave");
result = stmt.executeUpdate( "move -"+result+"in dave");
for(;;)
{
rs = stmt.executeQuery("fetch forward 5 in test");
if ( !rs.next() )
break;
}
while(rs.next()){
System.out.println("Id -->" + rs.getObject(1).toString());
}
rs.close();
con.commit();
con.close();
Dave
On Sun, 2002-10-06 at 22:57, David Wall wrote:
> > No, postgres fully supports cursors, the problem must be somewhere else.
>
> How do you make use of them from JDBC?
>
> David
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
hi Dave,
I get the following error when I execute the program
Caught Exception: Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
at
org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecutor.java:18
2)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:80)
at org.postgresql.Connection.ExecSQL(Connection.java:398)
at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
at fz.core.database.CursorTest.main(CursorTest.java:23)
It tells that it cannot determine the number of modified rows.
I am using Postgresql 7.1.3 and 7.2 jdbc driver. I will check with the beta
driver and see
if the error goes away.
Regards,
Nagarajan.
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> Sent: Monday, October 07, 2002 3:43 PM
> To: David Wall
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] jdbc cursor positioning
>
>
> This is a sample program which uses cursors
>
> con = getConnection();
> con.setAutoCommit( false );
> Statement stmt = con.createStatement();
>
> int result = stmt.executeUpdate( "declare test cursor for select *
> from orders" );
> System.out.println( "Created cursor, result is " + result );
> result = stmt.executeUpdate( "move 0 in dave");
> result = stmt.executeUpdate( "move -"+result+"in dave");
>
> for(;;)
> {
> rs = stmt.executeQuery("fetch forward 5 in test");
> if ( !rs.next() )
> break;
> }
>
> while(rs.next()){
> System.out.println("Id -->" + rs.getObject(1).toString());
> }
> rs.close();
> con.commit();
> con.close();
>
>
> Dave
> On Sun, 2002-10-06 at 22:57, David Wall wrote:
> > > No, postgres fully supports cursors, the problem must be
> somewhere else.
> >
> > How do you make use of them from JDBC?
> >
> > David
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
Sorry,
I should have taken the move lines out, there is no way to tell if they
succeed.
Dave
On Mon, 2002-10-07 at 12:51, G.Nagarajan wrote:
> hi Dave,
>
> I get the following error when I execute the program
>
> Caught Exception: Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
> Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
> at
> org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecutor.java:18
> 2)
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:80)
> at org.postgresql.Connection.ExecSQL(Connection.java:398)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
> at fz.core.database.CursorTest.main(CursorTest.java:23)
>
> It tells that it cannot determine the number of modified rows.
>
> I am using Postgresql 7.1.3 and 7.2 jdbc driver. I will check with the beta
> driver and see
> if the error goes away.
>
> Regards,
> Nagarajan.
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > Sent: Monday, October 07, 2002 3:43 PM
> > To: David Wall
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] jdbc cursor positioning
> >
> >
> > This is a sample program which uses cursors
> >
> > con = getConnection();
> > con.setAutoCommit( false );
> > Statement stmt = con.createStatement();
> >
> > int result = stmt.executeUpdate( "declare test cursor for select *
> > from orders" );
> > System.out.println( "Created cursor, result is " + result );
> > result = stmt.executeUpdate( "move 0 in dave");
> > result = stmt.executeUpdate( "move -"+result+"in dave");
> >
> > for(;;)
> > {
> > rs = stmt.executeQuery("fetch forward 5 in test");
> > if ( !rs.next() )
> > break;
> > }
> >
> > while(rs.next()){
> > System.out.println("Id -->" + rs.getObject(1).toString());
> > }
> > rs.close();
> > con.commit();
> > con.close();
> >
> >
> > Dave
> > On Sun, 2002-10-06 at 22:57, David Wall wrote:
> > > > No, postgres fully supports cursors, the problem must be
> > somewhere else.
> > >
> > > How do you make use of them from JDBC?
> > >
> > > David
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
I was trying to figure out a way to retrieve
a particular set of rows from the cursor. If I have
10,000 rows and need to take only the records between
5600 and 5700, "MOVE" would have been ideal. Using "FETCH"
would mean that the 5600 records have to be transferred
from the server. so I guess the only option left is to
use the offset and limit options in the query.
Thanks,
Nagarajan.
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> Sent: Monday, October 07, 2002 6:50 PM
> To: G.Nagarajan
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] jdbc cursor positioning
>
>
> Sorry,
>
> I should have taken the move lines out, there is no way to tell if they
> succeed.
>
> Dave
> On Mon, 2002-10-07 at 12:51, G.Nagarajan wrote:
> > hi Dave,
> >
> > I get the following error when I execute the program
> >
> > Caught Exception: Kann Anzahl der veränderten Zeilen nicht
> ermitteln: MOVE
> > Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
> > at
> >
> org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecut
> or.java:18
> > 2)
> > at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:80)
> > at org.postgresql.Connection.ExecSQL(Connection.java:398)
> > at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> > at
> org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
> > at fz.core.database.CursorTest.main(CursorTest.java:23)
> >
> > It tells that it cannot determine the number of modified rows.
> >
> > I am using Postgresql 7.1.3 and 7.2 jdbc driver. I will check
> with the beta
> > driver and see
> > if the error goes away.
> >
> > Regards,
> > Nagarajan.
> >
> > > -----Original Message-----
> > > From: pgsql-jdbc-owner@postgresql.org
> > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > > Sent: Monday, October 07, 2002 3:43 PM
> > > To: David Wall
> > > Cc: pgsql-jdbc@postgresql.org
> > > Subject: Re: [JDBC] jdbc cursor positioning
> > >
> > >
> > > This is a sample program which uses cursors
> > >
> > > con = getConnection();
> > > con.setAutoCommit( false );
> > > Statement stmt = con.createStatement();
> > >
> > > int result = stmt.executeUpdate( "declare test cursor
> for select *
> > > from orders" );
> > > System.out.println( "Created cursor, result is " + result );
> > > result = stmt.executeUpdate( "move 0 in dave");
> > > result = stmt.executeUpdate( "move -"+result+"in dave");
> > >
> > > for(;;)
> > > {
> > > rs = stmt.executeQuery("fetch forward 5 in test");
> > > if ( !rs.next() )
> > > break;
> > > }
> > >
> > > while(rs.next()){
> > > System.out.println("Id -->" + rs.getObject(1).toString());
> > > }
> > > rs.close();
> > > con.commit();
> > > con.close();
> > >
> > >
> > > Dave
> > > On Sun, 2002-10-06 at 22:57, David Wall wrote:
> > > > > No, postgres fully supports cursors, the problem must be
> > > somewhere else.
> > > >
> > > > How do you make use of them from JDBC?
> > > >
> > > > David
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Well, move works, it's just that it doesn't return anything usefull to
let you know where it moved to!
Dave
On Mon, 2002-10-07 at 13:15, G.Nagarajan wrote:
> I was trying to figure out a way to retrieve
> a particular set of rows from the cursor. If I have
> 10,000 rows and need to take only the records between
> 5600 and 5700, "MOVE" would have been ideal. Using "FETCH"
> would mean that the 5600 records have to be transferred
> from the server. so I guess the only option left is to
> use the offset and limit options in the query.
>
> Thanks,
> Nagarajan.
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > Sent: Monday, October 07, 2002 6:50 PM
> > To: G.Nagarajan
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] jdbc cursor positioning
> >
> >
> > Sorry,
> >
> > I should have taken the move lines out, there is no way to tell if they
> > succeed.
> >
> > Dave
> > On Mon, 2002-10-07 at 12:51, G.Nagarajan wrote:
> > > hi Dave,
> > >
> > > I get the following error when I execute the program
> > >
> > > Caught Exception: Kann Anzahl der veränderten Zeilen nicht
> > ermitteln: MOVE
> > > Kann Anzahl der veränderten Zeilen nicht ermitteln: MOVE
> > > at
> > >
> > org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecut
> > or.java:18
> > > 2)
> > > at
> > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:80)
> > > at org.postgresql.Connection.ExecSQL(Connection.java:398)
> > > at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> > > at
> > org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
> > > at fz.core.database.CursorTest.main(CursorTest.java:23)
> > >
> > > It tells that it cannot determine the number of modified rows.
> > >
> > > I am using Postgresql 7.1.3 and 7.2 jdbc driver. I will check
> > with the beta
> > > driver and see
> > > if the error goes away.
> > >
> > > Regards,
> > > Nagarajan.
> > >
> > > > -----Original Message-----
> > > > From: pgsql-jdbc-owner@postgresql.org
> > > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > > > Sent: Monday, October 07, 2002 3:43 PM
> > > > To: David Wall
> > > > Cc: pgsql-jdbc@postgresql.org
> > > > Subject: Re: [JDBC] jdbc cursor positioning
> > > >
> > > >
> > > > This is a sample program which uses cursors
> > > >
> > > > con = getConnection();
> > > > con.setAutoCommit( false );
> > > > Statement stmt = con.createStatement();
> > > >
> > > > int result = stmt.executeUpdate( "declare test cursor
> > for select *
> > > > from orders" );
> > > > System.out.println( "Created cursor, result is " + result );
> > > > result = stmt.executeUpdate( "move 0 in dave");
> > > > result = stmt.executeUpdate( "move -"+result+"in dave");
> > > >
> > > > for(;;)
> > > > {
> > > > rs = stmt.executeQuery("fetch forward 5 in test");
> > > > if ( !rs.next() )
> > > > break;
> > > > }
> > > >
> > > > while(rs.next()){
> > > > System.out.println("Id -->" + rs.getObject(1).toString());
> > > > }
> > > > rs.close();
> > > > con.commit();
> > > > con.close();
> > > >
> > > >
> > > > Dave
> > > > On Sun, 2002-10-06 at 22:57, David Wall wrote:
> > > > > > No, postgres fully supports cursors, the problem must be
> > > > somewhere else.
> > > > >
> > > > > How do you make use of them from JDBC?
> > > > >
> > > > > David
> > > > >
> > > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > > http://www.postgresql.org/users-lounge/docs/faq.html
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
>