File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes stale jdbc Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "stale jdbc Connection" Watch "stale jdbc Connection" New topic
Author

stale jdbc Connection

Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
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
Ranch Hand

Joined: Jul 17, 2004
Posts: 354
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


Kai Witte's business website Kai Witte's private homepage Mock exam / preparation kit reviews
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
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
Ranch Hand

Joined: Jul 17, 2004
Posts: 354
hello,

the exact query would be indeed:

It performs extremely well on all common database servers.

Kai
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
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
Ranch Hand

Joined: Jul 17, 2004
Posts: 354
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:

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

Kai
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
this is how i plan to do it


[ November 05, 2006: Message edited by: Jesus Angeles ]
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
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
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
Mine is mainframe db2.
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
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
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
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
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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
Ranch Hand

Joined: Feb 26, 2005
Posts: 2046
Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: stale jdbc Connection
 
Similar Threads
recreating damaged connection in pool
Unreferenced
HTTP Connection pooling
multiple clients accessing entity beans
Stale Database Connections