aspose file tools*
The moose likes JDBC and the fly likes closing a statement vs a result set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "closing a statement vs a result set" Watch "closing a statement vs a result set" New topic
Author

closing a statement vs a result set

William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
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.

Thanks for any input,
-=beeky
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

BTW- as for it feeling counter-intuitive... there are times when it is great to keep statements open while closing result sets, you just haven't encountered them yet.
William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
Scott,
Thanks for the reply.

I'm still puzzled about this usage:
ResultSet rs=conn.createStatement().executeQuery(...).

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.

What do you think?

-=beeky
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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 ]
William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
Thanks to all who replied. This turned out to be an very informative discussion.

My bottom line is this: cursors are associated with the Statement not the ResultSet (at least for the Oracle 10g JDBC driver). Closing the ResultSet is necessary but not sufficient.

-=beeky
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: closing a statement vs a result set