+

Search Tips   |   Advanced Search

Exceptions pertaining to data access

Java Database Connectivity (JDBC) applications receive a standard SQL exception if any JDBC operation fails. The product provides special exceptions for its relational resource adapter (RRA), to indicate that the connection currently held is no longer valid.

Enterprise bean container-managed persistence (CMP) beans under the EJB 2.x specification receive a standard EJB exception when an operation fails.

The userDefinedErrorMap custom property overlays existing entries in the error map by starting the DataStoreHelper.setUserDefinedMap method. Use the custom property to add, change, or remove entries from the error map.

For example, to remove the mapping of SQLState S1000, add a mapping of error code 1062 to duplicate key, and add a mapping of SQLState 08004 to stale connection, we can specify the following value for userDefinedErrorMap:

userDefinedErrorMap can be located in the administrative console by selecting the data source and configuring the custom properties.


Stale Connections

The product provides a special subclass of the java.sql.SQLException class for using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a WebSphere 4.0 data source and in the most recent version data source that use the relational resource adapter. This class serves to indicate that the connection currently held is no longer valid.

This situation can occur for many reasons, including the following:

To avoid encountering StaleConnectionExceptions:

If we are trying to use an orphaned connection or a connection that is made unavailable by auto connection cleanup, a stale connection exception indicates that the application has attempted to use a connection already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a stale connection exception indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, we cannot recover it, and we must completely close the connection rather than returning it to the pool.


Detecting stale connections

When a connection to the database becomes stale, operations on that connection result in an SQL exception from the JDBC driver. Because an SQL exception is a rather generic exception, it contains state and error code values we can use to determine the meaning of the exception. However, the meanings of these states and error codes vary depending on the database vendor. The connection pooling run time maintains a mapping of which SQL state and error codes indicate a stale connection exception for each database vendor supported. When the connection pooling run time catches an SQL exception, it checks to see if this SQL exception is considered a stale connection exception for the database server in use.


Recovering from stale connections

An application can catch a stale connection exception, depending on the type of error detection model configured on the data source:

Because of the differences between error detection models, the application server provides an API that applications can use with either case to identify stale connections. The API is com.ibm.websphere.rsadapter.WSCallHelper.getDataStoreHelper(datasource).isConnectionError(sqlexception).

Applications are not required to explicitly identify a stale connection exception. Applications are already required to catch the java.sql.SQLException, and the stale connection exception or the exception that is raised by the JDBC driver, always inherits data from the java.sql.SQLException. The stale connection exception, which can result from any method that is declared to raise SQLException, is caught automatically in the general catch-block. However, explicitly identifying a stale connection exception makes it possible for an application to recover from bad connections. When application code identifies a stale connection exception, it should take explicit steps to recover, such as retrying the operation under a new transaction and new connection.


Example: Handling data access exception - stale connection

These code samples demonstrate how to programmatically address stale connection exceptions for different types of data access clients in different transaction scenarios.

When an application receives a stale connection exception on a database operation, it indicates that the connection currently held is no longer valid. Although it is possible to get an exception for a stale connection on any database operation, the most common time to see a stale connection exception issued is after the first time the connection is retrieved. Because connections are pooled, a database failure is not detected until the operation immediately following its retrieval from the pool, which is the first time communication to the database is attempted. It is only when a failure is detected that the connection is marked stale. The stale connection exception occurs less often if each method that accesses the database gets a new connection from the pool.

Many stale connection exceptions are caused by intermittent problems with the network of the database server. Obtaining a new connection and retrying the operation can result in successful completion without exceptions. In some cases it is advantageous to add a small wait time between the retries to give the database server more time to recover. However, applications should not retry operations indefinitely, in case the database is down for an extended time.

If we are developing applications for the WebSphere Application Server with an integrated development environment (IDE) like Eclipse, you might must import the app_server_root/plugins/com.ibm.ws.runtime.jar file into the development environment to take advantage of code that is provided.

Before the application can obtain a new connection for a retry of the operation, roll back the transaction in which the original connection was involved and begin a new transaction. You can break down details on this action into the following two categories:

Objects operating in a bean-managed global transaction context begun in the same method as the database access

A servlet or session bean with bean-managed transactions (BMT) can start a global transaction explicitly by calling begin() on a javax.transaction.UserTransaction object, which we can retrieve from naming or from the bean EJBContext object. To commit a bean-managed transaction, the application calls commit() on the UserTransaction object. To roll back the transaction, the application calls rollback(). Entity beans and non-BMT session beans cannot explicitly begin global transactions.

If an object that explicitly started a bean-managed transaction receives a stale connection exception on a database operation, close the connection and roll back the transaction. At this point, the application developer can decide to begin a new transaction, get a new connection, and retry the operation.

The following code fragment shows an example of handling stale connection exceptions in this scenario:

//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) ;

Objects operating in a global transaction context and transaction not begun in the same method as the database access.

When the object which receives the stale connection exception does not have direct control over the transaction, such as in a container-managed transaction case, the object must mark the transaction for rollback, and then indicate to its caller to retry the transaction. In most cases, we can do this by creating an application exception that indicates to retry that operation. However this action is not always allowed, and often a method is defined only to create a particular exception. This is the case with the ejbLoad() and ejbStore() methods on an enterprise bean. The next two examples explain each of these scenarios.

Example 1: Database access method creates an application exception

When the method that accesses the database is free to create whatever exception is required, the best practice is to catch the stale connection exception and create some application exception that we can interpret to retry the method.

The following example shows an EJB client calling a method on an entity bean with transaction demarcation TX_REQUIRED, which means that the container begins a global transaction when insertValue() is called:

public class MyEJBClient
{
  //... other methods here ...

  public void myEJBClientMethod()
  {
    MyEJB myEJB = myEJBHome.findByPrimaryKey("myEJB");
    boolean retry = false;
    do
    {
      try
      {
        retry = false;
        myEJB.insertValue();
      }
      catch(RetryableConnectionException retryable)
      {
        retry = true;
      }
      catch(Exception e) { /* handle some other problem */ }
    }
    while (retry);
  }  
}  //end MyEJBClient

public class MyEJB implements javax.ejb.EntityBean
{
  //... other methods here ...
  public void insertValue() throws RetryableConnectionException,
         java.rmi.EJBException
  {
    try
    {
      conn = ds.getConnection();
      stmt = conn.createStatement();
      stmt.execute("INSERT INTO my_table VALUES (1)");
    }
    catch(java.sql.SQLException sqlX)
    {
      // Find out if the error indicates the connection is stale
      if (com.ibm.websphere.rsadapter.WSCallHelper
          .getDataStoreHelper(ds)
          .isConnectionError(sqlX))
      {
        getSessionContext().setRollbackOnly();
        throw new RetryableConnectionException();
      }
      else
      {
        //handle other database problem
      }
    }
    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
      }
    }
  }
}  //end MyEJB

MyEJBClient first gets a MyEJB bean from the home interface, assumed to have been previously retrieved from the Java Naming and Directory Interface (JNDI). It then calls insertValue() on the bean. The method on the bean gets a connection and tries to insert a value into a table. If one of the methods fails with a stale connection exception, it marks the transaction for rollbackOnly (which forces the caller to roll back this transaction) and creates a new retryable connection exception, cleaning up the resources before the exception is thrown. The retryable connection exception is simply an application-defined exception that tells the caller to retry the method. The caller monitors the retryable connection exception and, if it is caught, retries the method. In this example, because the container is beginning and ending the transaction; no transaction management is needed in the client or the server. Of course, the client could start a bean-managed transaction and the behavior would still be the same, provided that the client also committed or rolled back the transaction.

Example 2: Database access method creates an onlyRemote exception or an EJB exception

Not all methods are allowed to throw exceptions defined by the application. If we use bean-managed persistence (BMP), use the ejbLoad() and ejbStore() methods to store the bean state. The only exceptions issued from these methods are the java.rmi.Remote exception or the javax.ejb.EJB exception, so we cannot use something similar to the previous example.

If we use container-managed persistence (CMP), the container manages the bean persistence, and it is the container that sees the stale connection exception. If a stale connection is detected, by the time the exception is returned to the client it is simply a remote exception, and so a simple catch-block does not suffice. There is a way to determine if the root cause of a remote exception is a stale connection exception. When a remote exception is created to wrap another exception, the original exception is usually retained. All remote exception instances have a detail property, which is of type java.lang.Throwable. With this detail, we can trace back to the original exception and, if it is a stale connection exception, retry the transaction. In reality, when one of these remote exceptions flows from one Java Virtual Machine API to the next, the detail is lost, so it is better to start a transaction in the same server as the database access occurs. For this reason, the following example shows an entity bean accessed by a session bean with bean-managed transaction demarcation.

public class MySessionBean extends javax.ejb.SessionBean
{
  ... other methods here ...
  public void mySessionBMTMethod() throws
    java.rmi.EJBException
  {
    javax.transaction.UserTransaction tran =
    getSessionContext().getUserTransaction();
    boolean retry = false;
    do
    {
      try
      {
        retry = false;
        tran.begin();
        // causes ejbLoad() to be invoked
        myBMPBean.myMethod();
        // causes ejbStore() to be invoked
        tran.commit();
      }
      catch(java.rmi.EJBException re)
      {
        try
        {
          tran.rollback();
        }
        catch(Exception e)
        {
          //can ignore
        }
        if (causedByStaleConnection(re))
          retry = true;
        else
          throw re;
      }
      catch(Exception e)
      {
        // handle some other problem
      }
      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);
  }

  public boolean causedByStaleConnection(java.rmi.EJBException re)
  {
    // Search the exception chain for errors
    // indicating a stale connection
    for (Throwable t = re; t != null; t = t.getCause())
      if (t instanceof RetryableConnectionException)
        return true;

    // Not found to be stale
    return false;
  }
}

public class MyEntityBean extends javax.ejb.EntityBean
{
  ... other methods here ...
  public void ejbStore() throws java.rmi.EJBException
  {
    try
    {
      conn = ds.getConnection();
      stmt = conn.createStatement();
      stmt.execute("UPDATE my_table SET value=1 WHERE
      primaryKey=" + myPrimaryKey);
    }
    catch(java.sql.SQLException sqlX)
    {
      // Find out if the error indicates the connection is stale
      if (com.ibm.websphere.rsadapter.WSCallHelper
          .getDataStoreHelper(ds)
          .isConnectionError(sqlX))
      {
        // rollback the tran when method returns
        getEntityContext().setRollbackOnly();
        throw new java.rmi.EJBException(
          "Exception occurred in ejbStore",
          new RetryableConnectionException(sqlX));
      }
      else
      {
        // handle some other problem
      }
    }
    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
      }
    }
  }
}

In mySessionBMTMethod() of the previous example:

  • The session bean first retrieves a UserTransaction object from the session context and then begins a global transaction.
  • Next, it calls a method on the entity bean, which calls the ejbLoad() method. If ejbLoad() runs successfully, the client then commits the transaction, causing the ejbStore() method to be called.
  • In ejbStore(), the entity bean gets a connection and writes its state to the database; if the connection retrieved is stale, the transaction is marked rollbackOnly and a new EJBException that wraps the RetryableConnectionException is thrown. That exception is then caught by the client, which cleans up the JDBC resources, rolls back the transaction, and calls causedByStaleConnection(), which determines if a stale connection exception is buried somewhere in the exception.

  • If the method returns true, the retry flag is set and the transaction is retried; otherwise, the exception is re-issued to the caller.

  • The causedByStaleConnection() method looks through the chain of detail attributes to find the original exception. ultiple wrapping of exceptions can occur by the time the exception finally gets back to the client, so the method keeps searching until it encounters stale connection exception and true is returned; otherwise, there is no stale connection exception in the list and false is returned.
  • If we are talking to a CMP bean instead of to a BMP bean, the session bean is the same. The CMP bean ejbStore() method would most likely be empty, and the container after calling it would persist the bean with generated code.

  • If a stale connection exception occurs during persistence, it is wrapped with a remote exception and returned to the caller. The causedByStaleConnection() method would again look through the exception chain and find the root exception, which would be stale connection exception.

Objects operating in a local transaction context

When a database operation occurs outside of a global transaction context, a local transaction is implicitly begun by the container. This includes servlets or JSPs that do not begin transactions with the UserTransaction interface, as well as enterprise beans running in unspecified transaction contexts. As with global transactions, we must roll back the local transaction before the operation is retried. In these cases, the local transaction containment usually ends when the business method ends. The one exception is if we are using activity sessions. In this case the activity session must end before attempting to get a new connection.

When the local transaction occurs in an enterprise bean running in an unspecified transaction context, the enterprise bean client object, outside of the local transaction containment, could use the method described in the previous bullet to retry the transaction. However, when the local transaction containment takes place as part of a servlet or JSP file, there is no client object available to retry the operation. For this reason, IBM recommends to avoid database operations in servlets and JSP files unless they are a part of a user transaction.


Stale Connection on Linux systems

We might must set a loopback to access DB2 databases from the application server on a Linux platform.

A Linux semaphore issue can interfere with JDBC access to your DB2 database in either of these configurations:

The issue often triggers the JVM logs to display the DB2 stale connection exception SQL1224. Because the SQL exception code can vary, however, check the DB2 trace log when we encounter a stale connection. If we see the following error data, the Linux semaphore behavior is the problem:

To work around the problem, set the loopback for our database. For example, if the database name is WAS, host name is LHOST, and database service port number is 50000, issue the following commands from the DB2 command-line window:


Example: Handling servlet JDBC connection exceptions

The following code sample demonstrates how to set transaction management and connection management properties, such as operation retries, to address stale connection exceptions within a servlet JDBC transaction.

This example code performs the following actions:

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
// Import JDBC packages and naming service packages.
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.transaction.*;
import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
import com.ibm.websphere.rsadapter.WSCallHelper;

