-
1. Re: Tests for db connection
peterj Jan 11, 2009 3:18 PM (in response to kejoseph)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? -
2. Re: Tests for db connection
kejoseph Jan 11, 2009 3:39 PM (in response to 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 -
3. Re: Tests for db connection
peterj Jan 11, 2009 4:01 PM (in response to kejoseph)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. -
4. Re: Tests for db connection
peterj Jan 11, 2009 4:11 PM (in response to kejoseph)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?