• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

stale jdbc Connection

 
Ranch Hand
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).)
 
Ranch Hand
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello,

the exact query would be indeed:

It performs extremely well on all common database servers.

Kai
 
Jesus Angeles
Ranch Hand
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
this is how i plan to do it


[ November 05, 2006: Message edited by: Jesus Angeles ]
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mine is mainframe db2.
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks
 
I want my playground back. Here, I'll give you this tiny ad for it:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic