Providing variable input to dynamically executed SQL statements by using parameter markers

In a dynamic SQL statement, parameter markers that are indicated by a question mark (?) or a colon followed by a name (:name) are substituting host variables.

About this task

A dynamic SQL statement cannot contain host variables because host variable information (data type and length) is available only during application precompilation; during execution, host variable information is unavailable. In a dynamic SQL statement, parameter markers are used instead of host variables. A parameter marker is indicated by a question mark (?) or a colon followed by a name (:name) and indicates where to substitute a host variable inside an SQL statement.

For example, assume that you want to use a dynamic SQL statement to delete data from a table called TEMPL based on the value of an employee number. You might specify the DELETE statement as follows, using a parameter marker: 
   DELETE FROM TEMPL WHERE EMPNO = ?

To execute this statement, specify a host variable or an SQLDA structure for the USING clause of the EXECUTE statement. The contents of the host variable is used to specify the value of EMPNO.

The data type and length of the parameter marker depend on the context of the parameter marker inside the SQL statement. If the data type of a parameter marker is not obvious from the context of the statement in which it is used, use a CAST specification to specify the data type. A parameter marker for which you use a CAST specification is a typed parameter marker. A typed parameter marker is treated like a host variable of the data type used in the CAST specification. For example, the statement SELECT ? FROM SYSCAT.TABLES is invalid because the data type of the result column is unknown. However, the statement SELECT CAST(? AS INTEGER) FROM SYSCAT.TABLES is valid because the CAST specification indicates that the parameter marker represents an INTEGER value; the data type of the result column is known.

If the SQL statement contains more than one parameter marker, the USING clause of the EXECUTE statement must specify one of the following types of information:
  • A list of host variables, one variable for each parameter marker
  • An SQLDA that has one SQLVAR entry for each parameter marker for non-LOB data types or two SQLVAR entries per parameter marker for LOB data types

The host variable list or SQLVAR entries are matched according to the order of the parameter markers in the statement, and the data types must be compatible.

Note: Using a parameter marker in a dynamic SQL statement is like using a host variable in a static SQL statement in that the optimizer does not use distribution statistics and might not choose the best access plan.

The rules that apply to parameter markers are described in the PREPARE statement topic.