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 Oracle Operations

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

Oracle Adapter Database Operations (BPEL)

The Oracle Adapter uses a number operations to query the Oracle database. Within a BPEL business process, the Oracle Adapter uses BPEL activities to perform basic outbound database operations, including:

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

Activity Input and Output

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

Figure 1-4 displays the business rules between the FileClient.write and otdOracle.Db_employeeDelete activities. In this example, the whereClause attributes appears on the Input side.

Figure 1-4 Input and Output Between Activities

image:InputOutputActivity-Oracle

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

Table 1-4 Oracle 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.

Oracle Adapter Outbound XA Support for BPEL

There are a few additional steps required to enable Business Processes for XA support when using the Oracle Adapter.

To Enable XA Support for BPEL in the Oracle Adapter

  1. In the Business Process properties, set the Enable XA for the Entire Business Process field to Yes.

    Figure 1-5 Business Process Properties

    image:Business Process Properties
  2. For all needed activities in the Business Process, set the Transaction Support field to Participates.
    image:Transaction Support

Oracle 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 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.

By default, the Table OTD has UpdatableConcurrency and ScrollTypeForwardOnly. 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.

    Note - The content of the input.getText() file may contain null, meaning it will not have a where clause or it can contain a where clause such as empno > 50.


  2. Loop through the ResultSet using the next() method.
  3. Process the return record within a while() loop.

    For example:

    package prjOracle_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.otdOutputDTD1325973702.DB_Employee otdOutputDTD_DB_Employee_1,
     otdOracle.OtdOracleOTD otdOracle_1, com.stc.connector.appconn.file.
    FileApplication FileClient_1 )
            throws Throwable
        {
            FileClient_1.setText( "Selectiong records from db_employee table via
     Table Select........" );
            FileClient_1.write();
            otdOracle_1.getDb_employee().select( input.getText() );
            while (otdOracle_1.getDb_employee().next()) {
                otdOutputDTD_DB_Employee_1.setEmpNo( typeConverter.shortToString(
     otdOracle_1.getDb_employee().getEMP_NO(), "#", false, "" ) );
                otdOutputDTD_DB_Employee_1.setLastname( otdOracle_1.getDb_employee().
    getLAST_NAME() );
                otdOutputDTD_DB_Employee_1.setFirstname( otdOracle_1.getDb_employee().
    getFIRST_NAME() );
                otdOutputDTD_DB_Employee_1.setRate( otdOracle_1.getDb_employee().
    getRATE().toString() );
                otdOutputDTD_DB_Employee_1.setLastDate( typeConverter.dateToString(
     otdOracle_1.getDb_employee().getLAST_UPDATE(), "yyyy-MM-dd hh:mm:ss", false, "" ) );
                FileClient_1.setText( otdOutputDTD_DB_Employee_1.marshalToString() );
                FileClient_1.write();
            }
            FileClient_1.setText( "Table Select Done." );
            FileClient_1.write();
        }
    
    }

To Perform an Insert Operation on a Table

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

    This example inserts an employee record.

    package prjOracle_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,
     otdOracle.OtdOracleOTD otdOracle_1,     dtd.otdInputDTD_1206505729.DB_Employee
     otdInputDTD_DB_Employee_1, com.stc.connector.appconn.file.FileApplication
    FileClient_1 ) throws Throwable
        {
            FileClient_1.setText( "Inserting records in to db_employee table......" );
            FileClient_1.write();
            otdInputDTD_DB_Employee_1.unmarshalFromString( input.getText() );
            otdOracle_1.getDb_employee().insert();
            for (int i1 = 0; i1 < otdInputDTD_DB_Employee_1.countX_sequence_A(); i1 += 1) {
                otdOracle_1.getDb_employee().setEMP_NO( typeConverter.stringToShort(
     otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) );
                otdOracle_1.getDb_employee().setLAST_NAME( otdInputDTD_DB_Employee_1.
    getX_sequence_A( i1 ).getLastname() );
                otdOracle_1.getDb_employee().setFIRST_NAME( otdInputDTD_DB_Employee_1.
    getX_sequence_A( i1 ).getFirstname() );
                otdOracle_1.getDb_employee().setRATE( new java.math.BigDecimal(
     otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getRate() ) );
                otdOracle_1.getDb_employee().setLAST_UPDATE( typeConverter.
    stringToTimestamp( otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getLastDate(),
     "yyyy-MM-dd hh:mm:ss", false, "" ) );
                otdOracle_1.getDb_employee().insertRow();
            }
            FileClient_1.setText( "Insert Done." );
            FileClient_1.write();
        }
    
    }

