File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

closing a statement vs a result set

 
William Stafford
Ranch Hand
Posts: 109
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 109
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 109
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic