How to execute a stored procedure when getting a connection from datasource

[raylite3]

Hello,

I am using Oracle VPD (virtual private database) in my application to support multi-tenancy. To use Oracle VPD, I need to execute a stored procedure to set the context to the correct tenant every time I get a connection (and a corresponding stored proc to unset the context when I release the connection). I am using plain JDBC.

Is there a nifty callback or extension I can use for my Oracle datasource to execute this stored proc? Perhaps a <connection-listener> will work?

Any ideas appreciated.

Thanks!

[jesper.pedersen]

You posted the answer yourself Yes, <connection-listener> is the element you want.

[raylite3]

Great! Can you clarify if the listener will execute in a separate classloader (separate from my WAR classloader)? And can it access thread locals perhaps that I set in my webapp?

Thanks!

[jesper.pedersen]

It is executed in the thread getting the connection. You should use configuration properties to configure the listener.

See a simple test case in the IronJacamar code @ adapters/src/test/java/org/jboss/jca/adapters/jdbc/spi/ConnectionListenerTestCase.java on the 1.1 branch.

HTH

[raylite3]

Thanks , that helps !