Re: error when using move, any suggestions? - Mailing list pgsql-jdbc

Noel,

Ok, I have some test code that works

  public CursorTest()
  {
    Connection con = null;
    ResultSet rs;
    try{
      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 5 in test");
      System.out.println( "Moved " + result );
      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();
    }catch (Exception ex){
      ex.printStackTrace();
    }finally{
      try{
        if (null != con) con.close();
      }catch(Exception ex){}
    }
  }

note the fetch is actually going to return the result set, so you have
to do rs=executeQuery( "fetch ..." )

Dave
On Thu, 2002-12-19 at 19:48, Noel wrote:
> Dave,
> Thanks for taking the time to look at the problem. I tried the
> stmt.executeUpdate. It also gave the same error.
>
> Once again, thanks
> Noel
>
> >Noel,
> >
> >Ok, sorry, jdbc is getting in the way here. I haven't got time right
> >now, so if you can't wait try changing the stmt.execute to
> >stmt.executeUpdate, and see what happens. I will try to figure it out
> >later though.
> >
> >Dave
> >On Wed, 2002-12-18 at 23:51, Noel wrote:
> >
> >
> >>Hi Dave,
> >>
> >>I modified the small program to reflect the statement I performed in psql:
> >>
> >>import blastDb.*;
> >>import java.sql.*;
> >>import java.io.*;
> >>import java.util.*;
> >>import java.lang.*;
> >>
> >>public class TestFetchSize
> >>{
> >>    public static void main(String[] args)
> >>    {
> >>        try
> >>        {
> >>            BlastDB db = new BlastDB();
> >>
> >>            Statement stmt = db.connection.createStatement();
> >>            int counte = 0;
> >>            stmt.execute("begin work");
> >>            stmt.execute("declare test cursor for select id from region
> >>where protein_database = 1");
> >>            ResultSet rs;
> >>            System.out.println("here1");
> >>            stmt.execute("move forward 99 in test");
> >>            System.out.println("here2");
> >>            while(stmt.execute("fetch forward 2 in test"))
> >>            {
> >>                rs = stmt.getResultSet();
> >>                if (rs.next())
> >>                {
> >>                    ++counte;
> >>                    while (rs.next())
> >>                    {
> >>                        ++counte;
> >>                    }
> >>                }
> >>                else
> >>                    break;
> >>
> >>            }
> >>
> >>            rs = stmt.getResultSet();
> >>            if (rs.next())
> >>                System.out.println("ok");
> >>            else
> >>                System.out.println("NO");
> >>            System.out.println(counte);
> >>            stmt.execute("end work");
> >>        }
> >>        catch (Exception e)
> >>        {
> >>            e.printStackTrace();
> >>            System.out.println(e.getMessage());
> >>        }
> >>    }
> >>}
> >>
> >>This is the out put, the same error as before :(
> >>here1
> >>Unable to fathom update count MOVE
> >>        at
> >>org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecutor.java:182)
> >>        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 TestFetchSize.main(TestFetchSize.java:21)
> >>Unable to fathom update count MOVE
> >>
> >>So the programe is not moving beyound the move statement.
> >>
> >>Many thanks
> >>Noel
> >>
> >>P.S. Is this sim to the INSERT problem with the sim error message?
> >>
> >>
> >>Dave Cramer wrote:
> >>
> >>
> >>
> >>>Noel,
> >>>
> >>>Well, your sql in the driver was fetch forward 2000 in test, does that
> >>>work?
> >>>
> >>>It shouldn't be a driver error, the driver just passes sql through.
> >>>
> >>>Dave
> >>>On Wed, 2002-12-18 at 20:51, Noel Faux wrote:
> >>>
> >>>
> >>>
> >>>
> >>>>Hi Dave,
> >>>>
> >>>>It works fine in psql:
> >>>>blast=# begin;
> >>>>BEGIN
> >>>>blast=# declare test cursor for select id from region where
> >>>>protein_database = 1;
> >>>>SELECT
> >>>>blast=# move forward 99 in test;
> >>>>MOVE
> >>>>blast=# fetch forward 2 in test;
> >>>>  id
> >>>>--------
> >>>>486175
> >>>>481881
> >>>>(2 rows)
> >>>>
> >>>>Is this a driver error?
> >>>>Thanks for your reply
> >>>>Cheers
> >>>>Noel
> >>>>
> >>>>Noel Faux
> >>>>Department of Biochemistry and Molecluar Biology
> >>>>Monash University
> >>>>Clayton 3168
> >>>>Victoria
> >>>>Australia
> >>>>
> >>>>----- Original Message -----
> >>>>From: Dave Cramer <Dave@micro-automation.net>
> >>>>Date: Wednesday, December 18, 2002 9:43 pm
> >>>>Subject: Re: [JDBC] error when using move, any suggestions?
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>Noel,
> >>>>>
> >>>>>What do you get from the server when you try the same commands in
> >>>>>psql?
> >>>>>I just tried it and it works fine?
> >>>>>
> >>>>>test=# begin;
> >>>>>BEGIN
> >>>>>test=# declare test cursor for select id from address ;
> >>>>>DECLARE CURSOR
> >>>>>test=# move forward 99 in test;
> >>>>>MOVE 1
> >>>>>test=# fetch forward 2000 in test;
> >>>>>id
> >>>>>----
> >>>>>(0 rows)
> >>>>>
> >>>>>Dave
> >>>>>
> >>>>>On Wed, 2002-12-18 at 00:30, Noel wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hi all,
> >>>>>>I'm new to postgreSQL.
> >>>>>>I wrote a little test program to learn how to use fetch and move
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>in
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>postgreSQL.
> >>>>>>
> >>>>>>import blastDb.*;
> >>>>>>import java.sql.*;
> >>>>>>import java.io.*;
> >>>>>>import java.util.*;
> >>>>>>import java.lang.*;
> >>>>>>
> >>>>>>public class TestFetchSize
> >>>>>>{
> >>>>>>    public static void main(String[] args)
> >>>>>>    {
> >>>>>>            try
> >>>>>>            {
> >>>>>>                    BlastDB db = new BlastDB();
> >>>>>>
> >>>>>>                    Statement stmt =db.connection.createStatement();
> >>>>>>                    int counte = 0;
> >>>>>>                    stmt.execute("begin work");
> >>>>>>                    stmt.execute("declare test cursor for select id
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>from region where
> >>>>
> >>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>protein_database = 1");
> >>>>>>                    ResultSet rs;
> >>>>>>                    stmt.execute("move forward 99 in test");
> >>>>>>                    while(stmt.execute("fetch forward 2000 in test"))
> >>>>>>                    {
> >>>>>>                            rs = stmt.getResultSet();
> >>>>>>                            if (rs.next())
> >>>>>>                            {
> >>>>>>                                    ++counte;
> >>>>>>                                    while (rs.next())
> >>>>>>                                    {
> >>>>>>                                            ++counte;
> >>>>>>                                    }
> >>>>>>                            }
> >>>>>>                            else
> >>>>>>                                    break;
> >>>>>>
> >>>>>>                    }
> >>>>>>                    System.out.println(counte);
> >>>>>>                    stmt.execute("end work");
> >>>>>>            }
> >>>>>>            catch (Exception e)
> >>>>>>            {
> >>>>>>                    e.printStackTrace();
> >>>>>>                    System.out.println(e.getMessage());
> >>>>>>            }
> >>>>>>    }
> >>>>>>}
> >>>>>>
> >>>>>>It compiles ok, however when run i get this message:
> >>>>>>Unable to fathom update count MOVE
> >>>>>>at
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>org.postgresql.core.QueryExecutor.receiveCommandStatus(QueryExecutor.java:182)>
> >>>>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 TestFetchSize.main(TestFetchSize.java:25)
> >>>>>>Unable to fathom update count MOVE
> >>>>>>
> >>>>>>I've search the web and the archives, and the only thing close
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>is the
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>thread about a sim problem with INSERT:
> >>>>>>"Basically the driver was storing the oid in an int.  The value
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>you have
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>is overflowing the value allowed for a signed int.
> >>>>>>
> >>>>>>thanks,
> >>>>>>--Barry "
> >>>>>>
> >>>>>>Is this the same problem ?
> >>>>>>Using  7.4 divers with JDBC3 support.
> >>>>>>
> >>>>>>Cheers
> >>>>>>Noel
> >>>>>>
> >>>>>>
> >>>>>>---------------------------(end of broadcast)--------------------
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>-------
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>TIP 4: Don't 'kill -9' the postmaster
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>--
> >>>>>Dave Cramer <Dave@micro-automation.net>
> >>>>>
> >>>>>
> >>>>>---------------------------(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 6: Have you searched our list archives?
> >>>>
> >>>>http://archives.postgresql.org
> >>>>
> >>>>
> >>>>
> >>>>
>
--
Dave Cramer <Dave@micro-automation.net>