Solved

Locked table with JDBC Statement.executeQuery()

Posted on 2006-07-11
5
995 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Java string replace 11 63
ejb wildfly example 2 74
Eclipse Java import and method not resolved 4 81
batch vs regular insert in spring DAO 2 24
In this post we will learn different types of Android Layout and some basics of an Android App.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define ā€¦
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a vā€¦

733 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