The Table (Using Database Operations)

Using Database Operations

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:

SetConcurrencytoUpdatable
SetConcurrencytoReadOnly
SetScrollTypetoForwardOnly
SetScrollTypetoScrollSensitive
SetScrollTypetoInsensitive

The Query (Select) Operation

To perform a query operation on a table

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.

Loop through the ResultSet using the next() method.

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

To perform an insert operation on a table

Execute the insert() method. Assign a field.

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

To perform an update operation on a table

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.

Using a while loop together with next(), move to the row that you want to update.

Assign updating value(s) to the fields of the table OTD

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

To perform a delete operation on a table

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