New-connection-sql Jboss

[tnas]

Hi,

I'm trying to use the <new-connection-sql> tag in the Datasource to Oracle. I want to execute this query:

SET ROLE RL_USER_SELECT IDENTIFIED BY RL_USER_SELECT, RL_ACCESS_CONSULT IDENTIFIED BY RL_ACCESS_CONSULT;

before to return the connection to the user.

The problem is that Jbos doesn't execute this query!  My datasource is:

<datasources>

  <local-tx-datasource>

    <jndi-name>MyAppDs</jndi-name>

    <connection-url>jdbc:oracle:thin:@dbtest.org:1521:dadm</connection-url>

    <user-name>usr</user-name>

    <password>pass</password>

    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

    <min-pool-size>5</min-pool-size>

    <max-pool-size>15</max-pool-size>

    <idle-timeout-minutes>5</idle-timeout-minutes>

    <track-statements/>

    <prepared-statement-cache-size>100</prepared-statement-cache-size>

    <new-connection-sql>

      SET ROLE RL_USER_SELECT IDENTIFIED BY RL_USER_SELECT, RL_ACCESS_CONSULT IDENTIFIED BY RL_ACCESS_CONSULT;

    </new-connection-sql>

  </local-tx-datasource>

</datasources>

What am I doing wrong?

Thanks.

[wdfink]

new-connection is only if the connection is created.

After that it will be keept open and held in the connection pool.

What if you use the validation statement which is called if the connection is used.

[tnas]

Thanks for your reply, but the check-valid-connection-sql doesn't work too.  Is it possible to see the output of the query?

[tnas]

I've supposed the error was because I'm using the old driver (ojdb10g-2.jar). So, I've changed the driver to ojdb5.jar but it didn't work.

[wdfink]

What JBoss and Oracle version do you use?

There are newer drivers, ojdbc6 I think, the driver should fit for Java/JBoss/Oracle versions.

[tnas]

I'm using Jboss 5.1 and Oracle 11g. I'll try to use the ojdb6 driver. Let's go to see what will happen.

[tnas]

Neither the ojdbc6 driver has worked . Is it possible to see the <new-connection-sql> output? I'm not sure if the query is executing.

[alexeev]

Try this:

<new-connection-sql>

      BEGIN SET ROLE RL_USER_SELECT IDENTIFIED BY RL_USER_SELECT, RL_ACCESS_CONSULT IDENTIFIED BY RL_ACCESS_CONSULT; END;

</new-connection-sql>

[seenukarthi]

This should work, I tried someting similar, it worked for me

<new-connection-sql>

      BEGIN EXECUTE IMMEDIATE 'SET ROLE RL_USER_SELECT IDENTIFIED BY RL_USER_SELECT'; EXECUTE IMMEDIATE 'RL_ACCESS_CONSULT IDENTIFIED BY RL_ACCESS_CONSULT'; END;

</new-connection-sql>