Using named parameter markers with PreparedStatement objects

You can use named parameter markers instead of standard parameter markers in PreparedStatement objects to assign values to the parameter markers.

Before you begin

To ensure that applications with named parameters work correctly, regardless of the data server type and version, before you use named parameter markers in your applications, set the Connection or DataSource property enableNamedParameterMarkers to DB2BaseDataSource.YES.

About this task

Procedure

To use named parameter markers with PreparedStatement objects, follow these steps:

  1. Execute the Connection.prepareStatement method on an SQL statement string that contains named parameter markers.
    The named parameter markers must follow the rules for SQL host variable names.

    You cannot mix named parameter markers and standard parameter markers in the same SQL statement string.

    Named parameter markers are case-insensitive.

  2. For each named parameter marker, use a DB2PreparedStatement.setJccXXXAtName method to assign a value to each named input parameter.

    If you use the same named parameter marker more than once in the same SQL statement string, you need to call a setJccXXXAtName method for that parameter marker only once.

    Recommendation: Do not use the same named parameter marker more than once in the same SQL statement string if the input to that parameter marker is a stream. Doing so can cause unexpected results.
    Restriction: You cannot use standard JDBC PreparedStatement.setXXX methods with named parameter markers. Doing so causes an exception to be thrown.
  3. Execute the PreparedStatement.

Example

The following code uses named parameter markers to update the phone number to '4657' for the employee with employee number '000010'. The numbers to the right of selected statements correspond to the previously described steps.

Connection con;
PreparedStatement pstmt;
int numUpd;
…
pstmt = con.prepareStatement(
  "UPDATE EMPLOYEE SET PHONENO=:phonenum WHERE EMPNO=:empnum"); 
                                  // Create a PreparedStatement object     1 
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setJccStringAtName
  ("phonenum", "4567");
                                  // Assign a value to phonenum parameter  2 
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setJccStringAtName
  ("empnum", "000010");
                                  // Assign a value to empnum parameter
numUpd = pstmt.executeUpdate();   // Perform the update                    3 
pstmt.close();                    // Close the PreparedStatement object

The following code uses named parameter markers to update values in a PL/SQL block. The numbers to the right of selected statements correspond to the previously described steps.

Connection con;
PreparedStatement pstmt;
int numUpd;
…
String sql =
  "BEGIN " +
  "  UPDATE EMPLOYEE SET PHONENO = :phonenum WHERE EMPNO = :empnum; " +
  "END;";
pstmt = con.prepareStatement(sql); // Create a PreparedStatement object     1 
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setJccStringAtName
  ("phonenum", "4567");
                                   // Assign a value to phonenum parameter  2 
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setJccStringAtName
  ("empnum", "000010");
                                   // Assign a value to empnum parameter
numUpd = pstmt.executeUpdate();    // Perform the update                    3 
pstmt.close();                     // Close the PreparedStatement object