problem with resultset closing
I have a class which has methods
a)to create a connection and a statement
b)execute a query then returns the resultset, and
c)executes update.
I put the closing of the stmt and connection in the finalize method.
I then have other classes use the executequery method of the above class and places the returned resultset object in a local variable. the local resultset object is being closed everytime processing is done.
I sometimes get 'Function Sequence Error' and sometimes it works just fine. I upgraded to JDK1.3 hoping it will solve the problem. But now I get 'ResultSet is Closed' error.
Does the statement get garbage collected somewhere in my calling program?
class A{
public A(){
..create connection
..create statement
}
ResultSet execQuery(String sql){
}
protectedvoid finalize{
..close statement
..close connection
}
}
class B{
A dbConnect =new A();
ResultSet rs = dbConnect.execQuery(sql);
...do processing
rs.close();
ResultSet rs2 = dbConnect.execQuery(sql);
...do processing
rs2.close();
}
Is something wrong? Why does the result set get closed?
Please help!
tnx.
[1861 byte] By [
jcorpuz] at [2007-9-26 6:46:45]
>ResultSet execQuery(...
You are returning a result set outside of the scope of the statement and connection that it is attached to.
Really BAD idea. Do NOT do that.
A ResultSet does NOT contain data. It contains a connection to data.
Extract the data from a result set and put it in another structure and return that instead.
the code goes like this
ResultSet execQuery(..){
return statement.executeQuery(..)
}
should i reference it to another resultset(instance variable) and return that reference?
ResultSet execQuery(..){
rs = statement.executeQuery(..)
return rs
}
what happens when another stmt in the calling program invoke the method execQuery?
i get the function sequence error intermittently.. but it still works fine sometimes. how come?
really need help.... tnx guys.
The data in your ResultSet is valid only for as long as the Statement that produced it is valid. If other Threads manipulate or close it, your ResultSet is worthless. To be on the safe side, don't return the ResultSet object, but extract your data inside the Method to whichever structure you need (Object[][], ArrayLists, whatever ..)
and return that structure.
And after retrieving immediately and explicitely close your result set.
You can resuse the statement, or close it also, but reuse the connection.
But at the end of using the connection, you should close it explicitely, not relying on the finalize method. You can't be sure if it will ever be executed, so you're maybe blocking precious DB resources then for the entire application time.
