Re: error when using move, any suggestions?
От | Noel |
---|---|
Тема | Re: error when using move, any suggestions? |
Дата | |
Msg-id | 3E029E01.3090400@med.monash.edu.au обсуждение исходный текст |
Ответ на | Re: error when using move, any suggestions? (Dave Cramer <Dave@micro-automation.net>) |
Ответы |
Problem with move not returning number of rows affected [was: error
when using move, any suggestions?]
(Barry Lind <blind@xythos.com>)
|
Список | pgsql-jdbc |
Dave, Many thanks for your time. Unfortunalty, I'm still getting the same error, even with the code you send, modified to use my database. import blastDb.*; import java.sql.*; import java.io.*; import java.util.*; import java.lang.*; public class TestFetchSize{ public static void main(String[] args){ Connection connection = null; ResultSet rs; try{ connection = new BlastDB().connection; connection.setAutoCommit(false); Statement stmt = connection.createStatement(); int counte = 0; int result = stmt.executeUpdate("declare test cursor for select id from region " + "where protein_database = 1"); System.out.println("Cursor created, result is: " + result); result = stmt.executeUpdate("move forward 99 in test"); System.out.println("Moved 99, result is: " + result); while(true){ rs = stmt.executeQuery("fetch forward 2 in test"); if (rs.next()){ ++counte; while (rs.next()){ ++counte; System.out.println("Id --> " + rs.getObject(1).toString()); } } else break; } System.out.println(counte); rs.close(); connection.commit(); connection.close(); } catch (Exception e) { e.printStackTrace(); System.out.println(e.getMessage()); } finally{ try{ if(null != connection) connection.close(); } catch(Exception e) {} } } } With the move statement commented out it works fine. The database is running on an IBM server with 4 Intel(R) Xeon(TM) CPU 1.40GHz. The programe is running on a 1.2Ghz Athon machine with debin linux and 1Gig Ram. I don't know if this helps at all. Is there anyother info which may help you?? Once again many thanks for your help. Noel >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 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>
В списке pgsql-jdbc по дате отправления: