Executing database queries

During execution of a collaboration, you might need to obtain information from a database, such as the relationship database. To obtain or modify information from a database, you query its tables. A query is a request, usually in the form of an SQL (Structured Query Language) statement, that you send to the database for execution. Table 37 shows the steps involved in executing a query in a database.

Note:
You can access any external database that InterChange Server Express supports through JDBC through the Oracle thin type 4 driver and a InterChange Server Express branded MS-SQL Server type 4 driver.

Table 37. Steps for executing a query
Task for executing a query For more information
1. Obtain a connection (which is a CwDBConnection object) to the database. "Obtaining a connection"
2. Through the CwDBConnection object, send queries and manage transactions in the database. "Executing the query""Managing the transaction"
3. Release the connection. "Releasing a connection"

Tip:
One possible use of database queries is to handle service calls with long latency. After the collaboration issues a service request that is expected to take a long time, the collaboration saves the execution context using a database connection and then exits. The actual response from the service call, which might occur hours or even days later, returns as a new event and triggers another collaboration, which restores the proper execution context from the database and resumes the business process execution.

Obtaining a connection

To be able to query the database, you must first obtain a connection to this database with the getDBConnection() method of the BaseCollaboration class. To identify the connection to obtain, specify the name of the connection pool that contains this connection. All connections in a particular connection pool are to the same database. The number of connections in the connection pool is determined as part of the connection pool configuration. You must determine the name of the connection pool that contains connections for the database you want to query.

Important:
Connections are opened when InterChange Server boots or dynamically, when a new connection pool is configured. Therefore, the connection pool that contains connections to the desired database must be configured before the execution of the collaboration object that requests the connection. You configure connection pools within System Manager. For more information, see the Implementation Guide for WebSphere InterChange Server.

In Figure 71, the call to getDBConnection() obtains a connection to the database that is associated with connections in the CustDBConnPool connection pool.

Figure 71. Obtaining a connection from a connection pool

CwDBConnection connection = getDBConnection("CustDBConnPool");
 

The getDBConnection() call returns a CwDBConnection object in the connection variable, which you can then use to access the database associated with the connection.

Tip:
The getDBConnection() method provides an additional form that allows you to specify the transaction programming model for the connection. For more information, see "Managing the transaction".

Executing the query

Table 38 shows the ways that you can execute SQL queries with methods of the CwDBConnection class.

Table 38. Executing SQL queries with CwDBConnection methods
Type of query Description CwDBConnection method
Static query The SQL statement is sent as text to the database. executeSQL()
Prepared query After its initial execution, the SQL statement is saved in its compiled, executable form so that subsequent executions can use this precompiled form. executePreparedSQL()
Stored procedure A user-defined procedure that contains SQL statements and conditional logic executeSQL()executePreparedSQL() executeStoredProcedure()

Executing static queries

The executeSQL() method sends a static query to the database for execution. A static query is an SQL statement sent as a string to the database, which parses the string and executes the resulting SQL statement. This section covers how to send the following kinds of SQL queries to a database with executeSQL():

Executing static queries that return data (SELECT)

The SQL statement SELECT queries one or more tables for data. To send a SELECT statement to the database for execution, specify a string representation of the SELECT as an argument to the executeSQL() method. For example, the following call to executeSQL() sends a SELECT of one column value from the customer table:

connection.executeSQL(
    "select cust_id from customer where active_status = 1");
 

Note:
In the preceding code, the connection variable is a CwDBConnection object obtained from a previous call to the getDBConnection() method (see Figure 71).

You can also send a SELECT statement that has parameters in it by using the second form of the executeSQL() method. For example, the following call to executeSQL() performs the same task as the previous example except that it passes the active status as a parameter to the SELECT statement:

Vector argValues = new Vector();
  
 String active_stat = "1";
 argValues.add( active_stat );
 connection.executeSQL(
    "select cust_id from customer where active_status = ?", argValues);
 

The SELECT statement returns data from the database tables as rows. Each row is one row from the data that matches the conditions in the WHERE clause of the SELECT. Each row contains the values for the columns that the SELECT statement specified. You can visualize the returned data as a two-dimensional array of these rows and columns.