public class EmployeeListTran extends HttpServlet {
    private static DataSource ds   = null;   
    private UserTransaction ut = null;
    private static String title    = "Employee List";    

// ****************************************************************
// * Initialize servlet when it is first loaded.                  *
// * Get information from the properties file, and look up the    *
// * DataSource object from JNDI to improve performance of the    *
// * the servlet's service methods.                               *
// ****************************************************************
    public void init(ServletConfig config)
    throws ServletException
    {
        super.init(config);
        getDS();
    }

// ****************************************************************
// * Perform the JNDI lookup for the DataSource and              *
// * User Transaction objects.                                    *
// * This method is invoked from init(), and from the service     *
// * method of the DataSource is null                             *
// ****************************************************************
    private void getDS() {
        try {
            Hashtable parms = new Hashtable();
            parms.put(Context.INITIAL_CONTEXT_FACTORY, 
 com.ibm.websphere.naming.WsnInitialContextFactory);
            InitialContext ctx = new InitialContext(parms);
            // Perform a naming service lookup to get the DataSource object.
            ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
            ut = (UserTransaction) ctx.lookup("java:comp/UserTransaction");
        } catch (Exception e) {
            System.out.println("Naming service exception:" + e.getMessage());
            e.printStackTrace();
        }
    }

// ****************************************************************
// * Respond to user GET request                                  *
// ****************************************************************
    public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
    {
        Connection conn  = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector employeeList = new Vector();
        // Set retryCount to the number of times we would like to retry after a
        // stale connection exception
        int retryCount = 5;      
        // If the Database code processes successfully, we will set error = false
        boolean error = true;
        do
        {
            try
            {          
                //Start a new Transaction
                ut.begin();
                // Get a Connection object conn using the DataSource factory.
                conn = ds.getConnection();
                // Run DB query using standard JDBC coding.
                stmt = conn.createStatement();
                String query   = "Select FirstNme, MidInit, LastName" + 
                                     "from Employee ORDER BY LastName";
                rs   = stmt.executeQuery(query);
                while (rs.next())
                {
                    employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1)" +     " + rs.getString(2));
                }                           
                //Set error to false to indicate successful completion of the database work
                error=false;                    
            }
            catch (SQLException sqlX)
            {
              // Determine if the connection request timed out.
              // This code works regardless of which error detection
              // model is used. If exception mapping is enabled, then
              // we need to look for ConnectionWaitTimeoutException.
              // If exception checking is enabled, then look for
              // SQLTransientConnectionException with a chained
              // ConnectionWaitTimeoutException.

              if ( sqlX instanceof ConnectionWaitTimeoutException
                || sqlX instanceof SQLTransientConnectionException
                   && sqlX.getCause() instanceof ConnectionWaitTimeoutException)
              {
                // This exception is thrown if a connection can not be obtained from the
                // pool within a configurable amount of time.  Frequent occurrences of
                // this exception indicate an incorrectly tuned connection pool

                System.out.println("Connection Wait Timeout Exception during get connection or 
process SQL:" + c.getMessage());  
     
                //In general, we do not want to retry after this exception, so set retry count to 0
                //and roll back the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    //Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only!" + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    //Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    //Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only!" + sye.getMessage());
                }
                retryCount=0;   
              }
              else if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
              {
                // This exception indicates that the connection to the database is no longer valid.
                //Roll back the transaction, then retry several times to attempt to obtain a valid  
                //connection, display an error message if the connection still can not be obtained.

                System.out.println("Connection is stale:" + sc.getMessage());
                
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    //Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only!" + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    //Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    //Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only!" + sye.getMessage());
                }
                if (--retryCount == 0)
                {
                    System.out.println("Five stale connection exceptions, displaying error page.");
                }
              } 
              else
              {
                System.out.println("SQL Exception during get connection or process SQL: " + sq.getMessage());

                //In general, we do not want to retry after this exception, so set retry count to 0
                //and rollback the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    //Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only!" + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    //Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    //Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only!" + sye.getMessage());
                }
                retryCount=0;
              } 
            }
            catch (NotSupportedException nse)
            {
                //Thrown by UserTransaction begin method if the thread is already associated with a
                //transaction and the Transaction Manager implementation does not support nested  
                //transactions.
                System.out.println("NotSupportedException on User Transaction begin:" + nse.getMessage());
            } 
            catch (SystemException se)
            {
                //Thrown if the transaction manager encounters an unexpected error condition
                System.out.println("SystemException in User Transaction:" +se.getMessage());
            } 
            catch (Exception e)
            {
                System.out.println("Exception in get connection or process SQL:" + e.getMessage());
                //In general, we do not want to retry after this exception, so set retry count to 5
                //and roll back the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    //Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only!" + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    //Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    //Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only!" + sye.getMessage());
                }
                retryCount=0;
            } 
            finally
            {
                // Always close the connection in a finally statement to ensure proper
                // closure in all cases. Closing the connection does not close and 
                // actual connection, but releases it back to the pool for reuse.

                if (rs != null)
                {
                   try
                   {
                      rs.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close Resultset Exception:" + e.getMessage());
                   }
                }
                if (stmt != null)
                {
                   try
                   {
                      stmt.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close Statement Exception:" + e.getMessage());
                   }
                }
                if (conn != null)
                {
                   try
                   {
                      conn.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close connection exception:" + e.getMessage());
                   }
                }
                try
                {
                    ut.commit();
                } 
                catch (RollbackException re)
                {
                    //Thrown to indicate that the transaction has been rolled back rather than committed.
                    System.out.println("User Transaction Rolled back!" + re.getMessage());
                } 
                catch (SecurityException se)
                {
                    //Thrown to indicate that the thread is not allowed to commit the transaction.
                    System.out.println("Security Exception thrown on transaction commit:" + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    //Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception thrown on transaction commit:" + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    //Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception thrown on transaction commit:" + sye.getMessage());
                } 
                catch (Exception e)
                {
                    System.out.println("Exception thrown on transaction commit:" + e.getMessage());
                }
            }
        }
        while ( error==true && retryCount > 0 );

        // Prepare and return HTML response, prevent dynamic content from being cached
        // on browsers.
        res.setContentType("text/html");
        res.setHeader("Pragma", "no-cache");
        res.setHeader("Cache-Control", "no-cache");
        res.setDateHeader("Expires", 0);
        try
        {
            ServletOutputStream out = res.getOutputStream();
            out.println("<HTML>");
            out.println("<HEAD><TITLE>" + title + "</TITLE></HEAD>");
            out.println("<BODY>");                  
            if (error==true)
            {
                out.println("<H1>There was an error processing this request.</H1>" +
                  "Please try the request again, or contact" +
                   "the <a href='mailto:sysadmin@my.com'>System Administrator</a>");
            } 
            else if (employeeList.isEmpty())
            {
               out.println("<H1>Employee List is Empty</H1>");
            } 
            else
            {
               out.println("<H1>Employee List </H1>");
               for (int i = 0; i < employeeList.size(); i++)
               {
                  out.println(employeeList.elementAt(i) + "<BR>"); 
               }
            }
            out.println("</BODY></HTML>");
            out.close();
        } 
        catch (IOException e)
        {
           System.out.println("HTML response exception:" + e.getMessage());
        }
    }
}


Example: Handling connection exceptions for session beans in container-managed database transactions

The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
import com.ibm.websphere.rsadapter.WSCallHelper;

/*************************************************************************************
* This bean is designed to demonstrate Database Connections in a                      
* Container Managed Transaction Session Bean.  Its transaction attribute               *
* should be set to TX_REQUIRED or TX_REQUIRES_NEW.                                     *
**************************************************************************************
*/
public class ShowEmployeesCMTBean implements SessionBean {
		 private javax.ejb.SessionContext mySessionCtx = null;
		 final static long serialVersionUID = 3206093459760846163L;
		 
		 private javax.sql.DataSource ds;

//************************************************************************************
//* ejbActivate calls the getDS method, which does the JNDI lookup for the DataSource.
//* Because the DataSource lookup is in a separate method, we can also invoke it from 
//* the getEmployees method in the case where the DataSource field is null.		 		 
//************************************************************************************
public void ejbActivate() throws java.rmi.EJBException {
		 getDS();
}
/**
 * ejbCreate method
 * @exception javax.ejb.CreateException
 * @exception java.rmi.EJBException
 */
public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {}
/**
 * ejbPassivate method 
 * @exception java.rmi.EJBException
 */
public void ejbPassivate() throws java.rmi.EJBException {}
/**
 * ejbRemove method 
 * @exception java.rmi.EJBException 
 */
public void ejbRemove() throws java.rmi.EJBException {}

//************************************************************************************
//* The getEmployees method runs the database query to retrieve the employees.
//* The getDS method is only called if the DataSource variable is null.	
//* Because this session bean uses Container Managed Transactions, it cannot retry the
//* transaction on a StaleConnectionException.  However, it can throw an exception to
//* its client indicating that the operation is retriable.
//************************************************************************************

public Vector getEmployees() throws ConnectionWaitTimeoutException, SQLException,
       RetryableConnectionException
{
   Connection conn  = null;
   Statement stmt = null;
   ResultSet rs = null;
   Vector employeeList = new Vector();

   if (ds == null) getDS();
   		 
   try
   {
      // Get a Connection object conn using the DataSource factory.
      conn = ds.getConnection();
      // Run DB query using standard JDBC coding.
      stmt = conn.createStatement();
      String query   = "Select FirstNme, MidInit, LastName" + 
                       "from Employee ORDER BY LastName;"
      rs   = stmt.executeQuery(query);
      while (rs.next())
      {
         employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1)" + " + rs.getString(2));
      }                           
   } 
   catch (SQLException sqlX)
   {
     // Determine if the connection is stale
     if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
     {
       // This exception indicates that the connection to the database is no longer valid.
       // Roll back the transaction, and throw an exception to the client indicating they
       // can retry the transaction if desired.

       System.out.println("Connection is stale:" + sqlX.getMessage());
       System.out.println("Rolling back transaction and throwing  RetryableConnectionException");

       mySessionCtx.setRollbackOnly();
       throw new RetryableConnectionException(sqlX.toString());
     }
     // Determine if the connection request timed out.
     else if ( sqlX instanceof ConnectionWaitTimeoutException
            || sqlX instanceof SQLTransientConnectionException
               && sqlX.getCause() instanceof ConnectionWaitTimeoutException)
     {
       // This exception is thrown if a connection can not be obtained from the
       // pool within a configurable amount of time.  Frequent occurrences of
       // this exception indicate an incorrectly tuned connection pool

       System.out.println("Connection Wait Timeout Exception during get connection or process SQL:" +
       sqlX.getMessage());
       throw sqlX instanceof ConnectionWaitTimeoutException ?
             sqlX :
             (ConnectionWaitTimeoutException) sqlX.getCause();
     } 
     else
     {
       //Throwing a remote exception will automatically roll back the container managed 
       //transaction

       System.out.println("SQL Exception during get connection or process SQL:" +
		 		 		 		 sqlX.getMessage());
       throw sqlX;
     } 
   }
   finally
   {
     // Always close the connection in a finally statement to ensure proper 
     // closure in all cases. Closing the connection does not close and 
     // actual connection, but releases it back to the pool for reuse.

     if (rs != null)
     {
       try
       {
         rs.close();
       } 
       catch (Exception e)
       {
         System.out.println("Close Resultset Exception:" + 
                       e.getMessage());
       }
     }
     if (stmt != null)
     {
       try
       {
         stmt.close();
       } 
       catch (Exception e)
       {
         System.out.println("Close Statement Exception:" + 
                       e.getMessage());
       }
     }
     if (conn != null)
     {
       try
       {
         conn.close();
       } 
       catch (Exception e)
       {
         System.out.println("Close connection exception:" + e.getMessage());
       }
     }
   }
   return employeeList;
}

/**
 * getSessionContext method
 * @return javax.ejb.SessionContext
 */
public javax.ejb.SessionContext getSessionContext() {
		 return mySessionCtx;
}
//************************************************************************************
//* The getDS method performs the JNDI lookup for the data source.	
//* This method is called from ejbActivate, and from getEmployees if the data source	  
//* object is null.	
//************************************************************************************

private void getDS() {
		 try {
		 		 Hashtable parms = new Hashtable();
		 		 parms.put(Context.INITIAL_CONTEXT_FACTORY, 
		 		 		 		 com.ibm.websphere.naming.WsnInitialContextFactory);
		 		 InitialContext ctx = new InitialContext(parms);
		 		 // Perform a naming service lookup to get the DataSource object.
		 		 ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
		 } 
       catch (Exception e) {
		 		 System.out.println("Naming service exception:" + e.getMessage());
		 		 e.printStackTrace();
		 }
}
/**
 * setSessionContext method 
 * @param ctx javax.ejb.SessionContext
 * @exception java.rmi.EJBException
 */
public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException {
		 mySessionCtx = ctx;
}
}

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is a Home interface for the Session Bean
 */
public interface ShowEmployeesCMTHome extends javax.ejb.EJBHome {

/**
 * create method for a session bean
* @return WebSphereSamples.ConnPool.ShowEmployeesCMT
 * @exception javax.ejb.CreateException
 * @exception java.rmi.RemoteException
 */
WebSphereSamples.ConnPool.ShowEmployeesCMT create() throws javax.ejb.CreateException,
   java.rmi.RemoteException;
}

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is an Enterprise Java Bean Remote Interface
 */
public interface ShowEmployeesCMT extends javax.ejb.EJBObject {

/**
 * 
 * @return java.util.Vector
 */
java.util.Vector getEmployees() throws java.sql.SQLException, java.rmi.RemoteException,
 ConnectionWaitTimeoutException, WebSphereSamples.ConnPool.RetryableConnectionException;
}
//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * Exception indicating that the operation can be retried
 * Creation date: (4/2/2001 10:48:08 AM)
 * @author: Administrator
 */
public class RetryableConnectionException extends Exception {
/**
 * RetryableConnectionException constructor.
 */
public RetryableConnectionException() {
		 super();
}
/**
 * RetryableConnectionException constructor.
 * @param s java.lang.String
 */
public RetryableConnectionException(String s) {
		 super(s);
}
}


Example: Handling connection exceptions for session beans in bean-managed database transactions

The following code sample demonstrates your options for addressing stale connection exceptions. We can set different transaction management and connection management parameters, such as the number of operation retries, and the connection timeout interval.

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import javax.transaction.*;
import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
import com.ibm.websphere.rsadapter.WSCallHelper;

