stale jdbc Connection

[Jesus Angeles]

After getConnection(), do I need to always test if the connection is stale? like do a dummy Select on a table and see if it does not throw an Exception?

Before returning the connection object to the caller, do I need to do this? check if stale?

If this is needed, is there a better way to check if stale? ..as this method means we will do an extra sql query, and can be a performance issue.

(My production server is Websphere 5.x (not sure on the x).)

[Kai Witte]

hello Jesus,

some application servers have a feature to test a connection as they seem fit. For that purpose a test query would be configured along with the data source in the application server specific way.

I am not sure if you need such a test at all. If you do such a test select, no matter if you do it programmatically or with the help of the application server, don't do it on a table as you suggested! For performance reasons use something simple like "SELECT 1".

Kai

[Jesus Angeles]

When you say "SELECT 1" what do you mean? Do you mean exactly "SELECT 1" as the query?

(The production code I saw, which I think has that performance-dirty flaw, does "SELECT 1 from xxxxxx.yyyy".( I will still research but this looks like it returns only 1 column of '1's but selects the 'entire' table - a definite performance killer.) But anyway,that is not my application.)

[Kai Witte]

hello,

the exact query would be indeed:

SELECT 1

It performs extremely well on all common database servers.

Kai

[Jesus Angeles]

Technically what does it do? It connects to the database manager, the manager returns a single field with value 1?

Thereby, indirectly testing if the connection is stale?

If so, then its cool. Thats exactly what I need.

[Kai Witte]

hello,

the SELECT command is used to retrieve something from the database. It is followed by what should be retrieved. In this case it is used to test the connection, so it should be something that does not use much computation time: A constant:

SELECT 1

Typical real world SELECTs are a lot more complicated and typically involve table access, for example:

SELECT name FROM customers

Kai

[Jesus Angeles]

this is how i plan to do it

 int retryCount = 3;
 
String sql =
"select 1";
while (retryCount > 0) {
try {
Conn = ds.getConnection(DSID, DSPWD);

Statement st = Conn.createStatement();
ResultSet rs = st.executeQuery(sql);
rs.close();
st.close();
break;
} catch (SQLException se) {
disconnect(Conn);
PPLogger.info(
"PPDB2Connection",
" retry countdown connecting " + retryCount);
retryCount--;
PPLogger.error(
"PPDB2Connection",
" got error connecting " + se + " trying again...");
}
}  

[ November 05, 2006: Message edited by: Jesus Angeles ]

[Chris Hendy]

This comment only applies for Oracle, but

SELECT 1

won't work, the query needs to be

SELECT 1 FROM DUAL

where DUAL is a dummy table built into the RDBMS as standard.

[Jesus Angeles]

Mine is mainframe db2.

[Scott Johnson]

If you are using WebSphere's connection pooling library you don't need to worry about stale connections other than catching a stale connection exception.

For WAS 6 the exception is com.ibm.websphere.ce.cm.StaleConnectionException. I'm not sure about WAS 5.

WAS throws it whenever a stale connection is encountered. You can then do whatever exception handling is appropriate. For my application, I simply retry up to a set number of times.

You can also configure WAS to close all of the connections in the pool if one connection is stale so that greatly reduces the chances of hitting a second stale connection.

[Jesus Angeles]

Which commands do you 'try' for that exception?

---
Conn = ds.getConnection(DSID, DSPWD);

stmt = Conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {boName = rs.getString("NME_OFFICE_FULL").trim();}

rs.close();
stmt.close();
---

[Scott Johnson]

Which commands do you 'try' for that exception?

Sorry, I don't understand the question.

Are you asking what to put inside the try clause? You would put whatever normal JDBC calls you would normally write. And then catch the StaleConnectionException.

When a stale connection occurs, it's automatically detected. An exception is thrown to alert you to the situation so you can handle it as necessary.
[ November 06, 2006: Message edited by: Scott Johnson ]

[Jesus Angeles]

Yes. What I meant was: which calls to put inside a try clause.

Will the stale connection be detected in the call to 'ds.getConnection'? (java.sql.Connection Conn = ds.getConnection(DSID, DSPWD);)

[Scott Johnson]

Will the stale connection be detected in the call to 'ds.getConnection'?

I'm not absolutely certain, but I suspect it won't. It'll probably be detected when executeQuery() is called.

I doubt that the connection pooling library will test for a stale connection each time a connection is retrieved from the pool. That seems expensive.

[Jesus Angeles]

Thanks