Notes

Parameter markers: Although a statement string cannot include references to variables, it may include parameter markers. These can be replaced by the values of variables when the prepared statement is executed. A parameter marker is a question mark (?) that is used where a variable could be used if the statement string were a static SQL statement. For an explanation of how parameter markers are replaced by values, see OPEN and EXECUTE.

There are two types of parameter markers:

Typed parameter marker
A parameter marker that is specified along with its target data type. It has the general form:
   CAST(? AS data-type)
This notation is not a function call, but a "promise" that the type of the parameter at run time will be of the data type specified or some data type that can be converted to the specified data type. For example, in:
  UPDATE EMPLOYEE
    SET LASTNAME = TRANSLATE(CAST(? AS VARCHAR(12)))
   WHERE EMPNO = ?
the value of the argument of the TRANSLATE function will be provided at run time. The data type of that value will either be VARCHAR(12), or some type that can be converted to VARCHAR(12). For more information, refer to CAST specification.
Untyped parameter marker
A parameter marker that is specified without its target data type. It has the form of a single question mark. The data type of an untyped parameter marker is provided by context. For example, the untyped parameter marker in the predicate of the above update statement is the same as the data type of the EMPNO column.

Typed parameter markers can be used in dynamic SQL statements wherever a variable is supported and the data type is based on the promise made in the CAST function.

Untyped parameters markers can be used in dynamic SQL statements in selected locations where variables are supported. These locations and the resulting data type are found in Table 74. The locations are grouped in this table into expressions, predicates and functions to assist in determining applicability of an untyped parameter marker.

Table 74. Untyped Parameter Marker Usage
Untyped Parameter Marker Location Data Type
Expressions (including select list, CASE, and VALUES)
Alone in a select list that is not in a subquery Error
Alone in a select list that is in an EXISTS subquery Error
Alone in a select list that is in a subquery The data type of the other operand of the subquery.78
Alone in a select list that is in a select-statement of an INSERT statement The data type of the associated column of the target table. 78
Both operands of a single arithmetic operator, after considering operator precedence and order of operation rules.

Includes cases such as:

   ? + ? + 10
Error
One operand of a single operator in an arithmetic expression (not a datetime expression)

Includes cases such as:

   ? + ? * 10
The data type of the other operand.
Labelled duration within a datetime expression. (Note that the portion of a labelled duration that indicates the type of units cannot be a parameter marker.) DECIMAL(15,0)
Any other operand of a datetime expression (for instance 'timecol + ?' or '? - datecol'). Error
Any operands of a CONCAT operator Error
As a value on the right hand side of a SET clause of an UPDATE statement. The data type of the column. If the column is defined as a user-defined distinct type, then it is the source data type of the user-defined distinct type. 78
The expression following the CASE keyword in a simple CASE expression Error
At least one of the result-expressions in a CASE expression (both Simple and Searched) with the rest of the result-expressions either untyped parameter marker or NULL. Error
Any or all expressions following WHEN in a simple CASE expression. Result of applying the Rules for result data types to the expression following CASE and the expressions following WHEN that are not untyped parameter markers.
A result-expression in a CASE expression (both Simple and Searched) where at least one result-expression is not NULL and not an untyped parameter marker. Result of applying the Rules for result data types to all result-expressions that are other than NULL or untyped parameter markers.
Alone as a column-expression in a single-row VALUES clause that is not within an INSERT statement. Error.
Alone as a column-expression in a single-row VALUES clause within an INSERT statement. The data type of the column. If the column is defined as a user-defined distinct type, then it is the source data type of the user-defined distinct type. 78
As a value on the right side of a SET special register statement The data type of the special register.
As a value in the INTO clause of the VALUES INTO statement The data type of the associated expression. 78
As a value in a FREE LOCATOR or HOLD LOCATOR statement Locator.
As a value for the password in a SET ENCRYPTION PASSWORD statement VARCHAR(128)
As a value for the hint in a SET ENCRYPTION PASSWORD statement VARCHAR(32)
As a value in an insert-multiple-rows of an INSERT statement. INTEGER
Predicates
Both operands of a comparison operator Error
One operand of a comparison operator where the other operand is other than an untyped parameter marker or a distinct type. The data type of the other operand.78
One operand of a comparison operator where the other operand is a distinct type. Error
All operands of a BETWEEN predicate Error
Two operands of a BETWEEN predicate (either the first and second, or the first and third) Same as that of the only non-parameter marker.
Only one operand of a BETWEEN predicate Result of applying the Rules for result data types on all operands that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time.
All operands of an IN predicate, for example, ? IN (?,?,?) Error
The first operand of an IN predicate where the right hand side is a fullselect, for example, ? IN (fullselect). Data type of the selected column
The first operand of an IN predicate where the right hand side is not a fullselect, for example, ? IN (?,A,B) or for example, ? IN (A,?,B,?). Result of applying the Rules for result data types on all operands of the IN list (operands to the right of IN keyword) that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time.
Any or all operands of the IN list of the IN predicate, for example, for example, A IN (?,B,?). Result of applying the Rules for result data types on all operands of the IN predicate (operands to the left and right of the IN predicate) that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time.
Any operands in a row-value-expression of an IN predicate, for example, (c1,?) IN ... Error
Any select list items in a subquery if a row-value-expression is specified in an IN predicate, for example, (c1,c2) IN (SELECT ?, c1 FROM ...) Error
All three operands of the LIKE predicate. Error
The match expression of the LIKE predicate. Error
The pattern expression of the LIKE predicate. Either VARCHAR(32740) or VARGRAPHIC(16370) or VARBINARY(32740) depending on the data type of the match expression.

