aspose file tools*
The moose likes JDBC and the fly likes problems moving cursor in resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problems moving cursor in resultset" Watch "problems moving cursor in resultset" New topic
Author

problems moving cursor in resultset

Bob Rubin
Greenhorn

Joined: Mar 16, 2005
Posts: 21
first -- thanks for all previous help here is the new problem:

my resultset has 9 items yet after moving through the first 5 items (and updating each one in turn) i get an invalid cursor state error;

here is the code which is a method which open an SQL 8.0 server (2000) DB table retieves and updatable result set, writes a flat file reocrd and updates the corresponding database record


my system.out.println displays for cursor information are

the row is: 1total rows 9
the row is: 2total rows 9
the row is: 3total rows 9
the row is: 4total rows 9
the row is: 5total rows 9
the row is: 6total rows 9
[Microsoft] [ODBC Driver Manager] Invalid cursor state
[Microsoft] [ODBC Driver Manager] Invalid cursor state

Here is the interesting part -- when looking at the actual table i get the following results

1st physical row selected for resultset processed
2nd physical row selected for resultset skipped
3rd physical row selected for resultset processed
4th physical row selected for resultset skipped
5th physical row selected for resultset processed
6th physical row selected for resultset skipped
7th physical row selected for resultset processed
8th physical row selected for resultset skippped
9th physical row selected for resultset processed

which corresponds to the 5 display that don't have curosr state problems


If ia simply do the output file processing and eliminate THE 2 UPDATE Strings the the updateRow -- all 9 items process and 9 reocrds are written to my flat file in the sequence they appear in my original table

what is the problem with my cursor?
[ July 18, 2005: Message edited by: Bob Rubin ]
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
The cursor is being advanced twice: by the next() and isLast() methods. I think you only need to do this.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Bob Rubin
Greenhorn

Joined: Mar 16, 2005
Posts: 21
got rid of isLast logic and rewrote while(refunds.next()) {

no change in output -- still have skips problem
[ July 18, 2005: Message edited by: Bob Rubin ]
Bob Rubin
Greenhorn

Joined: Mar 16, 2005
Posts: 21
here is how i got it to work --= got rid of the refunds.next -- instead i'm testing while(refunds.isLast() == false)

i figured that the updateRow must be moving the cursor -- and sure enough it worked -- what happedn is after each update, row 1 becomes the next row so by doing next i was skipping every other row -- the displays always show current row of 1 because (i believe) the update row command deletes the updated row from the result set.

any comments ot this theory?
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
If this is happening, then it's surprising that the API documentation does not mention this. Or is it a "feature" of the driver you are using?

And I don't particularly like the idea of having to call isLast() in a loop because the API doc has this to say:
Note: Calling the method isLast may be expensive because the JDBC driver might need to fetch ahead one row in order to determine whether the current row is the last row in the result set.
Bob Rubin
Greenhorn

Joined: Mar 16, 2005
Posts: 21
api doc says nothing of the kind -- i'm simply observing how the JDBC odbc dirver for sql server 2000 appears to work - which i admit appears to be rather strange
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problems moving cursor in resultset