test

Using Database Operations

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:

The Query (Select) Operation

ProcedureTo 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();
    }

}

The Insert Operation

ProcedureTo 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();
    }

}

The Update Operation

ProcedureTo 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();
    }

}

The Delete Operation

ProcedureTo perform a delete operation on a table

  1. Execute the delete() 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.

    In this example DELETE an employee.


    package prjOracle_JCDjcdALL;

public class jcdDelete
{
    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( "Deleting record............" );
        FileClient_1.write();
        otdOracle_1.getDb_employee().delete( input.getText() );
        FileClient_1.setText( "Delete Done." );
        FileClient_1.write();
    }
}

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: