We have an application that has been in use for about five years with no problems. The application processes an ever growing table. Today we seemed to have hit a scalability limit The app failed with an ORA-01000 "exceeded maximum open cursors".
After a bit of experimentation I determined that this usage was causing the problem:
ResultSet rs = conn.createStatement().execute("... query here.."); ... do something with the result set rs.close().
And this, seemingly synonymous, usage fixed the problem: Statement stmt = conn.createStatement() ; ResultSet rs = stmt.execute("... query here.."); ... do something with the result set stmt.close() ;
The JDBC driver is the one supplied with Oracle 10g.
The first usage apparently does not close the cursor associated with the result set while the second does.
What gives? I can't find any documenation that explains this and it seems counter-intuitive.
I think you may have it backwards, stmt.close() will automatically close any associated result sets (check the JDBC API) but the reverse is not true.
Keep in mind, statements are reusable after there result sets are closed. For example, for batch updates/reads, you may execute a single statement multiple times (calling setString(?,?) etc to change values in-between executions). The key here is that if close a statement and try to run a query again, it will throw an error since the statement is no longer accessible.
In short, yes you need to close statements. Its good practice to always explicitly close result sets, statements, and connections.
Originally posted by William Stafford: This would seem to be a time bomb. It looks innocuous enough but if executed enough times it will leak all of the available cursors. As I have convincingly demonstrated.
I would think that this usage should be discouraged but I have seen nothing to this effect in any Java literature.
You are correct that it should be discouraged. Not cleaning up after oneself (via closing resources) is a bad idea.
Scott is correct that there are times it is good to keep the statement open while closing the resultset. However, you would still want to close the statement when you are done with it. It's just that this would be after processing multiple resultsets. And of course to do this, you still need a reference to the statement.
Originally posted by William Stafford: ResultSet rs=conn.createStatement().executeQuery(...).
Well, there's two problems there. One, you lose the reference to the statement as you rightly pointed out. Most (hopefully all) JDBC programmers learn the value of keeping references to the Connection/Statement/ResultSet, and its a common practice by most (again, hopefully all) JDBC developers to close them after using them. Second, its sort of a magic-servlet-style line of code. You should never have lines of code that do too much, it makes code hard to read and maintain. That's why code like this should always be broken into two lines, for maintenance reasons if nothing else.
FYI, your example provides a good technical interview exam question. Ask an applicant what's wrong with that line of code. Any good JDBC programmer with years of experience would be able to tell you what's so bad about it. [ July 09, 2008: Message edited by: Scott Selikoff ]