Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why does my Connection.close fail?

 
eileen keeney
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thankyou,

That is the approach I am now taking.
 
eileen keeney
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(oh, I posted something here that goes somewhere else, so I removed it. I don't see a delete message.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic