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

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?

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 ]

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 ]

thank you -- i've fixed other code in this program since your reply -- (closing both the statement as well as the resultset) seems to have solved most of my problems --- i've learned never to use ACCESS for devloping code because it lacks the locking featrues that SQLServer has thus code developedis buggy.

here is the story: -- you-re hint gave me the direction -- when i open my client app a combo box gets loaded based on reading a table using my server. if i later try to write a record the logic which re-accesses this table gets stuck (locked out) however if terminate my server and restart it with my client open -- no problem -- obviously my intiial access of the table is 'locking it' here is the offending code and my successful solution.

public static String [] loadFinComboBox() throws SQLException {
System.out.println("in load Fin Combo Box code");

String preparedFinClassSQL = "SELECT fincls " +
"FROM Nyhfine " +
"WHERE sumcls <> 'XXX'";

PreparedStatement fc = connection.prepareStatement(preparedFinClassSQL,
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet fcFinClasses = fc.executeQuery();
boolean resultFc = fcFinClasses.next();

int totRows = 0;

if (resultFc == true) {
totRows = getTotRows(fcFinClasses);
} else {
totRows = 0;
}

String [] finclasses = new String[totRows];
String auth = " ";
System.out.println("b4 fin load loop " + totRows);
for (int i = 0; i < totRows; i++) {
fcFinClasses.next();
finclasses[i] = fcFinClasses.getString(1);
}
System.out.println("af fin load loop " + totRows);
System.out.println(totRows);
System.out.println(finclasses);
fcFinClasses.close();
fc.close();
return finclasses;
}


the first close of fcFinClasses didn't do the trick until i added fc.close();

why is this necessary and why does it work
[ July 13, 2005: Message edited by: Bob Rubin ]

Jeanne i think you are on to the problem -- the program appears to be waiting -- i will get out DB guru to monitor the DB for access when i re-create the problem -- i will also post a final response --

what happens is it s NOT abending - its 'stopping' i get the aforemenioned displays ending with the 'stuck 3' then ---- nothing

problem is that no exception is generated -- the program seems somehow to have stopped executing as if its waiting for somehting -- no excpetion is generated -- i have never seen anything like this -- (exceptions i can handle)

i developed an app using MSAccess (for proof of concept only) works beautifully but i need to port to SQL Server 2000 (version 8.0 enterprise edition)

i'm having some problems -- sometimes things work other times not for example: the following code gets "STuck"

System.out.println("site and amt " + siteauth + amtauth); String preparedFinSQL = "SELECT finauth FROM Nyhfine WHERE fincls = ?"; System.out.println("stuck 1"); PreparedStatement fc = connection.prepareStatement(preparedFinSQL, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); System.out.println("stuck 2"); fc.setString(1, fincl); [B] System.out.println("stuck 3"); [/B] ResultSet fcRefund = fc.executeQuery(); System.out.println("stuck 4"); boolean resultfc = fcRefund.next(); System.out.println("stuck 5"); if (resultfc == true) { finauth = fcRefund.getString(1); } System.out.println("stuck 6"); String authkey = siteauth + amtauth + finauth; System.out.println("stuck 7"); System.out.println("this is the authkey " + authkey); if (onacct == true) { String pnum = (String) obj1.getPatnum(); String site = (String) obj1.getSite(); String regi = (String) obj1.getRegid(); double refa = (double) obj1.getRefundAmt(); String preparedSQL = "SELECT patnum, staynum, refnum, amount " + "FROM refund_request2 WHERE patnum = ? "; PreparedStatement ps = connection.prepareStatement(preparedSQL, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ps.setString(1, pnum); System.out.println("get ready for frist refund DB read"); ResultSet psRefund = ps.executeQuery(); System.out.println("after query"); boolean result = psRefund.next(); System.out.println("boolean " + result); int seq = 1; int curSeq = 0; int hiSeq = 0; if (result == false) { findRefund = new Refund("", "", "", "", "", 0, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", " ", " ", " ", " ", " ", " ", " "); System.out.println("bad read" + psRefund.getRow()); preparedSQL = "INSERT INTO refund_request2 " + "(site, refnum, patnum, staynum, io, amount, address, payto, city, state, " + "zip1, zip2, backtopfs, finclass, repname, repext, comment, auth, authid, " + "tele, authkey, transStatus, createDate, authorizeDate, sentDate, backDate, " + "operatorID, authorizerID) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement is = connection.prepareStatement(preparedSQL); is.setString(1, site); is.setString(2, "ON" + (String) obj1.getPatnum() + Integer.toString(seq)); is.setString(3, (String) obj1.getPatnum()); is.setString(4, (String) obj1.getRegid()); is.setString(5, (String) obj1.getIO()); is.setDouble(6, (double) obj1.getRefundAmt()); is.setString(7, (String) obj1.getAddr()); is.setString(8, (String) obj1.getPayto()); is.setString(9, (String) obj1.getCity()); is.setString(10, (String) obj1.getState()); is.setString(11, (String) obj1.getZip()); is.setString(12, (String) obj1.getZipext()); is.setString(13, (String) obj1.getBacktoPFS()); is.setString(14, (String) obj1.getFinclass()); is.setString(15, (String) obj1.getRefby()); is.setString(16, (String) obj1.getRefbyext()); is.setString(17, (String) obj1.getComments()); is.setString(18, "A"); is.setString(19, " "); is.setString(20, (String) obj1.getTelno()); is.setString(21, (String) authkey); is.setString(22, "W"); is.setString(23, nowString); is.setString(24, " "); is.setString(25, " "); is.setString(26, " "); is.setString(27, (String) obj1.getOperatorID()); is.setString(28, " "); is.executeUpdate(); status = "updated"; } else { System.out.println("duplicate patnum"); double enterAmt = (double) obj1.getRefundAmt(); String enterStay = (String) obj1.getRegid(); System.out.println("before loop"); while (result == true) { System.out.println("in loop 1"); String curSeqStr = (String) psRefund.getString(3); String curSeqSub = curSeqStr.substring(9, 10); curSeq = Integer.parseInt(curSeqSub); System.out.println("in loop 2"); hiSeq = hiSeq > curSeq ? hiSeq : curSeq; String tableStay = (String) psRefund.getString(2); System.out.println("in loop 3"); if (tableStay.equalsIgnoreCase(enterStay) == false) { result = psRefund.next(); continue; } System.out.println("in loop 4"); double curAmt = psRefund.getDouble(4); if (curAmt == enterAmt) { status = "duplicate"; break; } System.out.println("in loop 5"); if (hiSeq > 8) { status = "too many for patient"; break; } System.out.println("in loop 6"); result = psRefund.next(); System.out.println("in loop 7"); } System.out.println("after loop");
in my last test -- last thing to display on console is "stuck 3" -- no error message -- things appear to be "stuck"

another time i got passed this but got stuck in 'while loop' - dont know where yet because commented version wiht 'in loop' displays was added after last time it got to this point.

My environment is as follows --
i'm running client on a machine at home through a vpn to a server progrm (above code) at work which connects a SQL server also at work.

i'm using the JDBC ODBC Microsoft driver

[edited to add code tags]
[ July 07, 2005: Message edited by: Jeanne Boyarsky ]

I HAVE a NEW question; how do i jar my main-class? package is refunds;

dir structure c/javaproj/refunds

changed package on my classes to just refunds

i got it to work ;; added package refunds.DBStruct.DB to main classes -- no problem

WHY???

i did a little experiment -- when i move RefundDB2 (which invokes FormField) to c:/ and compile it fails on getOperatorID() -- the 22nd field which (i recently added to FormField) when compiled in c:\javaproj\refunds\DBStruct\DB -- no problem

somehow i think i'm pulling an old bad class file instead of the one i want but i don't see how -- i'm going to delete all FormField.class files on my system - recompile - rejar and try again.

i count 22 in the error message 3 before the double 15 after the double but before the boolean and 2 after the boolean 3 + 1 + 15 + 1 + 2 -- works without the package -- on antoher main class I tried to compile when a packaged class RefundDB2 tried to use FormFields i get:

C:\javaproj\refunds\PatientServer9.java:114: createRefund(DBStruct.FormFields) in DBStruct.DB.RefundDB2 cannot be applied to (FormFields)
String status = RefundDB2.createRefund(o2);
^
1 error

Tool completed with exit code 1

BTW "o2" is a FormField Object

RefundDB2 is in package DBStruct.DB;
FormField is in package DBstruct;