JDBC connection and transactions

[Peter Warde]

I'm not sure if the spec says anything about this, but if you have a EJB bean method running in a transaction and then open a JDBC connection and make a SQLStatement, is the transaction of the method propagated into the database such that a SQL update statement will rollback if an EJBException occurs.

Any ideas?

Peter

[Gian Franco]

Hi,

I don't think the transaction of the EJB bean is propagated
into the database, since the JDBC methods are non-EJB methods,
and they can't have transaction attributes specified in the
deployment descriptor like CMP beans.

Cheers,

Gian

[Keerthi P]

if you have a EJB bean method running in a transaction and then open a JDBC connection and make a SQLStatement, is the transaction of the method propagated into the database such that a SQL update statement will rollback if an EJBException occurs.

Yes... but the rollback/commit is guaranteed to work only if the EJB uses javax.sql.DataSource to 'get' the connection and the DataSource is managed by the container in which the bean is running.

If you use plain JDBC in the bean, there is no guarantee.

[Gemini Moses]

Hey Keerthi,

Glad that you are still posting on this board.
Thanks for all your helful replies.

Gemini
[ March 14, 2005: Message edited by: Gemini Moses ]

[Keerthi P]

[Gian Franco]

Hi Keerthi,

Could you please explain further , given the following
example, which uses javax.sql.Datasource to get the
connection as you mentioned:

public void processOrder(String order) {
    Context initCtx = new InitialContext();
    javax.sql.DataSource ds = javax.sql.DataSource)initCtx.lookup
        ("java:comp/env/jdbc/OrderDB");
    java.sql.Connection conn = ds.getConnection();
    try{
        conn.setAutoCommit( false );
        orderNo = createOrder( order );
        updateOrderStatus(orderNo, "order created");
        conn.commit();
    }
    catch( Exception e ){
        try{
            conn.rollback();
            throw new EJBException("Transaction rolled back: "
                + e.getMessage());
        }
        catch( SQLException sqle ){
            throw new EJBException("Rollback SQL error = "
                + sqle.getMessage());
        }
    }
}

Here, the first SQL statement after connect, commit, or rollback
implicitly initiates a JDBC transaction. Therefore I still
think the transaction of the calling bean won't be propagated to
the database.

Cheers,

Gian
[ March 15, 2005: Message edited by: Gian Franco Casula ]

[amol deshpande]

An Enterprise bean should not use commit and tx methods from Connection interface in bean code to interfere in Transactions!!!
Amol.

[Gian Franco]

Hi,

EJB bean-managed transaction (BMT)
offers the ability to use JDBC type
or JTA type transactions. Although
mixing JTA and JDBC transactions
is not recommended.

The above example is valid though

Cheers,

Gian

[Peter Warde]

This is brilliant. I didn't know I started off a such discussion. BMT is definetly a bad idea.