JavaScript is required to for searching.
Skip Navigation Links
Exit Print View
Using Database Operations with Oracle Java CAPS Database Adapters     Java CAPS Documentation
search filter icon
search icon

Document Information

1.  Using Database Operations

Using DB2 Operations

DB2 Database Operations (BPEL)

Activity Input and Output

DB2 Database Operations (JCD)

The Table

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Collaboration Usability for a Stored Procedure ResultSet

Prepared Statement

Batch Operations

Using DB2 Connect Operations

DB2 Connect Adapter Database Operations (BPEL)

Activity Input and Output

DB2 Connect Adapter Database Operations (JCD)

The Table

Prepared Statement

The Insert Operation

The Update Operation

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Collaboration Usability for a Stored Procedure ResultSet

Prepared Statement

Batch Operations

Using JDBC/ODBC Operations

JDBC Adapter Database Operations (BPEL)

Activity Input and Output

JDBC Adapter Database Operations (JCD)

The Table

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Collaboration Usability for a Stored Procedure ResultSet

Prepared Statement

Batch Operations

Using Oracle Operations

Oracle Adapter Database Operations (BPEL)

Activity Input and Output

Oracle Adapter Outbound XA Support for BPEL

Oracle Adapter Database Operations (JCD)

The Table

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Collaboration Usability for a Stored Procedure ResultSet

Oracle Table Data Types

Long RAW for Prepared Statements and Stored Procedure Support

Using CLOBs with the Oracle Adapter

Using SQL Server Operations

SQL Server Adapter Database Operations (BPEL)

Activity Input and Output

SQL Server Adapter Database Operations (JCD)

The Table

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Prepared Statement

Batch Operations

Result Sets

Using Sybase Operations

Sybase Adapter Database Operations (BPEL)

Activity Input and Output

Sybase Adapter Database Operations (JCD)

The Table

To Perform a Query Operation on a Table

To Perform an Insert Operation on a Table

To Perform an Update Operation on a Table

To Perform a Delete Operation on a Table

The Stored Procedure

Executing Stored Procedures

Manipulating the ResultSet and Update Count Returned by Stored Procedure

Collaboration Usability for a Stored Procedure ResultSet

Prepared Statement

Batch Operations

Using VSAM Operations

VSAM Adapter Database Operations (BPEL)

Activity Input and Output

VSAM Adapter Database Operations (JCD)

The Table

Prepared Statement

Batch Operations

Using JDBC/ODBC Operations

Database operations in the JDBC Adapter are used to access the JDBC database. Database operations are either accessed through activities in BPEL, or through methods called from a JCD Collaboration.

JDBC Adapter Database Operations (BPEL)

Within a BPEL business process, the JDBC Adapter uses BPEL activities to perform basic outbound database operations, including:

In addition to these outbound operations, the JDBC Adapter also employs the inbound ReceiveOne activity within a Prepared Statement OTD.

The ability to perform any of the above methods using a table OTD may not be possible with all third-party drivers. You have to use a Prepared Statement to perform such an operation. Check with the respective driver’s vendor for further information. This feature is known as Updatable ResultSet.

Activity Input and Output

The Java CAPS Business Rules Designer includes Input and Output columns to map and transform data between activities displayed on the Business Process Canvas.

The following figure displays the business rules between the FileClient.write and otdJDBC.Db_employeeDelete activities. In this example, and the whereClause attribute appears on the Input side.

Figure 1-3 Input and Output Between Activities

image:InputOutputActivity-JDBC

The following table lists the expected Input and Output of each database operation activity.

Table 1-3 JDBC/ODBC Operations

BPM Operations
Activity Input
Activity Output
SelectAll
where() clause (optional)
Returns all rows that fit the condition of the where() clause.
SelectMultiple
number of rows

where() clause (optional)

Returns the number of rows specified that fit the condition of the where() clause, and the number of rows to be returned.

