Search code examples
javadatabasedatasourceconnection-poolingwebsphere-7

Execute a sql statement when a connection is created

For our application to work properly we need to execute a SQL Statement on every new connection, before that connection is handed out to the application.

How do I configure a data source in WAS 7 accordingly?

We found the (deprecated) option to validate the datasource using a sql statement, which hopefully does the trick (coworker is testing it right now). This sounds wrong, since we are not 'testing' the connection, but setting it up properly. Also its deprecated so this probably will stop working with future versions of websphere

Is there a clean and correct way to do this?

The statement we'd like to execute is

ALTER SESSION NLS_SORT='GERMAN_AI'

One alternative approache: The application is hibernate based, so if we could convince hibernate to execute the statement before using a connection, this would work as well.

Solution

  • If it were me, I would just use the "connection test" approach:

    • It works!
    • The YAGNI principle says "worry about deprecation when it happens... if it ever happens" - probably years away or never
    • You will not add any business value by finding the "correct" way
    • You can drop this and get on with some real work that actually adds value to your project

    The only downside is that it will be executed every time a connection is tested, which may be many times during the life of the connection, but so what - it's a very fast executing statement and is idempotent, so no problem.