How to bind the unWrapped native connection object to Spring @Transactional platformTransactionManager about btm HOT 12 CLOSED

I do see a few things that do look suspicious to me:

1. Why do you need to unwrap the connection? The reason why the BTM pool wraps them is to intercept your statement creation calls to be able to enlist the connection into the transaction, i.e.: know what connection participates into what transaction. By using the unwrapped connection directly you bypassed this logic so BTM is unaware of what's going on. Don't do that.

2. Since you're using both JMS and JDBC in the same method together with a transaction manager I guess that you're willing to create a transaction that can either commit or rollback both atomically. If you don't create a BTM JMS connection pool, your JMS connections won't participate in the same transaction as your JDBC connections do, so rolling back the transaction will only rollback your JDBC work.

3. The Spring @transaction annotation does not rollback on exception by default, at least as far as I remember. You have to parameterize the annotation with rollbackFor=Throwable.class fr instance to make that happen.

from btm.

I need the native connection to create an oracle array which is only available through oracle.jdbc.OracleConnection.
Example : Array array1= ((oracle.jdbc.OracleConnection) conn).createOracleArray("VARCHAR2", arrayObject);

from btm.

Fine, create the array with the unwrapped connection object, but do everything else with the wrapped one.

from btm.

Agreed. This is true of any connection pool, especially in a transaction manager... ie the wrapped Connection should be used for everything possible, and standard JDBC API methods as much as possible too. It is pretty easy, and common practice, to unwrap the Connection only in very specific code blocks when non-standard JDBC functionality is needed.

from btm.

Thanks @lorban and @jonesde this works fine. the only issue which i see now is, I need to call a Stored Procedure (SP) from java callablestatement passing the arrayobject as one parameter.

1. with Drivermanager.getconnection of oracl connection the SP returns success .
2. with poolingdatasource.getconnection() at runtime invoked from my server the SP returns failure.

Where exactly could the issue be? java or SP?

from btm.

No idea, could be both.

You could try to unwrap the callable statement before executing it to see if that makes any difference. But it could very well be that your SP does something that is forbidden in a XA transaction context.

from btm.

approach1:
OracleConnection nativeConnection = conn.unwrap(OracleConnection.class);
java.sql.Array array1= nativeConnection.createOracleArray("VARCHAR",arrayName);
CallableStatement stmt = conn.prepareCall({"call SP1(?,?)"}); // This is java.sql.Connection which is bound to BTM

In this approach, SP returns ERROR.

Approach2:
OracleConnection nativeConnection = conn.unwrap(OracleConnection.class);
java.sql.Array array1= nativeConnection.createOracleArray("VARCHAR",arrayName);
CallableStatement stmt = nativeConnection.prepareCall({"call SP1(?,?)"}); // This is nativeConnection

In this approach, SP returns Success.(But the problem with this approach is the callable statement is not bound to java.sql.connection which we got from poolingdatasource )

from btm.

That doesn't invalidate what I've said: it could very well be that your SP does something that is forbidden in a XA transaction context.

As an experiment, you could try to force enlistment of the XAResource and work with the native connection. Simply execute SELECT 1 FROM DUAL using the wrapping connection right before using the unwrapped one should make sure the connection is bound to the XA transaction's context and should allow you to freely use the unwrapped connection.

from btm.

Thanks @lorban , Issue fixed. COMMIT was inside the Procedure which shouldn't be

from btm.

one last thing. do we need to close the connection which we unwrapped for performing the native connection operations?

from btm.

Definitely not, but you do have to close the wrapping one.

from btm.

got it!

from btm.