This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Prepared Statements not being freed by Informix Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statements not being freed by Informix" Watch "Prepared Statements not being freed by Informix" New topic
Author

Prepared Statements not being freed by Informix

curtis harrison
Greenhorn

Joined: Sep 12, 2001
Posts: 8
In the following code snip, I'm closing the prepared statement, yet it seems like the database is still caching a copy of the statement.
I ran this several thousand times on a machine, connected through our internal network to a database on a different machine with nothing connected to the database except for my java program. I observed free memory gradually decreasing on the database machine (using the unix 'top' function). When I ended my java process that was running the code below, the database became VERY busy for several minutes. The CPU was at about 0% idle time. The database was cleaning out a bunch of prepared statements (all the same statement content as below).
Do I need to explicitly 'free' the prepared statement?
How do I do this?
Do I need to force some garbage collection every so often to clear the prepared statements from the databases cache?
Here's the code snip:
void myMethod()
{
String sql = "insert into stress_output "+
"(iteration, start_date)"+
" values (?,?)";
PreparedStatement ps = pConn.prepareStatement(sql);

ps.clearParameters();
ps.setInt(1, myInt);
ps.setLong(2, myLong);
ps.execute();
ps.close();
}
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

PreparedStatements are allowed to be compiled and cached on the database if the driver and database support this function.
Have a quick look at the description for java.sql.PreparedStatement in the API, and also Connection.prepareStatement
There is also this article about PreparedStatements from TheServerSide.
DOM
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

"Do I need to force some garbage collection..."
Impossible. The best you can do is gently suggest that the gc run to clean up the jvm. Whether it does or not, no one knows(until it happens). But this probably won't help you, since the gc only clears the garbage from the jvm, not in the database. The database takes care of itself. Usually, a dbms will check to see if the statement is already cached before it adds it to cache, but this is dbms dependant. It looks like your dbms just keeps adding the same statement to cache, even though it already exists.
I have 2 alternatives that may help you:
1. Change the prepared statement to a simple statement (and change your code to statement.executeUpdate() which is the standard way of executing sql updates). See if it is actually the caching that is the problem. If the problem still persists when you use statements, then we're barking up the wrong tree!
2. Declare your preparedStatement at the class level. This way, it will be cached only once. Re-use it in your method as needed.
example:

Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared Statements not being freed by Informix