/**********************************************************************************
* This bean is designed to demonstrate Database Connections in a                  *
* Bean-Managed Transaction Session Bean.  Its transaction attribute               *
* should be set to TX_BEANMANAGED.                                                 
**********************************************************************************/
public class ShowEmployeesBMTBean implements SessionBean {
		 private javax.ejb.SessionContext mySessionCtx = null;
		 final static long serialVersionUID = 3206093459760846163L;

		 private javax.sql.DataSource ds;

		 private javax.transaction.UserTransaction userTran;

//************************************************************************************
//* ejbActivate calls the getDS method, which makes the JNDI lookup for the DataSource
//* Because the DataSource lookup is in a separate method, we can also invoke it from  
//* the getEmployees method in the case where the DataSource field is null.		 		 
//************************************************************************************
public void ejbActivate() throws java.rmi.EJBException {
		 getDS();
}
/**
 * ejbCreate method 
 * @exception javax.ejb.CreateException
 * @exception java.rmi.EJBException
 */
public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {}
/**
 * ejbPassivate method 
 * @exception java.rmi.EJBException
 */
public void ejbPassivate() throws java.rmi.EJBException {}
/**
 * ejbRemove method 
 * @exception java.rmi.EJBException
 */
public void ejbRemove() throws java.rmi.EJBException {}

//************************************************************************************
//* The getEmployees method runs the database query to retrieve the employees.	    
//* The getDS method is only called if the DataSource or userTran variables are null.
//* If a stale connection occurs, the bean retries the transaction 5 times,		 
//* then throws an EJBException.
//************************************************************************************

public Vector getEmployees() throws EJBException {
   Connection conn  = null;
   Statement stmt = null;
   ResultSet rs = null;
   Vector employeeList = new Vector();

   // Set retryCount to the number of times we would like to retry after a
   // stale connection
   int retryCount = 5;  
    
   // If the Database code processes successfully, we will set error = false
   boolean error = true;
   
   if (ds == null || userTran == null) getDS();
   do
   {		 		 
         try
         { 
            //try/catch block for UserTransaction work
            //Begin the transaction
            userTran.begin();
            try
            { 
               //try/catch block for database work
               //Get a Connection object conn using the DataSource factory.
               conn = ds.getConnection();
               // Run DB query using standard JDBC coding.
               stmt = conn.createStatement();
               String query   = "Select FirstNme, MidInit, LastName" + 
		 		 		     "from Employee ORDER BY LastName";
               rs   = stmt.executeQuery(query);
               while (rs.next())
               {
                  employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1) +" " + rs.getString(2));
               }
               //Set error to false, as all database operations are successfully completed
               error = false;
            } 
            catch (SQLException sqlX)
            {
              if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
              {
                // This exception indicates that the connection to the database is no longer valid.
                // Rollback the transaction, and throw an exception to the client indicating they
                // can retry the transaction if desired.

                System.out.println("Stale connection:" +
                se.getMessage());
                userTran.rollback();
                if (--retryCount == 0)
                {
                  //If we have already retried the requested number of times, throw an EJBException.
                  throw new EJBException("Transaction Failure:" + sqlX.toString());
                } 
                else
                {
                  System.out.println("Retrying transaction, retryCount =" +          
                         retryCount);
                }
              } 
              else if (sqlX instanceof ConnectionWaitTimeoutException
                    || sqlX instanceof SQLTransientConnectionException
                       && sqlX.getCause() instanceof ConnectionWaitTimeoutException)
              {
                // This exception is thrown if a connection can not be obtained from the
                // pool within a configurable amount of time.  Frequent occurrences of
                // this exception indicate an incorrectly tuned connection pool

                System.out.println("Connection request timed out:" + 
                sqlX.getMessage());
                userTran.rollback();
                throw new EJBException("Transaction failure:" + sqlX.getMessage());
              } 
              else
              {                   
		  	  // This catch handles all other SQL Exceptions
                System.out.println("SQL Exception during get connection or process SQL:" +
		 		 		 		 sqlX.getMessage());
                userTran.rollback();
                throw new EJBException("Transaction failure:" + sqlX.getMessage());
              } 
            finally
            {
              // Always close the connection in a finally statement to ensure proper 
              // closure in all cases. Closing the connection does not close and 
              // actual connection, but releases it back to the pool for reuse.

              if (rs != null) {
               try {
                  rs.close();
               } 
               catch (Exception e) {
                  System.out.println("Close Resultset Exception:" + e.getMessage());
               }
            }
            if (stmt != null) {
               try {
                  stmt.close();
 } 
            catch (Exception e) {
               System.out.println("Close Statement Exception:" + e.getMessage());
            }
         }         
         if (conn != null) {
            try {
               conn.close();
            } 
            catch (Exception e) {
               System.out.println("Close connection exception:" + e.getMessage());
            }
         }
      }		   		 
      if (!error) {
         //Database work completed successfully, commit the transaction
         userTran.commit();
      }
      //Catch UserTransaction exceptions
      } 
      catch (NotSupportedException nse) {

//Thrown by UserTransaction begin method if the thread is already associated with a 
//transaction and the Transaction Manager implementation does not support nested transactions.
 System.out.println("NotSupportedException on User Transaction begin:" +
                                 nse.getMessage());
         throw new EJBException("Transaction failure:" + nse.getMessage());
      } 
      catch (RollbackException re) {
//Thrown to indicate that the transaction has been rolled back rather than committed.
         System.out.println("User Transaction Rolled back!" + re.getMessage());
         throw new EJBException("Transaction failure:" + re.getMessage());
      } 
      catch (SystemException se) {
		   //Thrown if the transaction manager encounters an unexpected error condition
         System.out.println("SystemException in User Transaction:" + se.getMessage());
         throw new EJBException("Transaction failure:" + se.getMessage());
      } 
      catch (Exception e) {
         //Handle any generic or unexpected Exceptions
         System.out.println("Exception in User Transaction:" + e.getMessage());
         throw new EJBException("Transaction failure:" + e.getMessage());		 		 
      }
   } 
   while (error);
   return employeeList;
}
/**
 * getSessionContext method comment
 * @return javax.ejb.SessionContext
 */
public javax.ejb.SessionContext getSessionContext() {
		 return mySessionCtx;
}

//************************************************************************************
//* The getDS method performs the JNDI lookup for the DataSource.		 		 		 
//* This method is called from ejbActivate, and from getEmployees if the DataSource		 
//* object is null.		 		 		 		 		 		 		 		 		 
//************************************************************************************
private void getDS() {
   try {
      Hashtable parms = new Hashtable();
parms.put(Context.INITIAL_CONTEXT_FACTORY, 
         com.ibm.websphere.naming.WsnInitialContextFactory);
      InitialContext ctx = new InitialContext(parms);

      // Perform a naming service lookup to get the DataSource object.
      ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
      //Create the UserTransaction object
      userTran = mySessionCtx.getUserTransaction();
   } 
   catch (Exception e) {
      System.out.println("Naming service exception:" + e.getMessage());
      e.printStackTrace();
}
}
/**
 * setSessionContext method
 * @param ctx javax.ejb.SessionContext
 * @exception java.rmi.EJBException
 */
public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException {
		 mySessionCtx = ctx;
}
}
//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is a Home interface for the Session Bean
 */
