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 (compiled)
- Compound SQL (inlined)
- 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 EXPLAIN MODE
- SET CURRENT EXPLAIN SNAPSHOT
- SET CURRENT FEDERATED ASYNCHRONY
- SET CURRENT IMPLICIT XMLPARSE OPTION
- SET CURRENT ISOLATION
- SET
CURRENT LOCALE LC_MESSAGES
- 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
CURRENT TEMPORAL BUSINESS_TIME
- SET
CURRENT TEMPORAL SYSTEM_TIME
- 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 ROLE (only if DYNAMICRULES run behavior is in effect for the
package)
- SET SCHEMA
- SET SERVER OPTION
- SET SESSION AUTHORIZATION
- SET SQL_CCFLAGS
- SET
USAGE LIST STATE (only if DYNAMICRULES run behavior is in effect for
the package)
- 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 previous 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.
- All prepared statements created by a unit of
work remain in a prepared state until the application terminates,
with the following exceptions:
- A statement that is prepared within a package bound with KEEPDYNAMC
NO and which is not used by an open cursor declared with
the WITH HOLD option is no longer in a prepared state when the unit
of work ends.
- A dynamic statement that is bound with KEEPDYNAMIC NO and
which is used by an open cursor declared with the WITH HOLD option
is in a prepared state until the next unit of work boundary where
the cursor is closed.
Examples
Example 1: Prepare and
execute a non-select-statement in a COBOL program. Assume the statement
is contained in a host variable HOLDER and that the program will place
a statement string into the host variable based on some instructions
from the user. The statement to be prepared does not have any parameter
markers.
EXEC SQL PREPARE STMT_NAME FROM :HOLDER
END-EXEC.
EXEC SQL EXECUTE STMT_NAME
END-EXEC.
Example 2: Prepare and execute
a non-select-statement as in example 1, except code it for a C program.
Also assume the statement to be prepared can contain any number of
parameter markers.
EXEC SQL PREPARE STMT_NAME FROM :holder;
EXEC SQL EXECUTE STMT_NAME USING DESCRIPTOR :insert_da;
Assume
that the following statement is to be prepared:
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.
Table 1. Required
values for the INSERT_DA structureSQLDA field |
Value |
SQLDAID |
SQLDA |
SQLDABC |
192 (See note 1.) |
SQLN |
4 |
SQLD |
4 |
|
|
SQLTYPE |
452 |
SQLLEN |
3 |
SQLDATA |
pointer to G01 |
SQLIND |
(See note 2.) |
SQLNAME |
|
|
|
SQLTYPE |
449 |
SQLLEN |
29 |
SQLDATA |
pointer to COMPLAINTS |
SQLIND |
pointer to 0 |
SQLNAME |
|
|
|
SQLTYPE |
453 |
SQLLEN |
6 |
SQLDATA |
(See note 3.) |
SQLIND |
pointer to -1 |
SQLNAME |
|
|
|
SQLTYPE |
453 |
SQLLEN |
3 |
SQLDATA |
pointer to A00 |
SQLIND |
pointer to 0 |
SQLNAME |
|
Note: - This value is for a PREPARE done from a 32-bit application. If
the PREPARE was done in a 64-bit application, then SQLDABC would have
the value 240.
- The value in SQLIND for this SQLVAR is ignored because the SQLTYPE
identifies a non-nullable data type.
- The value in SQLDATA for this SQLVAR is ignored because the value
of SQLIND indicates this is a null value.
|