Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem with preparedStatement with SQL Server 2000

 
Bob Rubin
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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"


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
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what happens is it s NOT abending - its 'stopping' i get the aforemenioned displays ending with the 'stuck 3' then ---- nothing
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the SQL statement execute correctly if you run it directly against the DB? Which value does the variable fincl have?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic