[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - How do you forcefully expire bad connections?

[Unquist]

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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4034193#4034193

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4034193