To Perform an Update Operation on a Table

  1. Execute the update() method.

    Note - The content of the input.getText() file may contain null, meaning it will not have a where clause or it can contain a where clause such as empno > 50.


  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 prjOracle_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,
     otdOracle.OtdOracleOTD otdOracle_1, com.stc.connector.appconn.file.FileApplication
     FileClient_1 ) throws Throwable
        {
            FileClient_1.setText( "Updating the Rate and Last_update fields .. " );
            FileClient_1.write();
            otdOracle_1.getDb_employee().update( input.getText() );
            while (otdOracle_1.getDb_employee().next()) {
            otdOracle_1.getDb_employee().setLAST_NAME( "Krishna" );
            otdOracle_1.getDb_employee().setFIRST_NAME( "Kishore" );
            otdOracle_1.getDb_employee().updateRow();
            }
            FileClient_1.setText( "Update Done." );
            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. In the Collaboration Editor you can assign values to the input parameters, execute the call, collect data, and retrieve the values from output parameters.

Executing Stored Procedures

The OTD used in the example below, contains a Stored Procedure with input parameters. These input parameters are generated by the Database OTD Wizard and are displayed in the Collaboration Editor as subnodes of the OTD.

Below are the steps for executing the 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

The following methods are provided for using the ResultSet and Update Count when they are returned by Stored Procedures:

Oracle 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 OTD, simplifies the whole process of determining whether any results, whether they are 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 and/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.

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

Collaboration Usability for a Stored Procedure ResultSet

You can use your mouse to drag and drop the Column data of the ResultSets from their OTD nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor:

// resultsAvailable() true if there’s an update count and/or a result set available.
// note, it should not be called indiscriminantly because each time the results
pointer is advanced via getMoreResults() call.
while (getSPIn().getSpS_multi().resultsAvailable())
{
    // check if there’s an update count
    if (getSPIn().getSpS_multi().getUpdateCount() > 0)
    {
        logger.info("Updated "+getSPIn().getSpS_multi().getUpdateCount()+" rows");
    }
// each result set node has an available() method (similar to OTD’s) that tells the
// user whether this particular result set is available. note, JDBC does support access
// to more than one result set at a time, i.e., cannot drag from two distinct result
// sets simultaneously
    if (getSPIn().getSpS_multi().getNormRS().available())
    {
    while (getSPIn().getSpS_multi().getNormRS().next())
    {
    logger.info("Customer Id = "+getSPIn().getSpS_multi().getNormRS().getCustomerId());
    logger.info("Customer Name = "+getSPIn().getSpS_multi().getNormRS().
getCustomerName());
    }
    if (getSPIn().getSpS_multi().getDbEmployee().available())
    {
    while (getSPIn().getSpS_multi().getDbEmployee().next())
    {
    logger.info("EMPNO = "+getSPIn().getSpS_multi().getDbEmployee().getEMPNO());
    logger.info("ENAME = "+getSPIn().getSpS_multi().getDbEmployee().getENAME());
    logger.info("JOB = "+getSPIn().getSpS_multi().getDbEmployee().getJOB());
    logger.info("MGR = "+getSPIn().getSpS_multi().getDbEmployee().getMGR());
    logger.info("HIREDATE = "+getSPIn().getSpS_multi().getDbEmployee().getHIREDATE());
    logger.info("SAL = "+getSPIn().getSpS_multi().getDbEmployee().getSAL());
    logger.info("COMM = "+getSPIn().getSpS_multi().getDbEmployee().getCOMM());
    logger.info("DEPTNO = "+getSPIn().getSpS_multi().getDbEmployee().getDEPTNO());
    }
}

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


Once the "resultsAvailable()" method has been called, 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, SeeBeyond recommends that for maximum portability, all of the results in a ResultSet object should be retrieved before OUT parameters are retrieved. Therefore, you must 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:

Oracle Table Data Types

Oracle tables support the following data types:

For all others, use the data types Float, Double, or CLOB and build them using a data type of “Other”.


Note - The Oracle driver does not support the boolean and PL/SQL RECORD datatypes in the Function and Stored Procedure.


Long RAW for Prepared Statements and Stored Procedure Support

The following two parameters must be set prior to the Insert/Update/Delete statement.

setConcurrencyToReadOnly()
setScrollTypeToForwardOnly()

Using CLOBs with the Oracle Adapter

Perform the following steps to process CLOB data types with the Oracle Adapter.

To Use a CLOB

  1. Navigate to JavaCAPS_Home\.netbeans\caps\modules\ext\oracleadapter and create a copy of the ojdbc14.jar. Rename the copy classes12.jar.
  2. In NetBeans, right-click the Oracle Adapter project, point to Import, and then select File.

    The Import File dialog box appears.

  3. Browse to and select the classes12.jar file you created in step 1.
    image:Import File dialog box
  4. Click Select.
  5. Click Import.

    The classes12.jar file appears in the project tree.


    image:Classes12 JAR file
  6. Do the following to give access to the classes12.jar API in a Java Collaboration:
    1. Open the Java Collaboration in the Collaboration Editor.
    2. In the Collaboration Editor, click the Import JAR File icon.

      The Add/Remove Jar Files window appears.

    3. Click Add.
      image:Add/Remove JAR files
    4. In the Select Jar File window, select classes12.jar and click Import.
      image:Select JAR file
    5. In the Add/Remove Jar Files window, click Close.
  7. In the Business Rules Designer, click the Class Browser button and search for CLOB.

    The Class Browser dialog box appears.


    image:Call CLOB Method
  8. Select empty_lob from the list of CLOB methods and then click Select.
  9. Create a local variable by clicking the Local Variable button on the Business Rules toolbar.

    The Create Variable dialog box appears.


    image:Create Local Variable
  10. Name the variable myCLOB and select Class.
  11. Click the ellipses next to the Class option, and choose CLOB as the Class type.
  12. Click OK to create the variable.
  13. In the Business Rules Designer, drag the CLOB to the Local Variable using the Cast method. Click Yes when the incompatible Data Type warning appears.
    image:Drag CLOB Assign
  14. Use the CLOB putString method to assign 1 to Arg().

    In the Java Collaboration Editor, the Java code resembles the following:

    public void receive( com.stc.connector.appconn.file.FileTextMessage input,cLOB.
    CLOBOTD CLOB_1 ) throws Throwable
    {
        //@map:CLOB_1.getCLOB_TEST.insert
            CLOB_1.getCLOB_TEST().insert();
    
            //@map:Copy java.math.BigDecimal.valueOf(100) to CUSTOMER_ID
            CLOB_1.getCLOB_TEST().setCUSTOMER_ID( java.math.BigDecimal.
    valueOf( 100 ) );
    
            //@map:Copy oracle.sql.CLOB.empty_lob to PROCESSED_TEXT
            CLOB_1.getCLOB_TEST().setPROCESSED_TEXT( oracle.sql.CLOB.empty_lob() );
    
            //@map:CLOB_TEST.insertRow
            CLOB_1.getCLOB_TEST().insertRow();
    
    //@map:CLOB_1.getCLOB_TEST.select("customer_id = 100 for update")
            CLOB_1.getCLOB_TEST().select( "customer_id = 100 for update" );
    //If
            if (CLOB_1.getCLOB_TEST().next()) {
                //@map:oracle.sql.CLOB myClob;
                oracle.sql.CLOB myClob;
    
                //@map:Copy cast PROCESSED_TEXT to oracle.sql.CLOB to myClob
                myClob = (oracle.sql.CLOB) CLOB_1.getCLOB_TEST().getPROCESSED_TEXT();
    
               //@map:myClob.putString(1,Text)
                myClob.putString( 1,input.getText() );
    
                //@map:CLOB_TEST.updateRow
                CLOB_1.getCLOB_TEST().updateRow();
    }
        }