Tip:
The syntax of the SELECT statement must be valid to the particular database you are accessing. Consult your database documentation for the exact syntax of the SELECT statement.

To access the returned data, follow these steps:

  1. Obtain one row of data.
  2. Obtain column values, one by one.

Table 39 shows the methods in the CwDBConnection class that provide access to the rows of returned query data.

Table 39.
CwDBConnection methods for row access
Row-access task CwDBConnection method
Check for existence of a row. hasMoreRows()
Obtain one row of data. nextRow()

Control the loop through the returned rows with the hasMoreRows() method. End the row loop when hasMoreRows() returns false. To obtain one row of data, use the nextRow() method. This method returns the selected column values as elements in a Java Vector object. You can then use the Enumeration class to access the column values individually. Both the Vector and Enumeration classes are in the java.util package.

Table 40 shows the Java methods for accessing the columns of a returned query row.

Table 40. Java methods for column-value access
Column-access task Java method
Determine number of columns. Vector.size()
Cast Vector to Enumeration. Vector.elements()
Check for existence of a column. Enumeration.hasMoreElements()
Obtain one column of data. Enumeration.nextElement()

Control the loop through the column values with the hasMoreElements() method. End the column loop when hasMoreElements() returns false. To obtain one column value, use the nextElement() method.

The following code sample gets an instance of the CwDBConnection class, which is a connection to a database that stores customer information. It then executes a SELECT statement that returns only one row, which contains a single column, the company name "CrossWorlds" for the customer id of 20987:

CwDBConnection connectn = null;
 Vector theRow = null;
 Enumeration theRowEnum = null;
 String theColumn1 = null;
  
 try 
    {
    // Obtain a connection to the database
    connectn = getDBConnection("sampleConnectionPoolName");
    }
  
 catch(CwDBConnectionFactoryException e) 
    {
    System.out.println(e.getMessage());
    throw e;
    }
  
 // Test for a resulting single-column, single-row, result set
 try {
    // Send the SELECT statement to the database
    connectn.executeSQL(
       "select company_name from customer where cust_id = 20987");
  
    // Loop through each row
    while(connectn.hasMoreRows())
       {
       // Obtain one row
       theRow = connectn.nextRow();
       int length = 0;
       if ((length = theRow.size())!= 1)
          {
          return methodName + "Expected result set size = 1," + 
             " Actual result state size = " + length;
          }
  
       // Get column values as an Enumeration object
       theRowEnum = theRow.elements();
  
       // Verify that column values exist
       if (theRowEnum.hasMoreElements())
          {
          // Get the column value
          theColumn1 = (String)theRowEnum.nextElement();
          if (theColumn1.equals("CrossWorlds")==false)
             {
             return "Expected result = CrossWorlds,"
                + " Resulting result = " + theColumn1;
             }
          }
       }
    }
  
 // Handle any exceptions thrown by executeSQL()
 catch(CwDBSQLException e)
    {
    System.out.println(e.getMessage());
    }
 

The following example shows a code fragment for a SELECT statement that returns multiple rows, each row containing two columns, the customer id and the associated company name:

CwDBConnection connectn = null;
 Vector theRow = null;
 Enumeration theRowEnum = null;
 Integer theColumn1 = 0;
 String theColumn2 = null;
  
 try 
    {
    // Obtain a connection to the database
    connectn = getDBConnection("sampleConnectionPoolName");
    }
  
 catch(CwDBConnectionFactoryException e) 
    {
    System.out.println(e.getMessage());
    throw e;
    }
  
 // Code fragment for multiple-row, multiple-column result set.
 // Get all rows with the specified columns, where the
 // specified condition is satisfied
 try 
    {
    connectn.executeSQL(
 "select cust_id, company_name from customer where active_status = 1");
  
    // Loop through each row
    while(connectn.hasMoreRows())
       {
       // Obtain one row
       theRow = connectn.nextRow();
  
       // Obtain column values as an Enumeration object
       theRowEnum = theRow.elements();
       int length = 0;
       if ((length = theRow.size()) != 2)
       {
          return "Expected result set size = 2," +
             " Actual result state size = " + length;
       }
       // Verify that column values exist
       if (theRowEnum.hasMoreElements())
          {
          // Get the column values
          theColumn1 =
             ((Integer)theRowEnum.nextElement()).intValue();
          theColumn2 = (String)theRowEnum.nextElement();
          }
       }
    }
 catch(CwDBSQLException e) 
    {
    System.out.println(e.getMessage());
    }
 

Note:
The SELECT statement does not modify the contents of the database. Therefore, you do not usually need to perform transaction management for SELECT statements.

Executing static queries that modify data

SQL statements that modify data in a database table include the following:

To send one of these statements as a static query to the database for execution, specify a string representation of the statement as an argument to the executeSQL() method. For example, the following call to executeSQL() sends an INSERT of one row into the abc table of the database associated with the current connection:

connection.executeSQL("insert into abc values (1, 3, 6)");
 

Note:
In the preceding code, the connection variable is a CwDBConnection object obtained from a previous call to the getDBConnection() method.

For an UPDATE or INSERT statement, you can determine the number of rows in the database table that have been modified or added with the getUpdateCount() method.

Important:
Because the INSERT, UPDATE, and DELETE statements modify the contents of the database, it is good practice to assess the need for transaction management for these statements. For more information, see "Managing the transaction".

Executing a static stored procedure

You can use the executeSQL() method to execute a stored-procedure call as long as both of the following conditions exist:

For more information, see "Executing stored procedures".

Executing prepared queries

The executePreparedSQL() method sends a prepared query to the database for execution. A prepared query is an SQL statement that is already precompiled into the executable form used by the database. The first time that executePreparedSQL() sends a query to the database, it sends the query as a string. The database receives this query, compiles it into an executable form by parsing the string, and executes the resulting SQL statement (just as it does for executeSQL()). However, the database returns this compiled form of the SQL statement to executePreparedSQL(), which stores it in memory. This compiled SQL statement is called a prepared statement.

In subsequent executions of this same query, executePreparedSQL() first checks whether a prepared statement already exists for this query. If a prepared statement does exist, executePreparedSQL() sends it to the database instead of the query string. Subsequent executions of this query are more efficient because the database does not have to parse the string and create the prepared statement.

You can send the following kinds of SQL queries to a database with executePreparedSQL():

Executing prepared queries that return data (SELECT)

If you need to execute the same SELECT statement multiple times, use executePreparedSQL() to create a precompiled version of the statement. Keep the following in mind to prepare a SELECT statement:

Executing prepared queries that modify data

If you need to execute the same INSERT, UPDATE, or DELETE statement multiple times, use executePreparedSQL() to create a precompiled version of the statement. The SQL statement that you reexecute does not need to be exactly the same in each time it executes to take advantage of the prepared statement. You can use parameters in the SQL statement to dynamically provide information to each statement execution.

The code fragment in Figure 72 inserts 50 rows into the employee table. The first time executePreparedSQL() is invoked, it sends the string version of the INSERT statement to the database, which parses it, executes it, and returns its executable form: a prepared statement. The next 49 times that this INSERT statement executes (assuming all INSERTs are successful), executePreparedSQL() recognizes that a prepared statement exists and sends this prepared statement to the database for execution.

Figure 72. Passing argument values to a prepared statement

CwDBConnection connection;
 Vector argValues = new Vector();
  
 argValues.setSize(2);
  
 int emp_id = 1;
 int emp_id = 2000;
  
 for (int = 1; i < 50; i++)
    {
    argValues.set(0, new Integer(emp_id));
    argValues.set(1, new Integer(emp_num));
  
    try 
       {
       // Send the INSERT statement to the database
       connection.executePreparedSQL(
 "insert into employee (employee_id, employee_number) values (?, ?)",
           argValues);
  
       // Increment the argument values
       emp_id++;
       emp_num++
       }
  
    catch(CwDBSQLException e) 
       {
       System.out.println(e.getMessage());
       }
    }
 

Tip:
Executing the prepared version of the INSERT statement usually improves application performance, although it does increase the application's memory footprint.

When you reexecute an SQL statement that modifies the database, you must still handle transactions according to the transaction programming model. For more information, see "Managing the transaction".

Note:
To simplify the code in Figure 72 does not include transaction management.

Executing a prepared stored procedure

You can use the executePreparedSQL() method to execute a stored-procedure call as long as both of the following conditions exist:

For more information, see "Executing stored procedures".

Executing stored procedures

A stored procedure is a user-defined procedure that contains SQL statements and conditional logic. Stored procedures are stored in a database along with the data.

Note:
When you create a new relationship, Relationship Designer creates a stored procedure to maintain each relationship table.

Table 41 shows the methods in the CwDBConnection class that call a stored procedure.

Table 41.
CwDBConnection methods for calling a stored procedure
How to call the stored procedure CwDBConnection method Use
Send to the database a CALL statement to execute the stored procedure. executeSQL()
To call a stored procedure that does not have OUT parameters and is executed only once

executePreparedSQL() To call a stored procedure that does not have OUT parameters and is executed more than once
Specify the name of the stored procedure and an array of its parameters to create a procedure call, which is sent to the database for execution. executeStoredProcedure()
To call any stored procedure, including one with OUT parameters

Note:
You can use JDBC methods to execute a stored procedure directly. However, the interface that the CwDBConnection class provides is simpler and it reuses database resources, which can increase the efficiency of execution. You can use of the methods in the CwDBConnection class to execute stored procedures.

A stored procedure can return data in the form of one or more rows. In this case, you use the same Java methods (such as hasMoreRows() and nextRow()) to access these returned rows in the query result as you do for data returned by a SELECT statement. For more information, see "Executing static queries that return data (SELECT)".

As Table 41 shows, the choice of which method to use to call a stored procedure depends on:

The following sections describe how to use the executeSQL() and executeStoredProcedure() methods to call a stored procedure.

Calling stored procedures with no OUT parameters

To call a stored procedure that does not include any OUT parameters, you can use either of the following methods of CwDBConnection:

To call a stored procedure with one of these methods, specify as an argument to the method a string representation of the CALL statement that includes the stored procedure and any arguments. In Figure 73, the call to executeSQL() sends a CALL statement to execute the setOrderCurrDate() stored procedure.

Figure 73. Calling a stored procedure with executeSQL()

connection.executeSQL("call setOrderCurrDate(345698)");
 

In Figure 73, the connection variable is a CwDBConnection object obtained from a previous call to the getDBConnection() method. You can use executeSQL() to execute the setOrderCurrDate() stored procedure because its single argument is an IN parameter; that is, the value is only sent into the stored procedure. This stored procedure does not have any OUT parameters.

You can use the form of executeSQL() or executePreparedSQL() that accepts a parameter array to pass in argument values to the stored procedure. However, you cannot use these methods to call a stored procedure that uses an OUT parameter. To execute such a stored procedure, you must use executeStoredProcedure(). For more information, see "Calling stored procedures with executeStoredProcedure()".

Note:
Use an anonymous PL/SQL block if you plan on calling Oracle stored PL/SQL objects via ODBC using the CwDBConnection .executeSQL() method. The following is an acceptable format (the stored procedure name is myproc):
connection.executeSQL("begin myproc(...); end;");
 

Calling stored procedures with executeStoredProcedure()

The executeStoredProcedure() method can execute any stored procedure, including one that uses OUT parameters. This method saves the prepared statement for the stored-procedure call, just as the executePreparedSQL() method does. Therefore, executeStoredProcedure() can improve performance of a stored-procedure call that is executed multiple times.

To call a stored procedure with the executeStoredProcedure() method, you:

  1. Specify as a String the name of the stored procedure to execute.
  2. Build a Vector parameter array of CwDBStoredProcedureParam objects, which provide parameter information: the in/out parameter type and value of each stored-procedure parameter.

A parameter is a value you can send into or out of the stored procedure. The parameter's in/out type determines how the stored procedure uses the parameter value:

A CwDBStoredProcedureParam object describes a single parameter of a stored procedure. Table 42 shows the parameter information that a CwDBStoredProcedureParam object contains as well as the methods to retrieve and set this parameter information.

