Win a copy of Cross-Platform Desktop Applications: Using Node, Electron, and NW.js this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Prepared Statements not being freed by Informix  RSS feed

curtis harrison
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.setInt(1, myInt);
ps.setLong(2, myLong);
David O'Meara
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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.

  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!