Solved

Locked table with JDBC Statement.executeQuery()

Posted on 2006-07-11
5
979 Views
Last Modified: 2012-06-27
To anyone that can help, I have a problem with a Java application I am developing that interfaces to an MS SQL DB. I am running JDK 1.5 on Win2K Server using the ODBC bridge driver in JDBC. I confirmed that I have a locked table through the SQL Enterprise Manager logs. The strange thing is that it doesn't lock up until I have processed 3000 or so records. Since I am incorporating re-use code into my application, I did not look closely until now, and discover that I have code that has nested ResultSet objects trying to access the same tables like the example below:

-----------------------------------------------------------------
Connection con1 = null;
Connection con2 = null;
Statement stmt1 = null;
Statement stmt2 = null;
.
.

public void Method1InClass()
{
   String sqlQuery = null;
   String aResultString = null;
   ResultSet results1 = null;

   sqlQuery = "SELECT value1A.tableA, value1B.tableB
                     FROM tableA, tableB
                     WHERE tableA.id = tableB.id";

   con1 = DriverManager.getConnection(url,"","");
   stmt1 = con1.createStatement();

   stmt1.executeQuery(sqlQuery);

   while(results1.next())
   {
      // SOME OTHER CODE

      aResultString = results1.getString(value1A);
      Method2InClass(aResultString);

      // SOME OTHER CODE
   }

   con1.close();
   con1 = null;
   System.gc();
}

private void Method2InClass(String aName)
{
   String sqlQuery = null;
   String aResultString = null;
   ResultSet results2 = null;

   sqlQuery = "SELECT value2A.tableA, value2B.tableB
                     FROM tableA, tableB
                     WHERE tableA.value1A = aName
                     AND tableB.value2B = '<STRING>'";

   con2 = DriverManager.getConnection(url,"","");
   stmt2 = con2.createStatement();


   stmt2.executeQuery(sqlQuery); <--- CODE HANGS HERE
                                                        AFTER 3000 ROWS

   while(results2.next())
   {
      // SOME CODE TO PROCESS RESULTS
   }

   con2.close();
   con2 = null;
   stmt2 = null;
}
-------------------------------------------------------
Maybe there is something I am missing, but I would have expected the code to hang/lockup alot quicker than 3000 records, on account of the multiple ResultSet objects squashing each other from the get go. Stranger still, I do not have this problem on another DB that I access. The difference between that DB and the one I am using now, is that the DB that processes tens of thousands of records with no problem, has pointers to data in the DB, on the local disk. The DB that I am using which fails at ~3000 records, has referential data on a remote server through Samba. Any guesses as to what I can do to get past this problem (aside from re-coding things....which I'll probably end up doing anyway) or what I am doing wrong ??
Thanks.
0
Comment
Question by:jazzki
  • 2
5 Comments
 

Author Comment

by:jazzki
ID: 17082083
P.S. I also unsuccessfully tried processing the ResultSet objects in batches by trying to use Statement.setFetchSize(), but I get a "java.sql.SQLException: Invalid Fetch Size" error whenever I run my app. Don't understand.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 17089748
>> MS SQL DB. I am running JDK 1.5 on Win2K Server using the ODBC bridge driver in JDBC

Try the M$ JDBC driver instead.
0
 
LVL 30

Accepted Solution

by:
Mayank S earned 250 total points
ID: 17089757
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groupNoAdj 7 97
maven project error 5 56
Detect Closed Loops (circles, figure-8s, etc) in PNG Images 6 34
Which non-HTML GUI front end to use with Java? 3 22
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question