For information about using fixed-length variables for the value of the pattern, see LIKE predicate.

The escape expression of the LIKE predicate. Either VARCHAR(1) or VARGRAPHIC(1) or VARBINARY(1) depending on the data type of the match expression.
Operand of the NULL or DISTINCT predicate Error
Functions
All operands of COALESCE, IFNULL, LAND, LOR, MIN, MAX, NULLIF, VALUE, or XOR Error
The first operand of NULLIF Error
Any operand of COALESCE, IFNULL, LAND, LOR, MIN, MAX, NULLIF, VALUE, or XOR where at least one operand is other than an untyped parameter marker. Result of applying the Rules for result data types on all operands that are other than untyped parameter markers.
The first operand of LOCATE, the first operand of POSITION, or the second operand of POSSTR. Either VARCHAR(32740) or VARGRAPHIC(16370) or VARBINARY(32740) depending on the data type of the other operand.
The first operand of VARCHAR_FORMAT TIMESTAMP
All other operands of all other scalar functions including user-defined functions. Error
Operand of an aggregate function Error

Error checking: When a PREPARE statement is executed, the statement string is parsed and checked for errors. If the statement string is not valid, a prepared statement is not created and an error is returned.

In local and remote processing, the DLYPREP(*YES) option can cause some SQL statements to receive "delayed" errors. For example, DESCRIBE, EXECUTE, and OPEN might receive an SQLCODE that normally occurs during PREPARE processing.

Reference and execution rules: Prepared statements can be referred to in the following kinds of statements, with the following restrictions shown:

Statement          The prepared statement restrictions
DESCRIBE           None
DECLARE CURSOR     Must be SELECT when the cursor is opened
EXECUTE            Must not be SELECT

A prepared statement can be executed many times. If a prepared statement is not executed more than once and does not contain parameter markers, it is more efficient to use the EXECUTE IMMEDIATE statement rather than the PREPARE and EXECUTE statements.

Prepared statement persistence: All prepared statements are destroyed when:79

Scope of a statement: The scope of statement-name is the source program in which it is defined. You can only reference a prepared statement by other SQL statements that are precompiled with the PREPARE statement. For example, a program called from another separately compiled program cannot use a prepared statement that was created by the calling program.

The scope of statement-name is also limited to the thread in which the program that contains the statement is running. For example, if the same program is running in two separate threads in the same job, the second thread cannot use a statement that was prepared by the first thread.

Although the scope of a statement is the program in which it is defined, each package created from the program includes a separate instance of the prepared statement and more than one prepared statement can exist at run time. For example, assume a program using CONNECT (Type 2) statements connects to location X and location Y in the following sequence:

   EXEC SQL CONNECT TO X;
   EXEC SQL PREPARE S FROM :hv1;
   EXEC SQL EXECUTE S;
   .
   .
   .
   EXEC SQL CONNECT TO Y;
   EXEC SQL PREPARE S FROM :hv1;
   EXEC SQL EXECUTE S;

The second prepare of S prepares another instance of S at Y.

A prepared statement can only be referenced in the same instance of the program in the program stack, unless CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDACTGRP), or CLOSQLCSR(*ENDSQL) is specified on the CRTSQLxxx commands.

Allocating the SQL descriptor: If a USING clause is specified, before the PREPARE statement is executed, the SQL descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. If the number of descriptor items allocated is less than the number of result columns, a warning (SQLSTATE 01005) is returned.


78.
If the data type is DATE, TIME, or TIMESTAMP, then VARCHAR(32740) is used.
79.
Prepared statements may be cached and not actually destroyed. However, a cached statement can only be used if the same statement is prepared again.