PREPARE statement

The PREPARE statement is used by application programs to dynamically prepare an SQL statement for execution. The PREPARE statement creates an executable SQL statement, called a prepared statement, from a character string form of the statement, called a statement string.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

For statements where authorization checking is performed at statement preparation time (DML), the privileges held by the authorization ID of the statement must include those required to execute the SQL statement specified by the PREPARE statement. The authorization ID of the statement might be affected by the DYNAMICRULES bind option.

For statements where authorization checking is performed at statement execution time (DDL, GRANT, and REVOKE statements), no authorization is required to use this statement; however, the authorization is checked when the prepared statement is executed.

For statements involving tables that are protected with a security policy, the rules associated with the security policy are always evaluated at statement execution time.

If the authorization ID of the statement holds EXPLAIN, SQLADM, or DBADM authority, the user may prepare any statement; however, the ability to execute the statement is re-checked at statement execution time.

Syntax

>>-PREPARE--statement-name-------------------------------------->

>--+------------------------------------------+----------------->
   | .-OUTPUT-.                               |   
   '-+--------+--INTO--result-descriptor-name-'   

>--+-----------------------------------+------------------------>
   '-INPUT INTO--input-descriptor-name-'   

>--FROM--+-host-variable-+-------------------------------------><
         '-expression----'   

Description

statement-name

Names the prepared statement. If the name identifies an existing prepared statement, that previously prepared statement is destroyed. The name must not identify a prepared statement that is the SELECT statement of an open cursor.

OUTPUT INTO

If OUTPUT INTO is used, and the PREPARE statement executes successfully, information about the output parameter markers in the prepared statement is placed in the SQLDA specified by result-descriptor-name.

result-descriptor-name

Specifies the name of an SQLDA. (The DESCRIBE statement may be used as an alternative to this clause.)

INPUT INTO

If INPUT INTO is used, and the PREPARE statement executes successfully, information about the input parameter markers in the prepared statement is placed in the SQLDA specified by input-descriptor-name. Input parameter markers are always considered nullable, regardless of usage.

input-descriptor-name

Specifies the name of an SQLDA. (The DESCRIBE statement may be used as an alternative to this clause.)

FROM

Introduces the statement string. The statement string is the value of the specified host variable.

host-variable

Specifies a host variable that is described in the program in accordance with the rules for declaring character string variables. It must be a fixed-length or varying-length character-string variable that is less than the maximum statement size of 2 097 152 bytes. Note that a CLOB(2097152) can contain a maximum size statement, but a VARCHAR cannot.

expression

An expression specifying the statement string. The expression must return a fixed-length or varying-length character-string type that is less than the maximum statement size of 2 097 152 bytes.

Rules

• Rules for statement strings:  The statement string must be an executable statement that can be dynamically prepared. It must be one of the following SQL statements:
  • ALTER
  • CALL
  • COMMENT
  • COMMIT
  • Compound SQL (inlined)
  • Compound SQL (compiled)
  • CREATE
  • DECLARE GLOBAL TEMPORARY TABLE
  • DELETE
  • DROP
  • EXPLAIN
  • FLUSH EVENT MONITOR
  • FLUSH PACKAGE CACHE
  • GRANT
  • INSERT
  • LOCK TABLE
  • MERGE
  • REFRESH TABLE
  • RELEASE SAVEPOINT
  • RENAME
  • REVOKE
  • ROLLBACK
  • SAVEPOINT
  • select-statement
  • SET COMPILATION ENVIRONMENT
  • SET CURRENT DECFLOAT ROUNDING MODE
  • SET CURRENT DEFAULT TRANSFORM GROUP
  • SET CURRENT DEGREE
  • SET CURRENT FEDERATED ASYNCHRONY
  • SET CURRENT EXPLAIN MODE
  • SET CURRENT EXPLAIN SNAPSHOT
  • SET CURRENT IMPLICIT XMLPARSE OPTION
  • SET CURRENT ISOLATION
  • SET CURRENT LOCALE LC_TIME
  • SET CURRENT LOCK TIMEOUT
  • SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
  • SET CURRENT MDC ROLLOUT MODE
  • SET CURRENT OPTIMIZATION PROFILE
  • SET CURRENT QUERY OPTIMIZATION
  • SET CURRENT REFRESH AGE
  • SET ROLE (only if DYNAMICRULES run behavior is in effect for the package)
  • SET ENCRYPTION PASSWORD
  • SET EVENT MONITOR STATE (only if DYNAMICRULES run behavior is in effect for the package)
  • SET INTEGRITY
  • SET PASSTHRU
  • SET PATH
  • SET SCHEMA
  • SET SERVER OPTION
  • SET SESSION AUTHORIZATION
  • SET variable
  • TRANSFER OWNERSHIP (only if DYNAMICRULES run behavior is in effect for the package)
  • TRUNCATE (only if DYNAMICRULES run behavior is in effect for the package)
  • UPDATE

