This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to understand not closed PreparedStatement and ResultSet. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to understand not closed PreparedStatement and ResultSet." Watch "How to understand not closed PreparedStatement and ResultSet." New topic
Author

How to understand not closed PreparedStatement and ResultSet.

damodar kumar
Ranch Hand

Joined: May 19, 2008
Posts: 77

I have a stand alone java program which loops across all tables present in the database, and does not close the prepared statements and result sets created during the code execution, which finally leaves as many un used prepared statements and result sets open as the number of tables.

Now the question is, will this hurt the machine that is executing this stand alone Java code? My observation is that the LAN card of the machine executing the stand alone java code shows 80% usage even after termination of the program. Restarting the machine makes things normal.

How to understand this?

PS - One more thing is, responsibly closing the result sets and the prepared statements is not eating off the LAN card even after program termination.


<a href="http://stackoverflow.com/users/668970/user668970" rel="nofollow">
<img src="http://stackoverflow.com/users/flair/668970.png" >
</a>
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Firstly, the reason you need to close resources is that they are not 'owned' by the JVM but by something external to the application.
If you don't close resources then something, somewhere will eventually run out of resources.

With database resources, these resources are owned by the database. If you don't close these resources (or tell the database that you're done with them) then your application will continue to run fine, but the database will begin to suffer and eventually will no longer be able to accept new connections and any one using the database - not just you - will suddenly stop working.

I'm not sure why you can see the issue locally on your network card, but it may depend on your JDBC driver and driver type. For example if the driver is type 2 or 3 it may rely on native code on the local system and this could remain active even after the JVM closed down.

The big lesson is that closing resources is important.
damodar kumar
Ranch Hand

Joined: May 19, 2008
Posts: 77

I forgot to say that the code is executing on a single connection. So, the DB has to only maintain, the results and the compiled prepared statements. The amount of memory foot print for this on the DB side, depends upon the type of the result set and the number of tuples in the result. This should be the impact on the DB server side. This is understood, and right now this is not the point I'm trying to understand.

The observation that the LAN card suffocates even after the program termination should be attributed to what? What I feel is that, the JDBC driver finally might be interacting with the LAN card through some JNI wrapper, which has a bug and did not release the LAN resources back to the Operating System. Can we think so? This just is a wild guess.

Any opinions?

Edit - Oh Sorry - you said the same thing David! Now this can be a probable bug that can be filed to SUN?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

But if the DB Driver uses native code and you don't tell the native code that you're done, how will it know? If you tell Sun they'll say it isn't a bug in their code, it's a bug in yours
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to understand not closed PreparedStatement and ResultSet.
 
Similar Threads
making a stand alone program as a JBoss resource
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
Portability
P6spy does not create spy.log for stand-alone Java program
From Applet to Stand-Alone