• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

closing a statement vs a result set

 
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic