PREPARE is an executable command that can only be embedded in a host language. PREPARE cannot be dynamically prepared.
Users must have authorization to execute the command in the PREPARE statement.
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]
| 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. |
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.
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).
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. |
The two types of parameter markers are typed and untyped:
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 = ?
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.
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