How to set auto commit on a WebSphere Application Server Connection

Technote (FAQ)


Question

How do I set auto commit on a WebSphere Application Server Connection?

Answer

Concerning auto commit,

Setting auto commit on a connection is a J2CA concept and is not necessarily a WebSphere Application Server concept. You do, however, set autoCommit on a connection ... by default auto commit in WebSphere Application Server is set to true, but you can set it to false, by calling connection.setAutoCommit(false) as is shown in the following URL and as shown below:

//get a userTransaction
javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction();
//retry indicates whether to retry or not
//numOfRetries states how many retries have
// been attempted
boolean retry = false;
int numOfRetries = 0;
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
do {
 try {
   //begin a transaction
   tran.begin();
   //Assumes that a datasource has already been obtained
   //from JNDI
   conn = ds.getConnection();
   conn.setAutoCommit(false);
   stmt = conn.createStatement();
   stmt.execute("INSERT INTO EMPLOYEES VALUES
             (0101, 'Bill', 'R', 'Smith')");
   tran.commit();
   retry = false;
 } catch(java.sql.SQLException sqlX)
 {
   // If the error indicates the connection is stale, then
   // rollback and retry the action
   if (com.ibm.websphere.rsadapter.WSCallHelper
       .getDataStoreHelper(ds)
       .isConnectionError(sqlX))
   {
     try {
       tran.rollback();
     } catch (java.lang.Exception e) {
       //deal with exception
       //in most cases, this can be ignored
     }
     if (numOfRetries < 2) {
       retry = true;
       numOfRetries++;
     } else {
       retry = false;
     }
   }
   else
   {
     //deal with other database exception
     retry = false
   }
 } finally {
   //always cleanup JDBC resources
   try {
     if(stmt != null) stmt.close();
   } catch (java.sql.SQLException sqle) {
     //usually can ignore
   }
   try {
     if(conn != null) conn.close();
   } catch (java.sql.SQLException sqle) {
     //usually can ignore
   }
 }
} while (retry) ;

.
The above means that you have to manually commit the connection ...
i.e. connection.commit() before you close the connection.

Further, you can only do this when you're outside of a global
transaction (in a Local Transaction Containment or LTC).
If you are within a global transaction, WebSphere Application Server
handles the commits for you.

There is no data source custom property for autoCommit. To set this property in WebSphere Application Server, you need to call setAutoCommit on the connection, as shown above.

This information is not restricted to IBM WebSphere Application Server
but it is part of development using JAVA Enterprise Edition.

The Javadoc from ORACLE/Sun on auto commit...etc is located here. :

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

WebSphere Application Server
Java Transaction Service (JTS)

Software version:

6.1, 7.0, 8.0, 8.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1600078

Modified date:

2013-05-29

Translate my page

Machine Translation

Content navigation