Notes

• Parameter markers: Although a statement string cannot include references to host variables, it can include parameter markers. These can be replaced by the values of host variables when the prepared statement is executed. In the case of a CALL statement, a parameter marker can also be used for OUT and INOUT arguments to the procedure. After the CALL is executed, the returned value for the argument will be assigned to the host variable corresponding to the parameter marker.

  A parameter marker is a question mark (?) or a colon followed by a name (:name) that is used where a host 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".

  If the parameter marker is named, the name can include letters, numbers, and the symbols @, #, $, and _. The name is not folded to upper case.

  Named parameter markers have the same syntax as host variables, but the two are not interchangeable. A host variable has a value and is used directly in a static SQL statement. A named parameter marker is a placeholder for a value in a dynamic SQL statement and the value is provided when the statement is executed.

  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).

  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 host variable is supported and the data type is based on the promise made in the CAST function.

  Untyped parameter markers can be used in dynamic SQL statements as long as the data type of the parameter marker can be derived based on the context in the SQL statement (SQLSTATE 42610).

  The following example results in an error since in the first context, c1 would resolve to a string data type, but in the second context, c1 would resolve to a numeric data type:

  SELECT 'Hello' || c1, 5 + c1 FROM (VALUES(?)) AS T(c1)

  However, the following statement is successful since the parameter marker associated with the derived column, c1, would resolve to a numeric data type for both contexts:

  SELECT 7 + c1, 5 + c1 FROM (VALUES(?)) AS T(c1)

  See "Determining data types of untyped expressions" for the rules for typing an untyped parameter marker.
• When a PREPARE statement is executed, the statement string is parsed and checked for errors. If the statement string is invalid, the error condition is reported in the SQLCA. Any subsequent EXECUTE or OPEN statement that references this statement will also receive the same error (due to an implicit prepare done by the system) unless the error has been corrected.
• Prepared statements can be referred to in the following kinds of statements, with the restrictions shown:

  In...

  The prepared statement...

  DESCRIBE

  can be any statement

  DECLARE CURSOR

  must be SELECT

  EXECUTE

  must not be SELECT

• A prepared statement can be executed many times. Indeed, 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.
• Statement caching affects repeated preparations.

Examples

EXEC SQL  PREPARE STMT_NAME FROM :HOLDER
END-EXEC.
EXEC SQL  EXECUTE STMT_NAME
END-EXEC.

EXEC SQL  PREPARE STMT_NAME FROM :holder;
EXEC SQL  EXECUTE STMT_NAME USING DESCRIPTOR :insert_da;

INSERT INTO DEPT VALUES(?, ?, ?, ?)

The columns in the DEPT table are defined as follows:

DEPT_NO   CHAR(3) NOT NULL, -- department number
DEPTNAME  VARCHAR(29), -- department name
MGRNO     CHAR(6), -- manager number
ADMRDEPT  CHAR(3)  -- admin department number

To insert department number G01 named COMPLAINTS, which has no manager and reports to department A00, the structure INSERT_DA should have the values in Table 1 before issuing the EXECUTE statement.