Table 42. Parameter information in a CwDBStoredProcedureParam object
Parameter information CwDBStoredProcedureParam method
Parameter value getValue()
Parameter in/out type getParamType()

To pass parameters to a stored procedure with executeStoredProcedure():

  1. Create a CwDBStoredProcedureParam object to hold the parameter information.

    Use the CwDBStoredProcedureParam() constructor to create a new CwDBStoredProcedureParam object. To this constructor, pass the following parameter information to initialize the object:

  2. Repeat step 1 for each stored-procedure parameter.
  3. Create a Vector object with enough elements to hold all stored-procedure parameters.
  4. Add the initialized CwDBStoredProcedureParam object to the parameter Vector object.

    Use the addElement() or add() method of the Vector class to add the CwDBStoredProcedureParam object.

  5. Once you have created all CwDBStoredProcedureParam objects and added them to the Vector parameter array, pass this parameter array as the second argument to the executeStoredProcedure() method.

    The executeStoredProcedure() method sends the stored procedure and its parameters to the database for execution.

For example, suppose you have the get_empno() stored procedure defined in a database as follows:

create or replace procedure get_empno(emp_id IN number,
       emp_number OUT number) as
    begin
       select emp_no into emp_number
       from emp
       where emp_id = 1;
    end;
 

This get_empno() stored procedure has two parameters:

Figure 74 executes the get_empno() stored procedure with the executeStoredProcedure() method to obtain the employee number for an employee id of 65:

Figure 74. Executing the get_empno() stored procedure

