[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Locked table with JDBC Statement.executeQuery()

Posted on 2006-07-11
5
Medium Priority
?
1,027 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 total points
ID: 17089757
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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 how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

649 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