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.
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" |
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.
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.
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() |
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():
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");
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.
To access the returned data, follow these steps:
Table 39 shows the methods in the CwDBConnection class
that provide access to the rows of returned query data.
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()); }
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)");
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.
You can use the executeSQL() method to execute a stored-procedure call as long as both of the following conditions exist:
If the stored procedure uses an OUT parameter, you must use executeStoredProcedure() to execute it.
The executeSQL() method does not save the prepared statement for the stored-procedure call. Therefore, if you call the same stored procedure more than once (for example, in a loop), use of executeSQL() can be slower than calling a method that does save the prepared statement: executePreparedSQL() or executeStoredProcedure().
For more information, see "Executing stored procedures".
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():
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:
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()); } }
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".
You can use the executePreparedSQL() method to execute a stored-procedure call as long as both of the following conditions exist:
If the stored procedure uses an OUT parameter, you must use executeStoredProcedure() to execute it.
The executePreparedSQL() method saves the prepared statement for the stored-procedure call in memory. Therefore, if you call the stored procedure only once, use of executePreparedSQL() can use more memory than calling the stored procedure with executeSQL(), which does not save the prepared statement.
For more information, see "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.
Table 41 shows the methods in the CwDBConnection class
that call 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 |
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:
An OUT parameter is a parameter through which the stored procedure returns a value to the calling code. If the stored procedure uses an OUT parameter, you must use executeStoredProcedure() to call the stored procedure.
The executeStoredProcedure() method saves the compiled version of the stored procedure. Therefore, if you call the same stored procedure more than once (for example, in a loop), use of executeStoredProcedure() can be faster than executeSQL() because the database can reuse the precompiled version.
The following sections describe how to use the executeSQL() and executeStoredProcedure() methods to call a stored procedure.
To call a stored procedure that does not include any OUT parameters, you can use either of the following methods of CwDBConnection:
This procedure call is sent as a string to the database, which compiles it into a prepared statement before executing it. This prepared statement is not saved. Therefore, executeSQL() is useful for a stored procedure that only needs to be called once.
In its first invocation, this procedure call is sent to the database, which creates the prepared statement and executes it. However, the database then sends this prepared statement back to executePreparedSQL(), which saves it in memory. Therefore, executePreparedSQL() is useful for a stored procedure that needs to be called more than once (for example, in a loop).
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()".
connection.executeSQL("begin myproc(...); end;");
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:
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():
Use the CwDBStoredProcedureParam() constructor to create a new CwDBStoredProcedureParam object. To this constructor, pass the following parameter information to initialize the object:
Use the addElement() or add() method of the Vector class to add the CwDBStoredProcedureParam object.
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:
Therefore, you must initialize its associated CwDBStoredProcedureParam object with an in/out type of PARAM_IN, as well as with the appropriate value to send into the stored procedure. Because emp_id is declared as the SQL NUMBER type (which holds an integer value), the parameter's value is of a Java Object that holds integer values: Integer.
For this parameter, create an empty CwDBStoredProcedureParam object to send into the stored procedure. You initialize this object with an in/out type of PARAM_OUT. However, you provide a dummy Integer value for this parameter. Once the stored procedure completes execution, you can obtain the returned value from this OUT parameter with the getValue() method.
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(); }
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.
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:
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
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.
The connection's transaction programming model determines how the scope of the database transaction is specified. Therefore, this section provides the following information:
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.
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.
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.
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:
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.
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.
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:
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.
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();