PREPARE

The PREPARE statement (embedded SQL only) transforms a character string form of an SQL statement into an executable form of the same statement. The resulting executable statement is known as a prepared statement, while the original character string is known as the statement string.

Invocation

PREPARE is an executable command that can only be embedded in a host language. PREPARE cannot be dynamically prepared.

Authorization

Users must have authorization to execute the command in the PREPARE statement.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

PREPARE statement-name
    [INTO descriptor-name [USING {NAMES | LABELS | ANY | BOTH}]]
    {[ATTRIBUTES attr-host-variable] FROM host-variable |
     FROM string-expression}

attr-host-variable

{{ASENSITIVE | INSENSITIVE | SENSITIVE [DYNAMIC | STATIC]} |
{SCROLL | NOSCROLL} |
holdability |
returnability |
rowset-positioning |
fetch-first-clause |
{read-only-clause | update-clause} |
optimize-clause |
isolation-clause |
{FOR MULTIPLE ROWS | FOR SINGLE ROW} |
{ATOMIC | NOT ATOMIC CONTINUE ON SQLEXCEPTION} [...] }

holdability

[WITH HOLD | WITHOUT HOLD]

returnability

[WITH RETURN [TO CALLER] | WITHOUT RETURN]

rowset-positioning

[WITH ROWSET POSITIONING | WITHOUT ROWSET POSITIONING]

Parameters:

statement-name Names the prepared statement. If a prepared statement by that name already exists, then that prepared statement is destroyed and replaced by the newer version specified in this PREPARE statement.
descriptor-name Preceded by the keyword INTO, identifies an SQLDA containing a valid description of host variables. See Overview for information about SQLDA.
host-variable Preceded by the keyword FROM, must identify a variable (containing an SQL statement) described in the application program in accordance with the rules for declaring character string variables in the host language.

Description

The PREPARE statement is used by application programs to dynamically prepare an SQL statement for later use by an EXECUTE statement. Any SQL statement other than CLOSE, COMMIT, DECLARE CURSOR, DESCRIBE, EXECUTE, EXECUTE IMMEDIATE, FETCH, INCLUDE, OPEN, PREPARE, ROLLBACK, and WHENEVER may be prepared.

The prepared statement may not begin with an EXEC SQL or end with a statement terminator. Neither may the prepared statement contain references to host variables nor include comments. Although a statement may not contain host variables, it may include parameter markers. A parameter marker is a question mark character that will be replaced by a host variable at execution time.

INTO

The optional INTO clause places information about the prepared statement into the SQLDA specified in descriptor-name. This optional clause has the same effect as executing an extra DESCRIBE statement for the SQLDA (see DESCRIBE statement).

USING

Indicates what value to assign to each SQLNAME variable in the SQLDA when INTO is used. If the requested value does not exist, SQLNAME is set to length 0.

NAMES Assigns the name of the column. This is the default.
LABELS Assigns the label of the column. (Column labels are defined by the LABEL ON statement.)
ANY Assigns the column label, and, if the column has no label, the column name.
BOTH Assigns both the label and name of the column. In this case, two or three occurrences of SQLVAR per column, depending on whether the result set contains distinct types, are needed to accommodate the additional information. To specify this expansion of the SQLVAR array, set SQLN to 2¦n or 3¦n, where n is the number of columns in the object being described. For each of the columns, the first n occurrences of SQLVAR, which are the base SQLVAR entries, contain the column names. Either the second or third n occurrences of SQLVAR, which are the extended SQLVAR entries, contain the column labels. If there are no distinct types, the labels are returned in the second set of SQLVAR entries. Otherwise, the labels are returned in the third set of SQLVAR entries.

Parameter markers

The two types of parameter markers are typed and untyped:

Typed parameter marker

A parameter marker that is specified with its target data type. A typed parameter marker has the general form:

CAST(? AS data-type)

This notation is not a function call, but rather is a "promise" that the data type of the host variable at run time will be the same as, or can be converted to, the data type that was specified.

In the following example, the value of the argument that is provided for the TRANSLATE function at run time must be VARCHAR(12) or a data type that can be converted to VARCHAR(12).

UPDATE EMPLOYEE
    SET LASTNAME = TRANSLATE(CAST(? AS VARCHAR(12)))
    WHERE EMPNO = ?

Untyped parameter marker

A parameter marker that is specified without its target data type. An untyped parameter marker has the form of a single question mark. The context in which the parameter marker appears determines its data type. For example, in the above UPDATE statement, the data type of the untyped parameter marker in the predicate 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.

Example:

In COBOL the host-variable must be a varying-length string variable defined in working storage of the code.

EXEC SQL 
    PREPARE getcust 
        FROM 'SELECT cno, name FROM cust'
END-EXEC

Another example of PREPARE also involving EXECUTE:

EXEC SQL 
    PREPARE inscust FROM INSERT 
        INTO cust(cno, name) 
        VALUES (?, ?)
END-EXEC
.
. assign values to CNO and CNAME 
.
EXEC SQL 
    EXECUTE inscust 
        USING :CNO, :CNAME
END-EXEC

Alternatively, it is possible to move an SQL string into a host variable and prepare that:

MOVE 'INSERT INTO cust (cno, name) VALUES (?, ?)'
    TO HOLD
EXEC SQL
    PREPARE inscust FROM :HOLD END-EXEC
.
. assign values to CNO and CNAME
.
EXEC SQL 
    EXECUTE inscust 
        USING :CNO, :CNAME
END-EXEC