aspose file tools*
The moose likes JDBC and the fly likes Oracle -- Closing Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle -- Closing Statements" Watch "Oracle -- Closing Statements" New topic
Author

Oracle -- Closing Statements

Kevin Conaway
Ranch Hand

Joined: Jan 27, 2006
Posts: 57
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
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
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

Joined: Jan 27, 2006
Posts: 57
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
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
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

Joined: Jan 27, 2006
Posts: 57
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
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
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

Joined: Jan 27, 2006
Posts: 57
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
 
wood burning stoves
 
subject: Oracle -- Closing Statements