?
Solved

Locked table with JDBC Statement.executeQuery()

Posted on 2006-07-11
5
Medium Priority
?
1,019 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
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…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
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
Course of the Month10 days, 2 hours left to enroll

762 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