Websphere: inject a ALTER SESSION command to datasource connection

Question

In our development machines we are using Websphere Apllication Server Liberty accessing a Oracle database. To configure case-insensitive or accent-insensitive sort we configure the Datasource session of server.xml file as:

<dataSource id="MyDS" jndiName="jdbc/MyDS" type="javax.sql.DataSource">
    <jdbcDriver javax.sql.DataSource="oracle.jdbc.pool.OracleDataSource" libraryRef="OracleJDBC"/>
    <connectionManager maxPoolSize="10" minPoolSize="2"/>
    <properties.oracle URL="jdbc:oracle:thin:@xx.xxx.xxx.xxx:1521:database" password="..." user="..." />

    <onConnect>ALTER SESSION SET nls_sort = binary_ai</onConnect>
    <onConnect>ALTER SESSION SET nls_comp = linguistic</onConnect>

</dataSource>

In Production machines we use Websphere Apllication Server. To configure the dataSource configurations it is needed go to the administrative console, Resources > JDBC > Data sources. Although I was able to connect to the Data source, I could not find a way to inject those ALTER SESSION commands above.

I suspect that I need to add a new property in Custom properties page. I tried add a property onConnect with both commands (separated with ';'), but it did not work. Even googling, I could not find a satisfactory answer.

Does someone have any idea how to make this configuration?

Thanks,

Rafael Afonso

Answer 1

To do that on WebSphere traditional go to WebSphere Application Server data source properties under Additional Properties and check validate new connections. Provide your SQL statements in the Validation by SQL query section.

Answer 2

The answer that Alex gave is the simplest way to do this in WebSphere Application Server traditional. For completeness, I wanted to point out that there is one other way of doing this in WebSphere Application Server traditional, which is by defining a custom data store helper and overriding the doConnectionSetup method to perform the desired SQL command(s). This approach might be necessary if unable to specify multiple SQL commands within the Validation by SQL query attribute. In that case, leave Validation by SQL query blank and do not select either of the validate new connections or Validate existing pooled connections options.

Answer 3

Alex:

I tried your suggestion. In imagem below, I added this command:

ALTER SESSION SET nls_sort = binary_ai; ALTER SESSION SET nls_comp = linguistic; commit;

However, when I runned my application, it made no difference. Besides, as we can see in the image, the Validation by SQL query section is deprecated.