Tests for db connection

[kejoseph]

Hi,

Can someone tell me the exact difference between check-valid-connection-sql and valid-connection-checker-class-name ? As I understand, the latter was implemented recently and possibly is superior to the former. What I am trying to find out is in what way ? Would it automatically check for database failures and re-create the connection ? Would the developers need to modify their code as well ?

(I know that the valid-connection-checker only has support for Oracle, but that is fine with me as I work with Oracle databases).

-Kevin.

[peterj]

With check-valid-connection-sql, you place a SQL statement in *-ds.xml and that statement is run. If there is no error, it is assumed that the connection is valid.

With valid-connection-checker-class-name, you write a class that performs a connection check. This class can do anything. For example, it might query certain tables to ensure that the expected data exists, or perform a benign update to ensure that the account used has update right (or doesn't have update rights!)

So the question is, would running a simple SQL statement be sufficient to verify the connection, or do you need to do something more complex?

[kejoseph]

Thats for the reply Peter. My requirement is simple - I want the db connection to be tested by JBoss prior to handing it over to the application. This is easily achieved by the former check-valid-connection-sql statement.

However, in certain cases (say Oracle package changed and JBoss start's receiving ORA-04062) we need to stop and start JBoss even with the above statement. I am wondering whether replacing it with valid-connection-checker-class-name will help resolve this issue and save us from a restart everytime something on Oracle side changes.

Of course, I will try this out ; but wanted to understand the inner workings before blindly changing it. The documentation is not very detailed in this regard.

Kevin

[peterj]

Looking at the documentation on that Oracle error, it would appear that because of a database change that the current connection(s) are no longer valid and I guess that what you are looking for is some way of closing the existing connections and reestablishing them. I have seen requests for this reconnect capability before, but I do not think it is currently possibly.

And to answer your specific question, using valid-connection-checker-class-name will not help you resolve this issue.

[peterj]

I had another thought. The issue appears to have something to do with timestamps assigned to stored procedures. All of the posts I have seen on this recommend recompiling the client code so that the client picks up the new timestamps. Well, in Java, I suspect that the Oracle JDBC driver loads the timestamps. If the Oracle JDBC driver provides a way to reload the timestamps, then if you wrote a valid-connection-checker-class-name class, you could have it make that Oracle JDBC driver call to reload the timestamps. So the question then becomes, does the Oracle JDBC driver provide such a feature?