For example: If the number of rows that meet the condition are 5 and the number of available rows are 10, then only 5 rows will be returned.

Alternately, if the number of rows that meet the condition are 20, but if the number of available rows are 10, then only 10 rows are returned.

SelectOne
where() clause (optional)
Returns the first row that fits the condition of the where() clause.
Insert
definition of new item to be inserted
Returns status.
Update
where() clause
Returns status.
Delete
where() clause
Returns status.

JDBC Adapter Database Operations (JCD)

The same database operations are also used in the JCD, but appear as methods to call from the Collaboration. Tables, Views, and Stored Procedures are manipulated through OTDs. Methods to call include:

The ability to perform any of the above methods using a table OTD may not be possible with all third-party drivers. You have to use a Prepared Statement to perform such an operation. Check with the respective driver’s vendor for further information. This feature is known as Updatable ResultSet.


Note - Refer to the Javadoc for a full description of methods included in the JDBC Adapter.


The Table

A table OTD represents a database table. It consists of fields and methods. Fields correspond to the columns of a table while methods are the operations that you can apply to the OTD. This allows you to perform Query, Update, Insert, and Delete SQL operations in a table. The ability to update via a ResultSet is called Updatable ResultSet, which is a feature supported by this adapter. By default, the Table OTD has UpdatableConcurrency and ScrollTypeForwardOnly. Normally you do not have to change the default setting.

The type of result returned by the select() method can be specified using:

To Perform a Query Operation on a Table

  1. Execute the select() method with the where clause specified if necessary.
  2. Loop through the ResultSet using the next() method.
  3. Process the return record within a while() loop.

    For example:

    package prjJDBC_JCDjcdALL;
    
    public class jcdTableSelect
    {
    
        public com.stc.codegen.logger.Logger logger;
    
        public com.stc.codegen.alerter.Alerter alerter;
    
        public com.stc.codegen.util.CollaborationContext collabContext;
    
        public com.stc.codegen.util.TypeConverter typeConverter;
    
        public void receive( com.stc.connector.appconn.file.FileTextMessage
     input, dtd.otdInputDTD_1394195520.DBemployees otdInputDTD_DBemployees_1,
     otdJDBC.OtdJDBCOTD otdJDBC_1, dtd.otdOutputDTD882991309.DBemployee
     otdOutputDTD_DBemployee_1, com.stc.connector.appconn.file.FileApplication
     FileClient_1 ) throws Throwable
        {
            FileClient_1.setText( "Selecting record(s) from db_employee table via
     table select .." );
            FileClient_1.write();
            otdJDBC_1.getDB_EMPLOYEE().select( input.getText() );
            while (otdJDBC_1.getDB_EMPLOYEE().next()) {
                otdOutputDTD_DBemployee_1.setEmpNo( typeConverter.shortToString(
     otdJDBC_1.getDB_EMPLOYEE().getEMP_NO(), "#", false, "" ) );
                otdOutputDTD_DBemployee_1.setLastname(
     otdJDBC_1.getDB_EMPLOYEE().getLAST_NAME() );
                otdOutputDTD_DBemployee_1.setFirstname(
     otdJDBC_1.getDB_EMPLOYEE().getFIRST_NAME() );
                otdOutputDTD_DBemployee_1.setRate(
     otdJDBC_1.getDB_EMPLOYEE().getRATE().toString() );
                otdOutputDTD_DBemployee_1.setLastDate(
     typeConverter.dateToString( otdJDBC_1.getDB_EMPLOYEE().getLAST_UPDATE(),
     "yyyy-MM-dd hh:mm:ss", false, "" ) );
                FileClient_1.setText( otdOutputDTD_DBemployee_1.marshalToString() );
                FileClient_1.write();
            }
            FileClient_1.setText( "Done table select." );
            FileClient_1.write();
        }
    
    }

To Perform an Insert Operation on a Table

  1. Execute the insert() method. Assign a value to a field.
  2. Insert the row by calling insertRow().

    This example inserts an employee record.

    package prjJDBC_JCDjcdALL;
    
    public class jcdInsert
    {
    
        public com.stc.codegen.logger.Logger logger;
    
        public com.stc.codegen.alerter.Alerter alerter;
    
        public com.stc.codegen.util.CollaborationContext collabContext;
    
        public com.stc.codegen.util.TypeConverter typeConverter;
    
        public void receive( com.stc.connector.appconn.file.FileTextMessage input,
     com.stc.connector.appconn.file.FileApplication FileClient_1, 
    dtd.otdInputDTD_1394195520.DBemployees otdInputDTD_DBemployees_1, 
    dtd.otdOutputDTD882991309.DBemployee otdOutputDTD_DBemployee_1, 
    otdJDBC.OtdJDBCOTD otdJDBC_1 )
            throws Throwable
        {
            FileClient_1.setText( "Inserting records into db_employee table .." );
            FileClient_1.write();
            otdInputDTD_DBemployees_1.unmarshalFromString( input.getText() );
            for (int i1 = 0; i1 < otdInputDTD_DBemployees_1.countX_sequence_A(); i1 += 1) {
                otdJDBC_1.getInsert_Ps().setEmp_no( typeConverter.stringToShort( 
    otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) );
                otdJDBC_1.getInsert_Ps().setLast_name( 
    otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getLastname() );
                otdJDBC_1.getInsert_Ps().setFirst_name( 
    otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getFirstname() );
                otdJDBC_1.getInsert_Ps().setRate( 
    new java.math.BigDecimal( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).
    getRate() ) );            otdJDBC_1.getInsert_Ps().setLast_update( 
    typeConverter.stringToSQLDate( otdInputDTD_DBemployees_1.
    getX_sequence_A( i1 ).getLastDate(), "yyyy-MM-dd hh:mm:ss", false, "" ) );
                otdJDBC_1.getInsert_Ps().executeUpdate();
            }
            FileClient_1.setText( "Done Insert." );
            FileClient_1.write();
        }
    
    }

