Forums Register Login

Oracle -- Closing Statements

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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


--
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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..
+Pie Number of slices to send: Send
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
And tomorrow is the circus! We can go to the circus! I love the circus! We can take this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 2093 times.
Similar Threads
Closing Connection Which way ????
Optimal JDBC connection pooling configuration for tomcat server
App doesn't always close out
MVC or DAO?
App doesn't always close out
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 06:19:54.