problem with preparedStatement with SQL Server 2000

[Bob Rubin]

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 ]

[saravanan kanda swamy]

Hi,

Could you pleaes give the stack trace of the exception in the Catch block

you write the following code:

Catch(Exception e){
e.printStackTrace();
}

This will give you the complete stack trace of the exception.

I need some more information what is the operating system that you working on? Is it Windows XP ?


Thanks and Regards,
Saravanan.K

[Bob Rubin]

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)

[Bob Rubin]

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

[Ulf Dittmer]

Does the SQL statement execute correctly if you run it directly against the DB? Which value does the variable fincl have?

[Jeanne Boyarsky]

Bob,
That typically happens when another process has a lock on the table. Your code waits for the lock until it is free (which could be forever.)

Do you have any other programs going against this table? Like sqlPlus or your database's command line?

[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 --

[Bob Rubin]

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 ]

[Roger Chung-Wee]

The row locking was done for a Statement, so closing the Statement released the lock.

Incidentally, the connection variable is either instance or (worse) static. This is error-prone. I strongly suggest making it local if you were to turn your proof of concept into production code.

[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.

[Jeanne Boyarsky]

Originally posted by Bob Rubin:
why is this necessary and why does it work

Roger answered this very well.

Note that it is good practice to always close the connection, statement and resultset. In particular, you should do this in a finally clause (in production code) so it gets done even if an exception is thrown.