jas123
asked on
Checking the validity of an Oracle Connection
HI,
I use Oracle JDBC Driver's OracleConnection class in one of my applications where I have implemented connection pooling.
Now I faced a problem once. It is as follows:
When I once shutdown the oracle server when my application was up, I was unbale to get data from the databse, which is correct.
But ideally, the connection pool should not be affected in such circumstances.
If I get a connection form the pool and this connection is no more valid, then the conenction should be reinitialized.
How I can do this?
How do I know(in my ocde) that the oracle server is restarted?
Thanks
Regards
Jas
I use Oracle JDBC Driver's OracleConnection class in one of my applications where I have implemented connection pooling.
Now I faced a problem once. It is as follows:
When I once shutdown the oracle server when my application was up, I was unbale to get data from the databse, which is correct.
But ideally, the connection pool should not be affected in such circumstances.
If I get a connection form the pool and this connection is no more valid, then the conenction should be reinitialized.
How I can do this?
How do I know(in my ocde) that the oracle server is restarted?
Thanks
Regards
Jas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Some application servers (i.e. weblogic) have an additional step that may be worth considering, they have a test connection on reserve against a well-known table that does exist, if any error occurs then the connection is considered invalid (for Oracle the test query would be "SELECT 1 FROM dual").
the isClosed() and getWarnings() methods may help you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> Some application servers (i.e. weblogic) have an additional step that may be worth considering, they have a test
>connection on reserve against a well-known table that does exist, if any error occurs then the connection is considered
>invalid (for Oracle the test query would be "SELECT 1 FROM dual").
Yes, this one also. I completely forgot about this.
>connection on reserve against a well-known table that does exist, if any error occurs then the connection is considered
>invalid (for Oracle the test query would be "SELECT 1 FROM dual").
Yes, this one also. I completely forgot about this.
> A typical client
> * can determine that a connection is invalid by catching any
> * exceptions that might be thrown when an operation is attempted.
yes as i originally said, if the connection fails then it should be removed from pool.
> * can determine that a connection is invalid by catching any
> * exceptions that might be thrown when an operation is attempted.
yes as i originally said, if the connection fails then it should be removed from pool.
I made a CachedConnectionFactory class based on the Oracle classes OracleConnectionCacheImpl and OracleConnectionPoolDataSo urce.
In my CachedConnectionFactory.ge tConnectio n() method I use:
try {
tmpConnection = oracleConnectionCacheImpl. getConnect ion();
}
catch (SQLException ex1) {
logger.error("SQLException while getting connection.",ex1);
resetConnectionFactory();
tmpConnection = oracleConnectionCacheImpl. getConnect ion();
}
In resetConnectionFactory() I close the oracleConnectionCacheImpl object and create a new one. This works just fine for me.
If the second try does not work (SQLException) then the routing returns null.
In my CachedConnectionFactory.ge
try {
tmpConnection = oracleConnectionCacheImpl.
}
catch (SQLException ex1) {
logger.error("SQLException
resetConnectionFactory();
tmpConnection = oracleConnectionCacheImpl.
}
In resetConnectionFactory() I close the oracleConnectionCacheImpl object and create a new one. This works just fine for me.
If the second try does not work (SQLException) then the routing returns null.