problems moving cursor in resultset

[Bob Rubin]

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

 
 public static void getApprovals() throws ClassNotFoundException, SQLException, IOException {
File data = new File("approved.txt");
PrintWriter out = new PrintWriter(
      new BufferedWriter(
  new FileWriter(data)));
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc  :-o	  dbc:nypb";
        String user = "nypb";
        String password = "nypb";
        System.out.println("now connect");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("just connected");
Calendar now = Calendar.getInstance();
String nowString = (String) now.getTime().toString();
System.out.println("in getApprovals");
String preparedGetApprovalsSQL = "SELECT * FROM refund_request2 " +
                                 "WHERE auth = 'Y' AND transStatus = 'A'";
PreparedStatement ga = conn.prepareStatement(preparedGetApprovalsSQL,
 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet refunds= ga.executeQuery();
boolean result = refunds.next();
refunds.last();
int numrows = refunds.getRow();
refunds.beforeFirst();
while (true) {
refunds.next();
int gr = refunds.getRow();
System.out.println("the row is: " + gr + "total rows: " + numrows);
Refund curRefund = getFields(refunds);
String refamt = String.format("%11.2f", curRefund.getAmount());
String field1 = curRefund.getPatnum();
System.out.println(field1);
String line_buffer = curRefund.getSite() + curRefund.getRefnum() +
     curRefund.getPatnum() +
     padBlanks(curRefund.getStaynum(), 6) +
     padBlanks(curRefund.getIO(), 1) +
     refamt +
     padBlanks(curRefund.getAddr(), 30) +
     padBlanks(curRefund.getPayto(), 30) +
     padBlanks(curRefund.getCity(), 20) +
     curRefund.getState() +
     curRefund.getZip1() +
     padBlanks(curRefund.getZip2(), 4) +
     curRefund.getBacktoPFS() +
     curRefund.getFinclass() +
     padBlanks(curRefund.getRepname(), 30) +
     padBlanks(curRefund.getRepext(), 6) +
     padBlanks(curRefund.getComments(), 40) +
     curRefund.getAuth() +
     curRefund.getAuthID() +
     padBlanks(curRefund.getTelno(), 12);
System.out.println(line_buffer);
out.println(line_buffer);
refunds.updateString("transStatus", "S");
refunds.updateString("sentDate", nowString);
refunds.updateRow();
if (refunds.isLast()) {
break;
}
}
out.close();
refunds.close();
ga.close();
conn.close();
}

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]

The cursor is being advanced twice: by the next() and isLast() methods. I think you only need to do this.

while (refunds.next()) {
   ...
}

[Bob Rubin]

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]

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]

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]

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