To Perform an Update Operation on a Table

  1. Execute the update() method.
  2. Using a while loop together with next(), move to the row that you want to update.
  3. Assign updating value(s) to the fields of the table OTD
  4. Update the row by calling updateRow().
    package prjJDBC_JCDjcdALL;
    
    public class jcdUpdate
    {
    
        public com.stc.codegen.logger.Logger logger;
    
        public com.stc.codegen.alerter.Alerter alerter;
    
        public com.stc.codegen.util.CollaborationContext collabContext;
    
        public com.stc.codegen.util.TypeConverter typeConverter;
    
        public void receive( com.stc.connector.appconn.file.FileTextMessage input,
    otdJDBC.OtdJDBCOTD otdJDBC_1, dtd.otdOutputDTD882991309.DBemployee
    otdOutputDTD_DBemployee_1, dtd.otdInputDTD_1394195520.DBemployees
    otdInputDTD_DBemployees_1, com.stc.connector.appconn.file.FileApplication
    FileClient_1 ) throws Throwable
        {
            FileClient_1.setText( "Update the Rate and Last_update
     fields using Prepared Statement.. " );
            FileClient_1.write();
            otdJDBC_1.getUpdate_Ps().setEmp_no( typeConverter.stringToShort(
     input.getText(), "#", false, 0 ) );
            otdJDBC_1.getUpdate_Ps().executeUpdate();
            FileClient_1.setText( "Done Update." );
            FileClient_1.write();
        }
    
    }

To Perform a Delete Operation on a Table

The Stored Procedure

A Stored Procedure OTD represents a database stored procedure. Fields correspond to the arguments of a stored procedure while methods are the operations that you can apply to the OTD. It allows you to execute a stored procedure. Remember that while in the Collaboration Editor you can drag and drop nodes from the OTD into the Collaboration Editor.

Executing Stored Procedures

The OTD represents the Stored Procedure “LookUpGlobal” with two parameters, an inbound parameter (INLOCALID) and an outbound parameter (OUTGLOBALPRODUCTID). These inbound and outbound parameters are generated by the Database Wizard and are represented in the resulting OTD as nodes. Within the Transformation Designer, you can drag values from the input parameters, execute the call, collect data, and drag the values to the output parameters.

To Execute a Stored Procedure

  1. Specify the input values.
  2. Execute the Stored Procedure.
  3. Retrieve the output parameters if any.

    For example:

    package Storedprocedure;
    
    public class sp_jce
    {
    
        public com.stc.codegen.logger.Logger logger;
    
        public com.stc.codegen.alerter.Alerter alerter;
    
        public void receive( com.stc.connector.appconn.file.FileTextMessage
          input,com.stc.connector.appconn.file.FileApplication FileClient_1,
          employeedb.Db_employee employeedb_with_top_db_employee_1,
          insert_DB.Insert_DBOTD insert_DB_1 )
          throws Throwable
        {
         employeedb_with_top_db_employee_1.unmarshalFromString( input.getText() );
    
         insert_DB_1.getInsert_new_employee().setEmployee_no(
               java.lang.Integer.parseInt( employeedb_with_top_db_employee_1.
               getEmployee_no() ) );
    
         insert_DB_1.getInsert_new_employee().setEmployee_Lname(
               employeedb_with_top_db_employee_1.getEmployee_lname() );
    
         insert_DB_1.getInsert_new_employee().setEmployee_Fname(
               employeedb_with_top_db_employee_1.getEmployee_fname() );
    
         insert_DB_1.getInsert_new_employee().setRate(
               java.lang.Float.parseFloat( employeedb_with_top_db_employee_1.getRate() ) );
    
         insert_DB_1.getInsert_new_employee().setUpdate_date(
               java.sql.Timestamp.valueOf( employeedb_with_top_db_employee_1.
               getUpdate_date() ) );
    
         insert_DB_1.getInsert_new_employee().execute();
    
         insert_DB_1.commit();
    
         FileClient_1.setText( "procedure executed" );
    
         FileClient_1.write();
        }
    
    }

Manipulating the ResultSet and Update Count Returned by Stored Procedure

For Stored Procedures that return ResultSets and Update Count, the following methods are provided to manipulate the ResultSet:

Many drivers do not support manipulating ResultSets in a Stored Procedure. It is recommended that you use specific Adapters for Oracle, SQL Server, Sybase, DB2, and so forth, to perform such operations. JDBC stored procedures do not return records as ResultSets. Instead, the records are returned through output reference cursor parameters. Reference Cursor parameters are essentially ResultSets.

The resultsAvailable() method, added to the PreparedStatementAgent class, simplifies the whole process of determining whether any results, be it Update Counts or ResultSets, are available after a stored procedure has been executed. Although JDBC provides three methods (getMoreResults(), getUpdateCount(), and getResultSet()) to access the results of a stored procedure call, the information returned from these methods can be quite confusing to the inexperienced Java JDBC programmer and they also differ between vendors. You can simply call resultsAvailable() and if Boolean true is returned, you can expect either a valid Update Count when getUpdateCount() is called or the next ResultSet has been retrieved and made available to one of the ResultSet nodes defined for the Stored Procedure OTD, when that node’s available() method returns true.

Frequently, Update Counts information that is returned from a Stored Procedures is insignificant. You should process returned ResultSet information and avoid looping through all of the Update Counts. The following three methods control exactly what information should be returned from a stored procedure call. The enableResultSetsOnly() method, added to the PreparedStatement Agent class allows only ResultSets to be returned and thus every resultsAvailable() called only returns Boolean true if a ResultSet is available. Likewise, the enableUpdateCountsOnly() causes resultsAvailable() to return true only if an Update Count is available. The default case of enableResultsetsAndUpdateCount() method allows both ResultSets and Update Counts to be returned.

Collaboration Usability for a Stored Procedure ResultSet

The Column data of the ResultSets can be dragged-and-dropped from their nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor:

while (getSPIn().getSpS_multi().resultsAvailable())
{
if (getSPIn().getSpS_multi().getUpdateCount() > 0)
  {
    System.err.println("Updated "+getSPIn().getSpS_multi().getUpdateCount()+" rows");
  }

  if (getSPIn().getSpS_multi().getNormRS().available())
  {
    while (getSPIn().getSpS_multi().getNormRS().next())
    {
      System.err.println("Customer Id   = "+getSPIn().
getSpS_multi().getNormRS().getCustomerId());
      System.err.println("Customer Name = "+getSPIn().
getSpS_multi().getNormRS().getCustomerName());
      System.err.println();
    }
    System.err.println("===");
  }
  else if (getSPIn().getSpS_multi().getDbEmployee().available())
  {
    while (getSPIn().getSpS_multi().getDbEmployee().next())
    {
      System.err.println("EMPNO    = "+getSPIn().getSpS_multi().
          getDbEmployee().getEMPNO());
      System.err.println("ENAME    = "+getSPIn().getSpS_multi().
          getDbEmployee().getENAME());
      System.err.println("JOB      = "+getSPIn().getSpS_multi().
          getDbEmployee().getJOB());
      System.err.println("MGR      = "+getSPIn().getSpS_multi().
          getDbEmployee().getMGR());
      System.err.println("HIREDATE = "+getSPIn().getSpS_multi().
          getDbEmployee().getHIREDATE());
      System.err.println("SAL      = "+getSPIn().getSpS_multi().
          getDbEmployee().getSAL());
      System.err.println("COMM     = "+getSPIn().getSpS_multi().
          getDbEmployee().getCOMM());
      System.err.println("DEPTNO   = "+getSPIn().getSpS_multi().
          getDbEmployee().getDEPTNO());
      System.err.println();
    }
    System.err.println("===");
  }
}

Note - resultsAvailable() and available() cannot be indiscriminately called because each time they move ResultSet pointers to the appropriate locations.


After calling resultsAvailable(), the next result (if available) can be either a ResultSet or an UpdateCount if the default enableResultSetsAndUpdateCount() was used.

Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results in a ResultSet object should be retrieved before OUT parameters are retrieved. Therefore, you should retrieve all ResultSet(s) and Update Counts first followed by retrieving the OUT type parameters and return values.

The following list includes specific ResultSet behavior that you may encounter:

The Database Wizard Assistant expects the column names to be in English when creating a ResultSet.

Prepared Statement

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide. Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example:

insert into EMP_TAB(Age, Name, Dept No) value(?, ?, ?)

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”John Smith’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().executeUpdate();

Note - Drivers must be able to support metadata calls to view column information. Some drivers may not support metadata calls, in which case you must add the columns manually. For drivers that do support metadata calls, prefill the column information. For drivers that do not support the metadata call, column information can be left blank.


Batch Operations

To achieve better performance, consider using a bulk insert if you have to insert many records. This is the “Add Batch” capability. The only modification required is to include the addBatch() method for each SQL operation and then the executeBatch() call to submit the batch to the database server. Batch operations apply only to Prepared Statements.

Not all drivers support batch operations. Check with the respective driver’s vendor for further information.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”John Smith’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().addBatch();

getPrepStatement().getPreparedStatementTest().setAge(45);
getPrepStatement().getPreparedStatementTest().setName(”Judy Miller’);
getPrepStatement().getPreparedStatementTest().setDeptNo(7);
getPrepStatement().getPreparedStatementTest().addBatch();
getPrepStatement().getPreparedStatementTest().executeBatch();