0 Replies Latest reply on Apr 3, 2007 12:45 PM by Peter Guarnieri

    How do you forcefully expire bad connections?

    Peter Guarnieri Newbie

      Here's the situation: We have configured a local-tx-datasource for Jboss that connects to an Oracle db. We have a struts app that uses this data source. It happens from time to time that a developer will develop a query that for whatever reason causes oracle to return an exception, and thus throws an SQLException (for example, trying to make a query on a table that the user account can't see). When this happens, it looks like even though in our Java code we're calling:

      finally
      {
      connection.close();
      }

      the connection is not actually being closed. Thus after about 20 bad calls (we have max number of connections set to 20) we get a "No connections left in the pool" error message.

      We of course are addressing this by fixing the bad queries, but sometimes the error is on the db end (i.e. a permission setting) and it's not a db we have control over. Thus I'd like the whole thing to be robust enough that when this happens, the connections that didn't work will be put back into the pool. I have two questions then:

      1) Does the idle timeout that you can set count for connections like this? I.e. is a bad connection considered idle?
      2) Is there any way to force JBoss to recycle bad connections like this? Close them and make a new connection available in the pool?

      Thanks,
      Pete