Using DB2PreparedStatement methods or constants to provide extended parameter information

Use DB2PreparedStatement methods or PreparedStatement methods with DB2PreparedStatement constants to assign default values to target columns or to assign no values to target columns.

About this task

Follow these steps to send extended client information for a PreparedStatement to the data server.

Procedure

  1. Create a PreparedStatement object.

    The SQL statement is a INSERT, UPDATE, or MERGE statement.

  2. If you are not using setObject to assign the values, cast the PreparedStatement object to a com.ibm.db2.jcc.DB2PreparedStatement object.
  3. Call one of the following methods:
    • If you are not using setObject to assign the value:
      • To assign the default value of the target column to the input parameter, call DB2PreparedStatement.setDBDefault or DB2PreparedStatement.setJccDBDefaultAtName.
      • To mark the input parameter as unassigned, call DB2PreparedStatement.setDBUnassigned or DB2PreparedStatement.setJccDBUnassignedAtName.
    • If you are using setObject to assign the value:
      • To assign the default value of the target column to the input parameter, call PreparedStatement.setObject with DB2PreparedStatement.DB_PARAMETER_DEFAULT as the assigned value.
      • To mark the input parameter as unassigned, call PreparedStatement.setObject with DB2PreparedStatement.DB_PARAMETER_UNASSIGNED as the assigned value.
  4. Execute the SQL statement.

Example

The following code assigns the default values of the target columns to the third and fifth parameters in an INSERT statement. The numbers to the right of selected statements correspond to the previously described steps.

import java.sql.*;
import com.ibm.db2.jcc.*;

Connection conn;
…
PreparedStatement p = conn.prepareStatement(                   1 
  "INSERT INTO DEPARTMENT " +
  "(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) " +
  "VALUES (?,?,?,?,?)");
p.setString(1, "X00");
p.setString(2, "FACILITIES");
p.setString(4, "A00");
((com.ibm.db2.jcc.DB2PreparedStatement)p).setDBDefault(3);     2,3 
((com.ibm.db2.jcc.DB2PreparedStatement)p).setDBDefault(5);
int uCount = p.executeUpdate();                                4 
…
p.close();                         // Close PreparedStatement

The following code uses the PreparedStatement.setObject method and DB2PreparedStatement constants to perform the same function as in the previous example. The numbers to the right of selected statements correspond to the previously described steps.

import java.sql.*;
import com.ibm.db2.jcc.*;

Connection conn;
…

PreparedStatement p = conn.prepareStatement(                   1 
  "INSERT INTO DEPARTMENT " +
  "(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) " +
  "VALUES (?,?,?,?,?)");
p.setString(1, "X00");
p.setString(2, "FACILITIES");
p.setString(4, "A00");
p.setObject(3, DB2PreparedStatement.DB_PARAMETER_DEFAULT);     3 
p.setObject(5, DB2PreparedStatement.DB_PARAMETER_DEFAULT);
int uCount = p.executeUpdate();                                4 
…
p.close();                         // Close PreparedStatement

In these examples, use of the method DB2PreparedStatement.setDBDefault or the constant DB2PreparedStatement.DB_PARAMETER_DEFAULT simplifies programming of the INSERT operation. If DB2PreparedStatement.setDBDefault or DB2PreparedStatement.DB_PARAMETER_DEFAULT is not used, up to 32 different PreparedStatement objects are necessary to cover all combinations of default and non-default input values.