This is a problem I had not too long ago. From what I remember, it seems that if you create a Connection object in the method you are calling and then try to pass back a ResultSet object, the garbage collector will close the connection and therefore the ResultSet object before it gets back to the calling method.
We had two work-arounds for this. One was to never pass ResultSet objects, but rather vectors, arrays, custom objects, whatever containing the data back. The second solution was to create the Connection object in the calling method then pass the Connection to the called method, use the passed Connection to execute the query, pass back the ResultSet, then close the Connection after the ResultSet has been used.
This might give you the gist of passing the Connection object:
public callingMethod()
{
Connection myconn =
ConnectionPool.getConnection();
ResultSet rs;
rs = queryMethod(someString, myconn);
doSomthingWithRS(rs);
rs.close();
myconn.close();
}
public queryMethod(
String someString,
Connection myconn)
{
ResultSet rs;
String sql = "select something from somewhere
where somestring = " + someString;
PreparedStatement ps =
myconn.prepareStatement(select);
ps.execute();
rs = ps.getResultSet();
return rs;
}
Hope this helps!