Executing Stored Procedures (Using Database Operations)

Using Database Operations

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.

Steps for executing the Stored Procedure include:

Specify the input values.

Execute the Stored Procedure.

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

}