public interface ShowEmployeesBMTHome extends javax.ejb.EJBHome {

/**
 * create method for a session bean
 * @return WebSphereSamples.ConnPool.ShowEmployeesBMT
 * @exception javax.ejb.CreateException
 * @exception java.rmi.RemoteException
 */
WebSphereSamples.ConnPool.ShowEmployeesBMT create() throws javax.ejb.CreateException,
    java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is an Enterprise Java Bean Remote Interface
 */
public interface ShowEmployeesBMT extends javax.ejb.EJBObject {

/**
 * 
 * @return java.util.Vector
 */
java.util.Vector getEmployees() throws java.rmi.RemoteException, javax.ejb.EJBException;
}


Example: Handling connection exceptions for BMP beans in container-managed database transactions

The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2005,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

import java.util.*;
import javax.ejb.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import com.ibm.websphere.rsadapter.WSCallHelper;

/**
 * This is an Entity Bean class with five BMP fields
 * String firstName, String lastName, String middleInit
 * String empNo, int edLevel
 */
public class EmployeeBMPBean implements EntityBean {
       private javax.ejb.EntityContext entityContext = null;
       final static long serialVersionUID = 3206093459760846163L;

       private java.lang.String firstName;
       private java.lang.String lastName;
       private String middleInit;
       private javax.sql.DataSource ds;
       private java.lang.String empNo;
       private int edLevel;
/**
 * ejbActivate method 
 * ejbActivate calls getDS(), which performs the 
 * JNDI lookup for the datasource.
 */
public void ejbActivate() {
       getDS();
}
/**
 * ejbCreate method for a BMP entity bean
 * @return WebSphereSamples.ConnPool.EmployeeBMPKey
 * @param key WebSphereSamples.ConnPool.EmployeeBMPKey
 * @exception javax.ejb.CreateException 
 */
public WebSphereSamples.ConnPool.EmployeeBMPKey ejbCreate(String empNo, 
String firstName, String lastName, String middleInit, int edLevel) throws 
javax.ejb.CreateException {

   Connection conn = null;
   PreparedStatement ps = null;
       
   if (ds == null) getDS();
              
   this.empNo = empNo;
   this.firstName = firstName;
   this.lastName = lastName;
   this.middleInit = middleInit;
   this.edLevel = edLevel;

   String sql = "insert into Employee (empNo, firstnme, midinit, lastname,
        edlevel) values (?,?,?,?,?)";
              
   try {
      conn = ds.getConnection();
      ps = conn.prepareStatement(sql);                     
      ps.setString(1, empNo);
      ps.setString(2, firstName);
      ps.setString(3, middleInit);
      ps.setString(4, lastName);
      ps.setInt(5, edLevel);
              
 if (ps.executeUpdate() != 1){
         System.out.println("ejbCreate Failed to add user.");
         throw new CreateException("Failed to add user.");
      }
   } 
   catch (SQLException se)
   {
      if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
      {
         // This exception indicates that the connection to the database is no longer valid.
         // Rollback the transaction, and throw an exception to the client indicating they
         // can retry the transaction if desired.

         System.out.println("Connection is stale:" +  se.getMessage());
         throw new CreateException(se.getMessage());
      } 
      else
      {
         System.out.println("SQL Exception during get connection or process SQL:" +
                          se.getMessage());
         throw new CreateException(se.getMessage());
      } 
   }
   finally
   {
      // Always close the connection in a finally statement to ensure proper 
      // closure in all cases. Closing the connection does not close an
      // actual connection, but releases it back to the pool for reuse.
      if (ps != null)
      {
         try
         {
            ps.close();
         } 
         catch (Exception e)
         {
            System.out.println("Close Statement Exception:" + e.getMessage());
         }
      }
      if (conn != null)
      {
         try
         {
            conn.close();
         } 
         catch (Exception e)
         {
            System.out.println("Close connection exception:" + e.getMessage());
         }
      }
   }
   return new EmployeeBMPKey(this.empNo);
}
/**
 * ejbFindByPrimaryKey method
 * @return WebSphereSamples.ConnPool.EmployeeBMPKey
 * @param primaryKey WebSphereSamples.ConnPool.EmployeeBMPKey
 * @exception javax.ejb.FinderException
 */
public WebSphereSamples.ConnPool.EmployeeBMPKey
     ejbFindByPrimaryKey(WebSphereSamples.ConnPool.EmployeeBMPKey primaryKey)
                 javax.ejb.FinderException {
       loadByEmpNo(primaryKey.empNo);
       return primaryKey;
}
/**
 * ejbLoad method
 */
public void ejbLoad() {
       try {
              EmployeeBMPKey pk = (EmployeeBMPKey) entityContext.getPrimaryKey();
              loadByEmpNo(pk.empNo);
       } catch (FinderException fe) {
              throw new EJBException("Cannot load Employee state from database.");
       }       
}
/**
 * ejbPassivate method
 */
public void ejbPassivate() {}
/**
 * ejbPostCreate method for a BMP entity bean
 * @param key WebSphereSamples.ConnPool.EmployeeBMPKey
 */
public void ejbPostCreate(String empNo, String firstName, String lastName, String middleInit,
    int edLevel) {}
/**
 * ejbRemove method
 * @exception javax.ejb.RemoveException
 */
public void ejbRemove() throws javax.ejb.RemoveException
{
       
   if (ds == null) 
      GetDS();

   String sql = "delete from Employee where empNo=?";
   Connection con = null;
   PreparedStatement ps = null;
   try
   {                     
      con = ds.getConnection();   
      ps = con.prepareStatement(sql);   
      ps.setString(1, empNo);
      if (ps.executeUpdate() != 1)
      {
         throw new EJBException("Cannot remove employee:" + empNo);
      }
   } 
   catch (SQLException se)
   {
      if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
      {
         // This exception indicates that the connection to the database is no longer valid.
         // Rollback the transaction, and throw an exception to the client indicating they
         // can retry the transaction if desired.

         System.out.println("Connection is stale:" + se.getMessage());
         throw new EJBException(se.getMessage());
      } 
      else
      {
         System.out.println("SQL Exception during get connection or process SQL:" +
                          se.getMessage());
         throw new EJBException(se.getMessage());   
      } 
   }
   finally
   {
      // Always close the connection in a finally statement to ensure proper 
      // closure in all cases. Closing the connection does not close an
      // actual connection, but releases it back to the pool for reuse.
      if (ps != null)
      {
         try
         {
            ps.close();
         } 
         catch (Exception e)
         {
            System.out.println("Close Statement Exception:" + e.getMessage());
         }
      }
      if (con != null)
      {
         try
         {
            con.close();
         } 
         catch (Exception e)
         {
            System.out.println("Close connection exception:" + e.getMessage());
         }
      }
   }
}
/**
 * Get the employee's edLevel
 * Creation date: (4/20/2001 3:46:22 PM)
 * @return int
 */
public int getEdLevel() {
       return edLevel;
}
/**
 * getEntityContext method
 * @return javax.ejb.EntityContext
 */
public javax.ejb.EntityContext getEntityContext() {
       return entityContext;
}
/**
 * Get the employee's first name
 * Creation date: (4/19/2001 1:34:47 PM)
 * @return java.lang.String
 */
public java.lang.String getFirstName() {
       return firstName;
}
/**
 * Get the employee's last name
 * Creation date: (4/19/2001 1:35:41 PM)
 * @return java.lang.String
 */
public java.lang.String getLastName() {
       return lastName;
}
/**
* get the employee's middle initial
 * Creation date: (4/19/2001 1:36:15 PM)
 * @return char
 */
public String getMiddleInit() {
       return middleInit;
}
/**
 * Lookup the DataSource from JNDI
 * Creation date: (4/19/2001 3:28:15 PM)
 */
private void getDS() {
       try {
              Hashtable parms = new Hashtable();
              parms.put(Context.INITIAL_CONTEXT_FACTORY, 
                            com.ibm.websphere.naming.WsnInitialContextFactory);
              InitialContext ctx = new InitialContext(parms);
              // Perform a naming service lookup to get the DataSource object.
              ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
       } 
       catch (Exception e) {
              System.out.println("Naming service exception:" + e.getMessage());
              e.printStackTrace();
       }
}
/**
 * Load the employee from the database
 * Creation date: (4/19/2001 3:44:07 PM)
 * @param empNo java.lang.String
 */
private void loadByEmpNo(String empNoKey) throws javax.ejb.FinderException
{
       String sql = "select empno, firstnme, midinit, lastname, edLevel from employee where empno = ?";
       Connection conn = null;
       PreparedStatement ps = null;
       ResultSet rs = null;

       if (ds == null) getDS();
       
       try
       {
              // Get a Connection object conn using the DataSource factory.
              conn = ds.getConnection();
              // Run DB query using standard JDBC coding.
              ps = conn.prepareStatement(sql);
              ps.setString(1, empNoKey);
              rs = ps.executeQuery();
              if (rs.next())
              {
                     empNo= rs.getString(1);
                     firstName=rs.getString(2);
                     middleInit=rs.getString(3);
                     lastName=rs.getString(4); 
                     edLevel=rs.getInt(5);
              } 
              else
              {
                throw new ObjectNotFoundException("Cannot find employee number" +
                                                   empNoKey);
              }
       } 
       catch (SQLException se)
       {
         if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
         {
           // This exception indicates that the connection to the database is no longer valid.
           // Roll back the transaction, and throw an exception to the client indicating they
           // can retry the transaction if desired.

           System.out.println("Connection is stale:" + se.getMessage());
           throw new FinderException(se.getMessage());
         } 
         else
         {
           System.out.println("SQL Exception during get connection or process SQL:" +
                               se.getMessage());
           throw new FinderException(se.getMessage());
         } 
       }
       finally
       {
         // Always close the connection in a finally statement to ensure 
         // proper closure in all cases. Closing the connection does not 
         // close an actual connection, but releases it back to the pool 
         // for reuse.
         if (rs != null)
         {
            try
            {
               rs.close();
            } 
            catch (Exception e)
            {
               System.out.println("Close Resultset Exception:" + e.getMessage());
            }
         }
         if (ps != null)
         {
            try
            {
               ps.close();
            } 
            catch (Exception e)
            {
               System.out.println("Close Statement Exception:" + e.getMessage());
            }
         }
         if (conn != null)
         {
            try
            {
               conn.close();
            } 
            catch (Exception e) 
            {
               System.out.println("Close connection exception:" + e.getMessage());
            }
         }
       }      
}
/**
 * set the employee's education level
 * Creation date: (4/20/2001 3:46:22 PM)
 * @param newEdLevel int
 */
public void setEdLevel(int newEdLevel) {
       edLevel = newEdLevel;
}
/**
 * setEntityContext method
 * @param ctx javax.ejb.EntityContext
 */
public void setEntityContext(javax.ejb.EntityContext ctx) {
       entityContext = ctx;
}
/**
 * set the employee's first name
 * Creation date: (4/19/2001 1:34:47 PM)
 * @param newFirstName java.lang.String
 */
public void setFirstName(java.lang.String newFirstName) {
       firstName = newFirstName;
}
/**
 * set the employee's last name
 * Creation date: (4/19/2001 1:35:41 PM)
 * @param newLastName java.lang.String
 */
public void setLastName(java.lang.String newLastName) {
       lastName = newLastName;
}
/**
 * set the employee's middle initial
 * Creation date: (4/19/2001 1:36:15 PM)
 * @param newMiddleInit char
 */
public void setMiddleInit(String newMiddleInit) {
       middleInit = newMiddleInit;
}
/**
 * unsetEntityContext method
 */
public void unsetEntityContext() {
       entityContext = null;
}
}