CwDBConnection connectn = null;
  
 try 
    {
    // Get database connection
    connectn = getDBConnection("CustomerDBPool");
  
    // Create parameter Vector
    Vector paramData = new Vector(2);
  
    // Create IN parameter for the employee id and add to parameter
    // vector
    paramData.add(
       new CwDBStoredProcedureParam(PARAM_IN, new Integer(65)));
  
    // Create dummy argument for OUT parameter and add to parameter
    // vector
    paramData.add(
       new CwDBStoredProcedureParam(PARAM_OUT, new Integer(0));
  
    // Call the get_empno() stored procedure
    connectn.executeStoredProcedure("get_empno", paramData);
  
    // Get the result from the OUT parameter
    CwDBStoredProcedureParam outParam = 
       (CwDBStoredProcedureParam) paramData.get(1);
    int emp_number = ((Integer) outParam.getValue().Intvalue();
    }
 

Tip:
The Java Vector object is a zero-based array. In the preceding code, to access the value for this OUT parameter from the Vector parameter array, the get() call specifies an index value of 1 because this Vector array is zero-based.

A stored procedure processes its parameters as SQL data types. Because SQL and Java data types are not identical, the executeStoredProcedure() method must convert a parameter value between these two data types. For an IN parameter, executeStoredProcedure() converts the parameter value from a Java data type to its SQL data type. For an OUT parameter, executeStoredProcedure() converts the parameter value from its SQL data type to a Java data type.

The executeStoredProcedure() method uses the JDBC data type internally to hold the parameter value sent to and from the stored procedure. JDBC defines a set of generic SQL type identifiers in the java.sql.Types class. These types represent the most commonly used SQL types. JDBC also provides standard mapping from JDBC types to Java data types. For example, a JDBC INTEGER is normally mapped to a Java int type. The executeStoredProcedure() method uses the mappings shown in Table 43.

Table 43.
Mappings between Java and JDBC data types
Java data type JDBC data type
String CHAR, VARCHAR, or LONGVARCHAR
Integer, int INTEGER
Long BIGINT
Float, float REAL
Double, double DOUBLE
java.math.BigDecimal NUMERIC
Boolean, boolean BIT
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
java.sql.Clob CLOB
java.sql.Blob BLOB
byte[] BINARY, VARBINARY, or LONGVARBINARY
Array ARRAY
Struct STRUCT

Managing the transaction

A transaction is a set of operational steps that execute as a unit. All SQL statements that execute within a transaction succeed or fail as a unit. This section provides the following information about managing transactions:

Determining the transaction programming model

The grouping of the database operation execution steps into transactions is called transaction bracketing. Associated with each connection is one of the following transaction programming models:

At runtime, a collaboration object determines which transaction programming model to use for each connection it acquires. By default, a collaboration object assumes that all connections it acquires use implicit transaction bracketing as their transaction programming model. You can override the default transaction programming model in any of the ways listed in Table 44.

Table 44. Overriding the transaction programming model for a connection
Transaction programming model to override Action to take
To specify a different transaction programming model for all connections obtained by a particular collaboration object Check or uncheck the Implicit Database Transaction box on the Collaboration Properties dialog of System Manager. For more information, see the Implementation Guide for WebSphere InterChange Server.
To specify a transaction programming model for a particular connection

Provide a boolean value to indicate the desired transaction programming model (for this connection only) as the optional second argument to the getDBConnection() method.

The following getDBConnection() call specifies explicit transaction bracketing for the connection obtained from the ConnPool connection pool:

conn = getDBConnection("ConnPool",
     false);
 

You can determine the current transaction programming model that connections will use with the BaseCollaboration.implicitDBTransactionBracketing() method, which returns a boolean value indicating whether the transaction programming model is implicit transaction bracketing.

Specifying the transaction scope

The connection's transaction programming model determines how the scope of the database transaction is specified. Therefore, this section provides the following information:

Transaction scope with implicit transaction bracketing

InterChange Server handles transaction management for all collaborations. All actions in the collaboration's business process are either completed as a unit or not completed. Therefore, InterChange Server handles the business process as a whole as a single implicit transaction. If any task fails, you choose how to handle the failed collaboration through the Unresolved Flow browser.

If the connection uses implicit transaction bracketing, InterChange Server also handles transaction management for operations performed on an external database, one associated with a connection from a connection pool. When a collaboration performs database operations, these database operations are part of the collaboration's business process. InterChange Server handles these database operations as an implicit transaction, which is subtransaction of the main transaction (the collaboration's business process). This database subtransaction begins as soon as the collaboration obtains the connection. ICS implicitly ends the subtransaction when execution of the collaboration completes.

The success or failure of this database subtransaction depends on the success or failure of the main transaction, as follows:

When a collaboration invokes another collaboration directly, the first collaboration is called the parent, while the second one is called the child. When a parent collaboration calls a child collaboration, InterChange Server manages the transaction for the child collaboration separately. The success or failure of this child collaboration is independent of the success or failure of the parent collaboration. If the child collaboration fails, the parent collaboration can decide how to handle this failure. For example, it can decide it must fail as well, or it can decide to fix or ignore the situation and continue execution.

However, even though the success and failure of a child collaboration is independent of the parent collaboration, the same is not true of any implicit database transactions that the child might perform. If the child collaboration performs database operations through a database connection that uses implicit transaction bracketing, the child collaboration inherits the transaction of the parent collaboration. InterChange Server handles these database operations as a subtransaction of the parent collaboration. That is, the failure or success of the parent (or top-level) collaboration determines the final transactional state of the implicit database subtransaction in the child collaboration, as follows:

InterChange Server does not commit or roll back the subtransaction until it knows the success or failure of the parent collaboration.

With this behavior, if the child collaboration failed and the parent collaboration chose to continue execution, InterChange Server would commit the implicit database transaction of the child collaboration.

Note:
InterChange Server handles any explicit database subtransactions that the child collaboration performs and that are still active (i.e. those that have been performed through a database connection that uses explicit transaction bracketing but not been explicitly committed or rolled back in the child's collaboration template) in the same way as implicit database subtransactions.

This method of handling child transactions provides the collaboration developer with a means to perform a transactional join from child to parent without explicitly using join semantics. If instead, child database transactions were committed or rolled back at the child level, the developer could not correlate transactions that the child started (either explicitly or implicitly) with the global business process transaction that the parent started.

Note:
Transactional collaborations use this same model for their parent and child database transactions.

Transaction scope with explicit transaction bracketing

If the connection uses explicit transaction bracketing, ICS expects the collaboration template to explicitly specify the scope of each database transaction. Explicit transaction bracketing is useful if you have some database work to perform that is independent of the success or failure of the collaboration. For example, if you need to perform auditing to indicate that certain tables were accessed, this audit needs to be performed regardless of whether the table accesses were successful or not. If you contain the auditing database operations in an explicit transaction, they are executed regardless of the success or failure of the collaboration.

Table 45 shows the methods in the CwDBConnection class that provide management of transaction boundaries for explicit transactions.

Table 45.
CwDBConnection methods for explicit transaction management
Transaction-management task CwDBConnection method
Begin a new transaction. beginTransaction()
End the transaction, committing (saving) all changes made during the transaction to the database. commit()
Determine if a transaction is currently active. inTransaction()
End the transaction, rolling back (backing out) all changes made during the transaction. rollBack()

To specify transaction scope of an explicit transaction, follow these steps:

  1. Mark the beginning of the transaction with a call to the beginTransaction() method.
  2. Execute all SQL statements that must succeed or fail as a unit between this call to beginTransaction() and the end of the transaction.
  3. End the transaction in either of two ways:

    You can choose what conditions cause a transaction to fail. Test the condition and call rollBack() if any failure condition is met. Otherwise, call commit() to end the transaction successfully.

Important:
If you do not use beginTransaction() to specify the beginning of the explicit transaction, the database executes each SQL statement as a separate transaction. If you include beginTransaction() but do not specify the end of the database transaction with commit() or rollback() before the connection is released, InterChange Server implicitly ends the transaction based on the success of the collaboration. If the collaboration is successful, ICS commits this database transaction. If the collaboration is not successful, ICS implicitly rolls back the database transaction. Regardless of the success of the collaboration, ICS logs a warning.

The following code fragment updates three tables in the database associated with connections in the CustDBConnPool. If all these updates are successful, the code fragment commits these changes with the commit() method. If any transaction errors occur, a CwDBTransactionException exception results and the code fragment invokes the rollback() method.

CwDBConnection connection = getDBConnection("CustDBConnPool", false);
  
 // Begin a transaction
 connection.beginTransaction();
  
 // Update several tables
 try
    {
    connection.executeSQL("update table1....");
    connection.executeSQL("update table2....");
    connection.executeSQL("update table3....");
  
    // Commit the transaction
    connection.commit();
    }
  
 catch (CwDBSQLException e)
    {
    // Roll back the transaction if an executeSQL() call throws 
    // an exception
    connection.rollback();
    }
  
 // Release the database connection
 connection.release();
 

To determine whether a transaction is currently active, use the inTransaction() method.

Attention:
Use the beginTransaction(), commit(), and rollback() methods only if the connection uses explicit transaction bracketing. If the connection uses implicit transaction bracketing, use of any of these methods results in a CwDBTransactionException exception.

Releasing a connection

Once a connection is released, it is returned to its connection pool, where it is available for use by other components. The way that a connection to the database is released depends on the transaction programming model. Therefore, this section provides the following information:

Releasing a connection with implicit transaction bracketing

ICS automatically releases a connection that uses implicit transaction bracketing once it has ended the database transaction. ICS does not end the database transaction until it determines the success or failure of the collaboration object; that is, ICS releases these connections when the collaboration finishes execution. If the collaboration executes successfully, ICS automatically commits any database transactions that are still active. If the collaboration execution fails (for instance, if an exception is thrown that is not handled with a catch statement), ICS automatically rolls back any transactions that are still active.

Releasing a connection with explicit transaction bracketing

For a connection that uses explicit transaction bracketing, the connection ends in either of the following cases:

You can use the CwDBConnection.isActive() method to determine whether a connection has been released. If the connection has been released, isActive() returns false, as the following code fragment shows:

if (connection.isActive())
    connection.release();
 

Attention:
Do not use the release() method if a transaction is currently active. With implicit transaction bracketing, ICS does not end the database transaction until it determines the success or failure of the collaboration. Therefore, use of this method on a connection that uses implicit transaction bracketing results in a CwDBTransactionException exception. If you do not handle this exception explicitly, it also results in an automatic rollback of the active transaction. You can use the inTransaction() method to determine whether a transaction is active. ICS automatically releases a connection regardless of the transaction programming model it uses. In most cases, you do not need to explicitly release the connection.

Copyright IBM Corp. 2003, 2004