aspose file tools*
The moose likes JDBC and the fly likes Why does my  Connection.close  fail? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Why does my  Connection.close  fail?" Watch "Why does my  Connection.close  fail?" New topic
Author

Why does my Connection.close fail?

eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
Whenever I attempt to close my Connection it causes my call to fail.

If I remove the Connection.close(); my method works just fine.

public static ResultSet doSelectQ(String myQ)
{
Statement mySm = null;
ResultSet myRs = null;
Connection dbConnection = null;

try {
dbConnection = stkConnect(); //another method which successfully connects and returns Connection
//no need to hold locks for this
dbConnection.setAutoCommit(true);
mySm = dbConnection.createStatement();
myRs = mySm.executeQuery(myQ);
}
catch( SQLException x ) {
System.out.println( "Could Not Get Data!" );
}

//this always causes an error, why?
/*
try {
dbConnection.close();
}
catch( SQLException x ) {
System.out.println( "Could Not Close Connection" );
}
*/
return myRs;
}

IF I comment out the dbConnection.close stuff (as shown commented out in the above code block) it works fine.
The select works just fine, and my data gets passed to the caller.

So something is either wrong with my close call or I have something else that has to close first.
I auto commit, so I don't think that is the problem (or maybe it is).


Thank you
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18563
    
    8

You would have more useful information if your code didn't ignore the exception. Try this instead:

And if that doesn't tell you enough, post the output here and ask about it.
eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
I didn't know that even existed (I am just learning this stuff, but probably should have looked up all the available stuff in x)

Anyway, I test the method with this block of code:
try {
ResultSet myRs = myDbUtils.doSelectQ(myQ);
System.out.println("ResultSet myRs returned \n");

while (myRs.next())
{
route_id = myRs.getString("route_id");
System.out.println("ROUTE_ID=" + route_id + "\n");
}
}

catch( SQLException x ) {
System.out.println( "Could Not Get Data!" );
x.printStackTrace();
}

and I get this output:
ResultSet myRs returned
Could Not Get Data!
java.sql.SQLException: Closed Connection: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181)
at testecspack2.main(testecspack2.java:35)


I just added the println "ResultSet myRs returned", and the fact that it is showing up, tells me the problem is later.

So now I am suspecting that I need the database connection to read through my result set, and can not close it yet.
So I guess that I need to process my result set into another useful structure before I pass it back, if I want to close my database connection inside that method.


So when will the connection automatically close?
Harm in leaving it open until it automatically closes, as long as I am not pending any commits?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18563
    
    8

eileen keeney wrote:So now I am suspecting that I need the database connection to read through my result set, and can not close it yet.
So I guess that I need to process my result set into another useful structure before I pass it back, if I want to close my database connection inside that method.

So when will the connection automatically close?
Harm in leaving it open until it automatically closes, as long as I am not pending any commits?

Yes, you're suspecting correctly. You do indeed need the connection so you can use the ResultSet to get data from the database. (Don't assume that the ResultSet "contains" the data from your query. It doesn't. It just helps you to get that data from the database.)

And when will the connection automatically close? It won't ever automatically close. Never. And yes, there is harm in leaving it open, as doing so makes your database server allocate resources (memory, disk, and so on) which won't ever be freed until you do close it.

So what you should do is something like this:

Open a Connection.
Create a Statement from it.
Create a ResultSet from that.
Extract all the data from the ResultSet into some kind of collection, or at any rate use all of the data to do whatever.
Close the ResultSet, the Statement, and the Connection.

Check out the JDBC Tutorial.
eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
Thankyou,

That is the approach I am now taking.
eileen keeney
Ranch Hand

Joined: May 04, 2009
Posts: 51
(oh, I posted something here that goes somewhere else, so I removed it. I don't see a delete message.)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why does my Connection.close fail?