//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is an Enterprise Java Bean Remote Interface
 */
public interface EmployeeBMP extends javax.ejb.EJBObject {

/**
 * 
 * @return int
  */
int getEdLevel() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getFirstName() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getLastName() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getMiddleInit() throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newEdLevel int
 */
void setEdLevel(int newEdLevel) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newFirstName java.lang.String
 */
void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newLastName java.lang.String
 */
void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newMiddleInit java.lang.String
 */
void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
 * This is an Enterprise Java Bean Remote Interface
 */
public interface EmployeeBMP extends javax.ejb.EJBObject {

/**
 * 
 * @return int
  */
int getEdLevel() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getFirstName() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getLastName() throws java.rmi.RemoteException;
/**
 * 
 * @return java.lang.String
 */
java.lang.String getMiddleInit() throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newEdLevel int
 */
void setEdLevel(int newEdLevel) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newFirstName java.lang.String
 */
void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newLastName java.lang.String
 */
void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException;
/**
 * 
 * @return void
 * @param newMiddleInit java.lang.String
 */
void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
//   5630-A23, 5630-A22,
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR
//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
//   OR PERFORMANCE OF THIS SOFTWARE.
//
//===================END_PROLOG========================================

package WebSphereSamples.ConnPool;

/**
* This is a Primary Key Class for the Entity Bean
**/
public class EmployeeBMPKey implements java.io.Serializable { 
		 public String empNo;
		 final static long serialVersionUID = 3206093459760846163L;

/**
* EmployeeBMPKey() constructor 
*/
public EmployeeBMPKey()  {
}
/**
* EmployeeBMPKey(String key) constructor 
*/
public EmployeeBMPKey(String key)  {
		 empNo = key;
}
/**
* equals method
* - user must provide a proper implementation for the equal method. The generated 
*   method assumes the key is a String object.
*/
public boolean equals (Object o)  {
		 if (o instanceof EmployeeBMPKey) 
		 		 return empNo.equals(((EmployeeBMPKey)o).empNo);
		 else
		 		 return false;
}
/**
* hashCode method
* - user must provide a proper implementation for the hashCode method. The generated
*    method assumes the key is a String object.
*/
public int hashCode ()  {
		 return empNo.hashCode();


Example: Handling data access exception - ConnectionWaitTimeoutException (for the JDBC API)

This code sample demonstrates how we specify the conditions under which the application server issues the ConnectionWaitTimeoutException for a JDBC application.

In all cases in which the ConnectionWaitTimeoutException is caught, there is little that can be done to recover.

public void test1() {
		 		 java.sql.Connection conn = null;
		 		 java.sql.Statement stmt = null;
		 		 java.sql.ResultSet rs = null;

		 		 try {
		 		 		 // Look for datasource
		 		 		 java.util.Properties props = new java.util.Properties();
		 		 		 props.put(
		 		 		 		 javax.naming.Context.INITIAL_CONTEXT_FACTORY,
		 		 		 		 com.ibm.websphere.naming.WsnInitialContextFactory);
		 		 		 ic = new javax.naming.InitialContext(props);
		 		 		 javax.sql.DataSource ds1 = (javax.sql.DataSource) ic.lookup(jndiString);

		 		 		 // Get Connection.
		 		 		 conn = ds1.getConnection();
		 		 		 stmt = conn.createStatement();
		 		 		 rs = stmt.executeQuery("select * from mytable where this = 54");
		 		 }
		 		 catch (java.sql.SQLException sqlX) {
					if (sqlX instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException
					 || sqlX instanceof java.sql.SQLTransientConnectionException
					    && sqlX.getCause() instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException)
					{
		 		 		 //notify the user the system could not provide a 
		 		 		 //connection to the database.  This usually happens when the 
		 		 		 //connection pool is full and there is no connection 
		 		 		 //available for to share.
		 			}
					else
					{
		 		 		 // handle other database problems.
					}
		 		 }
		 		 finally {
		 		 		 if (rs != null)
		 		 		 		 try {
		 		 		 		 		 rs.close();
		 		 		 		 }
		 		 		 		 catch (java.sql.SQLException sqle1) {
		 		 		 		 }
		 		 		 if (stmt != null)
		 		 		 		 try {
		 		 		 		 		 stmt.close();
		 		 		 		 }
		 		 		 		 catch (java.sql.SQLException sqle1) {
		 		 		 		 }
		 		 		 if (conn != null)
		 		 		 		 try {
		 		 		 		 		 conn.close();
		 		 		 		 }
		 		 		 		 catch (java.sql.SQLException sqle1) {
		 		 		 		 }
		 		 }
		 }


Example: Handling data access exception - ConnectionWaitTimeoutException for Java EE Connector Architecture

This code sample demonstrates how we specify the conditions under which WAS issues the ConnectionWaitTimeout exception for a JCA application.

In all cases in which the ConnectionWaitTimeout exception is caught, there is little to do for recovery.

The following code fragment shows how to use this exception in Java EE Connector Architecture (JCA):

/**
 * This method does a simple Connection test. 
 */
public void testConnection()
   throws javax.naming.NamingException, javax.resource.ResourceException, 
       com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException {
   javax.resource.cci.ConnectionFactory factory = null;
   javax.resource.cci.Connection conn = null;
   javax.resource.cci.ConnectionMetaData metaData = null;
   try {
      // lookup the connection factory
      if (verbose) System.out.println("Look up the connection factory...");
try {
factory = 
	 (javax.resource.cci.ConnectionFactory) (new InitialContext()).lookup("java:comp/env/eis/Sample");
  }
   catch (javax.naming.NamingException ne) {
      // Connection factory cannot be looked up.
      throw ne;
   }
    // Get connection
      if (verbose) System.out.println("Get the connection...");
      conn = factory.getConnection();
      // Get ConnectionMetaData
      metaData = conn.getMetaData();
      // Print out the metadata Information.
      System.out.println("EISProductName" is + metaData.getEISProductName());
   }
   catch (com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException cwtoe) {
      // Connection Wait Timeout
      throw cwtoe;
   }
   catch (javax.resource.ResourceException re) {
      // Something wrong with connections.
      throw re;
   }
   finally {
      if (conn != null) {
         try {
            conn.close();
         }
         catch (javax.resource.ResourceException re) {
         }
      }
   }
}


Example: Handling data access exception - error mapping in DataStoreHelper

The application server provides a DataStoreHelper interface for mapping different database SQL error codes to the appropriate exceptions in the application server.

Error mapping is necessary because various database vendors can provide different SQL errors and codes that represent that same issue. For example, the stale connection exception has different codes in different databases. The DB2 SQLCODEs of 1015, 1034, 1036, and so on indicate that the connection is no longer available because of a temporary database problem. The Oracle SQLCODEs of 28, 3113, 3114, and so on, indicate the same situation.

Mapping these error codes to standard exceptions provides the consistency that makes applications portable across different installations of the application server. The following code segment illustrates how to add two error codes into the error map:
public class NewDSHelper extends GenericDataStoreHelper
{
  public NewDSHelper(java.util.Properties dataStoreHelperProperties)  
  {
    super(dataStoreHelperProperties);
    java.util.Hashtable myErrorMap = null;
    myErrorMap = new java.util.Hashtable();
    myErrorMap.put(new Integer(-803), myDuplicateKeyException.class);
    myErrorMap.put(new Integer(-1015), myStaleConnectionException.class);
    myErrorMap.put("S1000", MyTableNotFoundException.class);
    setUserDefinedMap(myErrorMap);
    ...
  }
}

A configuration option known as the Error Detection odel controls how the error map is used. At V6 and earlier, Exception apping was the only option available for the Error Detection Model. At V7 and later, another option called Exception Checking is also available. Under the Exception Mapping model, the application server consults the error map and replaces exceptions with the corresponding exception type listed in the error map. Under the Exception Checking model, the application server still consults the error map for its own purposes but does not replace exceptions. To continue to use Exception Mapping, we do not need to change anything. Exception apping is the default Error Detection Model. To use the Exception Checking Model, see the topic "Changing the Error Detection odel to use the Exception Checking Model" in the related links.


Database deadlock and foreign key conflicts

Repetition of certain SQL error messages indicates problems, such as database referential integrity violations, that we can prevent using the container managed persistence (CMP) sequence grouping feature.

Exceptions resulting from foreign key conflicts due to violations of database referential integrity

A database referential integrity (RI) policy prescribes rules for how data is written to and deleted from the database tables to maintain relational consistency. Runtime requirements for managing bean persistence, however, can cause an enterprise JavaBeans (EJB) application to violate RI rules, which can cause database exceptions.

Your EJB application is violating database RI if you see an exception message in the WebSphere Application Server trace or log file that is similar to one of the following messages (which were produced in an environment running DB2):

or

To prevent these exceptions, we must designate the order in which entity beans update relational database tables by defining sequence groups for the beans.

Exceptions resulting from deadlock caused by optimistic concurrency control schemes

Additionally, sequence grouping can minimize transaction rollback exceptions for entity beans that are configured for optimistic concurrency control. Optimistic concurrency control dictates that database locks be held for minimal amounts of time, so that a maximum number of transactions consistently have access to the data. In such a highly available database, concurrent transactions can attempt to lock the same table row and create deadlock. The resulting exceptions can generate messages similar to the following (which was produced in an environment running DB2):

Use the sequence grouping feature to order bean persistence so that database deadlock is less likely to occur.


Subtopics


Related:

  • Data sources
  • Relational resource adapters and JCA
  • Connection considerations when migrating servlets, JavaServer Pages, or enterprise session beans
  • Connection pool settings
  • Connection pool (Version 4) settings (deprecated)