• 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
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Oracle -- Closing Statements

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This question is directed to R.M. Menon:

I'm curious about the difference between closing a Statement explicitly on the Statement object used to execute the stored procedure or closing the Statement that is obtained from a ResultSet.

If I call a stored procedure that returns a cursor to me like so:


In the above code, is there a practical difference between:



and



Looking at each object in the debugger suggests that they are different objects, but I'm not quite sure.

Thanks!

Kevin
 
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

In the above code, is there a practical difference between:

code:
--------------------------------------------------------------------------------

stmt.close();rs.getStatement().close();

--------------------------------------------------------------------------------



and


code:
--------------------------------------------------------------------------------

rs.getStatement().close();




I m afraid, I am not sure exactly what you are looking for... In first case, you are closing the statement twice?

Regardless, the simplest thing to do is to close the result set before the statement and always in the finally clause:
try {
..}
finally {
if( rs != null ) {
rs.close();
}
if( stmt != null ) {
stmt.close();
}
}

Having said that, as long as you close both (Result Set first), you should be ok.
 
Kevin Conaway
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
R.M,

Thanks for getting back to me.

Sorry about the confusion. I meant to say is there any difference between:

rs.getStatement().close()

and

stmt.close();

Basically, I'm trying to save a few lines of code for every single database call. I'd like to have a utility method that closes the ResultSet, its parent Statement, and optionally, the Connection.

In my first post, stmt and rs.getStatement() appear to be different objects. Is that the case? If I close one, will the other remain open?

Thanks,

Kevin
 
R. M. Menon
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you want to save lines, why not use a utility method in a class such as JDBCUtil? - like so:
public static void close ( ResultSet resultSet, Statement statement )
{
try
{
if( resultSet != null )
resultSet.close();
}
catch ( SQLException ignored ) {
// may be log the error here...
}
try
{
if( statement != null )
statement.close();
}
catch ( SQLException ignored ) {
// may be log the error here...
}
}

Then you can simply invoke this method. Anyways, from the doc, as long as you generated the ResultSet from the statement that you want to close, you should be fine (assuming you are checking for nulls in the object.)

-- JDBC doc
getStatement

public Statement getStatement()
throws SQLException

Retrieves the Statement object that produced this ResultSet object. If the result set was generated some other way, such as by a DatabaseMetaData method, this method returns null.

Returns:
the Statment object that produced this ResultSet object or null if the result set was produced some other way
Throws:
SQLException - if a database access error occurs
Since:
1.2


--
 
Kevin Conaway
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
R.M,

I actually read the doc for that method and I was confused by it.

When it says, Retrieves the Statement object that produced this ResultSet object. If the result set was generated some other way, such as by a DatabaseMetaData method, this method returns null., does that mean that

ResultSet rs = (ResultSet) stmt.getObject(1) is NOT produced by the statement? I assume that ResultSet rs = stmt.getResultSet() is supported.

Kevin
 
R. M. Menon
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It simply means if the ResultSet came from a statement object - then it would return that statement object otherwise null.

I would simply create overloaded close methods in a util class to resolve this issue and not worry too much about it..
 
Kevin Conaway
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So I would be safe then in a situation where a ref cursor is returned from a stored procedure and cast to a ResultSet with stmt.getObject()?

I've had problems with "too many open cursors" so I'm a little paranoid about it.

Thanks for your help,

Kevin
 
Your mind is under my control .... your will is now mine .... read this tiny ad
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic