How to combine JDBC & EJB in one transaction?

[Lu Samm]

Dear all,
I want to make JDBC and EJB(CMP1.1) in one transaction. Is it possible? how to implement?
Many thanks!

[Chris Mathews]

You need to use JTA either explicitly thru code or implicitly thru a Container Managed Transaction in an EJB. Your JDBC Driver must support the XA Protocol for 2 Phase Commit and so must your Application Server. Other than that it is fairly simple, yet resource intensive.

[Kent Farnsworth]

The method that does the JDBC calls must have the Transaction type as NotSupported, this will stop the JDBC calls from commiting data unwanted data to the database.

[Kyle Brown]

Kent, are you trying to imply that the default for the Connections are autocommit=true? That's not true of all vendors by any stretch of the imagination. For instance, it will work FINE in WebSphere if you just obtain a Connection, work with it, and then let the CMT handle the commits...
Kyle

[Kyle Brown]

Originally posted by Chris Mathews:
You need to use JTA either explicitly thru code or implicitly thru a Container Managed Transaction in an EJB. Your JDBC Driver must support the XA Protocol for 2 Phase Commit and so must your Application Server. Other than that it is fairly simple, yet resource intensive.

Actually this depends on your vendor's implementation. For instance, in WebSphere the container can detect that both the CMP and the JDBC call are against the same database and do this without XA... (it can use a single, shared connection within the transaction).
Kyle

[Lu Samm]

Dear all,
Thanks for your replies, but I still don't make a clear sense about it. I use websphere as application server. I'd like to develop my codes like this:
in a session bean:
public void update() {
//find my entity bean
//call update method of entity bean
//get connection and use the default tansaction mode
//invoke JDBC call without commit statement
}
the point is if I want to make entity bean update call and jdbc call in one transaction, what i need to do is set transaction attribute of the update() method of the session bean?
Thanks again!

[Kyle Brown]

You don't have to do anything special to the Session bean. If you set the transaction attribute to REQUIRES_NEW then it will use a transaction context if one is available, or create one if it's not. There's nothing else to do -- it will just work.
Kyle

[�ystein Andr�]

Originally posted by Kyle Brown:
Kent, are you trying to imply that the default for the Connections are autocommit=true? That's not true of all vendors by any stretch of the imagination. For instance, it will work FINE in WebSphere if you just obtain a Connection, work with it, and then let the CMT handle the commits...
Kyle

Hello. We are using WSAD 5.1, WebSphere 5.1(not yet, we are still only testing some things with the WebSphere Test Environment integrated in WSAD) and DB2 8.1. We would very much like to be able to mix updates/creates of CMP-beans with JDBC (SELECT only) within the same transaction. We would like to "see" the results of the updates and creates done for CMP-beans (from session bean methods) in results from SQL later in the same transaction. We have tried to find out how to achive this but have so far failed. I wonder how we do this? How do we to obtain the "correct" db-connection etc.?
�ystein

[Roland Barcia]

Here is a recent article on what Kyle is talking about with sharing connections:
http://www-106.ibm.com/developerworks/websphere/library/techarticles/0404_tang/0404_tang.html