Calling procedure when connection is 'got'

[mlythgoe]

We're adapting an existing system that sets information inside the connection that is picked up by database triggers.
Currently the system gets a connection for a user and then passes it around all the methods to ensur that the same connection is used.
This connection has a user variable set when the connection is first obtained. When a trigger in the database is activated, it uses this value in one the columns in a row it inserts into a table.
Its actually an audit table and it is logging the change and who performed the change.
This uses an Oracle specific procedure called called DBMS_APPLICATION_INFO.SET_CLIENT_INFO.
We need to keep this feature. I am looking at passing the variable through using a ThreadLocal class but can't figure out how to set this when using Spring JDBC, which doesn't give you access to the connection, you only specify the datasource. As the datasource may change with each call, is there any way to intercept(?) the get connection and call the procedure to set the variable before the connection is used in the SPring JDBC statements?

[mlythgoe]

Could I use a method interceptor on DataSourceUtils.getConnection(DataSource dataSource, boolean allowSynchronization)?

It sounds right but I'm not sure if I can use the return value in the method interceptor?

something like:

public Object invoke(MethodInvocation methodInvocation) throws Throwable
{
try
{
Object retVal = methodInvocation.proceed();
// CALL THE PROCEDURE HERE
return retVal;
}
finally
{
}

[mlythgoe]

Scratch the AOP idea above, I don't think I can configure Spring to use the method interceptor unless it is a class that is obtained from a Spring context, which isn't the case here.
Am I right?
Any other suggestions?
How many times can somebody reply to their own post before it becomes a bit weird?

[dejanp]

All JDBCTemplate calls will use the same connection as long as they are all called from the same thread that's bound to the transaction.

In other words - you can have a method that starts the transaction, does the procedure call (using JDBCTemplate or org.springframework.jdbc.object.StoredProcedure for example), calls your JDBCTemplate stuff and finally ends the transaction.

[mlythgoe]

All JDBCTemplate calls will use the same connection as long as they are all called from the same thread that's bound to the transaction.

In other words - you can have a method that starts the transaction, does the procedure call (using JDBCTemplate or org.springframework.jdbc.object.StoredProcedure for example), calls your JDBCTemplate stuff and finally ends the transaction.

That sounds OK but I have to wrapper every DAO method (or every method that uses them with its own transaction) with another method that calls the stored procedure. I was hoping to find a solution that didn't increase the code too much. I'd much prefer to do this via wrappering the getConnection rather than my DOA calls.

[dejanp]

How do you manage your transactions?

[mlythgoe]

We're hoping Declarative transaction demarcation will be sufficient so we can just make POJO's transactional.

I'm also hoping to make the use of this stored procedure hidden in the application code except for the setting of the value required in a ThreadLocal

[dejanp]

I guess you could do this by implementing your own transaction manager (by extending the DataSourceTransactionManager) that does the sp call after super.doBegin().

It's not very elegant and better solution would be to do it AOP way - by adding your own interceptor, but I'm not really that deep in Spring AOP to tell you how. :)

[Rod Johnson]

Use the postInterceptors property of TransactionProxyFactoryBean to add a custom MethodInterceptor that invokes the stored proc. That's a simple way of doing it that doesn't require delving deeply into AOP theory :-)

We've used this in one complex project where we needed to set database session state for the current transaction, with excellent results.

[mlythgoe]

Use the postInterceptors property of TransactionProxyFactoryBean to add a custom MethodInterceptor that invokes the stored proc. That's a simple way of doing it that doesn't require delving deeply into AOP theory :-)

We've used this in one complex project where we needed to set database session state for the current transaction, with excellent results.

Thanks Rod, I'll look into this