Prepared Statement (Using Database Operations)

Using Database Operations

Prepared Statement

A Prepared Statement is a SQL statement which can also contain parameter marker as input holder.

Example: select * from table1 where col1 > ?

This statement selects all the columns from a table called table1 if column col1 is greater than a certain value. The value will be supplied during runtime.

Note –

The DB2 Connect Universal Driver only supports Updatable Resultsets for Update and Delete. The Insert operation is not supported. You can use a Prepared Statement to perform the Insert operation.

The Insert Operation

To perform an insert operation using Prepared Statement

Assign values to input fields.
Execute the executeUpdate().

This example inserts employee records. The Prepared Statement looks like this:

Insert into DB_EMPLOYEE values (?, ?, ?, ?, ?)

Note –

If you don’t want to insert values into all columns, your insert statement should look like this:

Insert into DB_EMPLOYEE (col1, col2) values (?, ?)

public class jcdPsInsert
{
    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,
 otdDB2Connect.OtdDB2ConnectOTD otdDB2Connect_1, com.stc.connector.appconn.file.
FileApplication FileClient_1, dtd.otdInputDTD_654315252.DBemployees 
otdInputDTD_DBemployees_1, dtd.otdOutputDTD1750519912.DBemployee 
otdOutputDTD_DBemployee_1 )

        throws Throwable

    {

        FileClient_1.setText( "Inserting records into db_employee table using 
Prepared Statement....." );

        FileClient_1.write();

        otdInputDTD_DBemployees_1.unmarshalFromString( input.getText() );

        for (int i1 = 0; i1 < otdInputDTD_DBemployees_1.countX_sequence_A(); i1 += 1) {

            otdDB2Connect_1.getInsert_ps().setEMP_NO( typeConverter.stringToShort( 
otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) );

            otdDB2Connect_1.getInsert_ps().setLAST_NAME( otdInputDTD_DBemployees_1.
getX_sequence_A( i1 ).getLastname() );

            otdDB2Connect_1.getInsert_ps().setFIRST_NAME( otdInputDTD_DBemployees_1.
getX_sequence_A( i1 ).getFirstname() );

            otdDB2Connect_1.getInsert_ps().setRATE( new java.math.BigDecimal( 
otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getRate() ) );

            otdDB2Connect_1.getInsert_ps().setLAST_UPDATE( typeConverter.
stringToSQLDate( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getLastDate(),
 "yyyy-MM-dd hh:mm:ss", false, "" ) );

            otdDB2Connect_1.getInsert_ps().executeUpdate();
        }

        FileClient_1.setText( "Insert Done......" );

        FileClient_1.write();

    }

}

The Update Operation

To perform an update operation using Prepared Statement

Assign value to input field.
Execute the executeUpdate() .

This example updates employee records which matches the where clause. The Prepared Statement looks like this:

Update DB_EMPLOYEE set rate = 19 where EMP_NO = ?

Note –

The content of the input.getText() file must contain the input value to substitute the parameter marker ?

package prjDB2Connect_JCDjcdALL; public class jcdPsUpdate { 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, otdDB2Connect.OtdDB2ConnectOTD otdDB2Connect_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(); otdDB2Connect_1.getUpdate_ps().setEmp_no( typeConverter.stringToShort( input.getText(), "#", false, 0 ) ); otdDB2Connect_1.getUpdate_ps().executeUpdate(); FileClient_1.setText( "Done Update." ); FileClient_1.write(); } }