PREPARE statement
Use the PREPARE statement to parse, validate, and generate an execution plan for one or more SQL statements at runtime.
Syntax
(explicit id prep001)
prep001
(explicit id prep002)
prep002
PREPARE
{
statement_id
| []statement_id_var
}
FROM
{
' statement_text '
| []statement_var
| []char_expression
}
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| char_expression | Expression that evaluates to the text of a single SQL statement | Statement must be a SELECT, EXECUTE FUNCTION, or EXECUTE PROCEDURE | Expression |
| statement_id | Identifier declared here for the prepared object | Must be unique in the routine among names of cursors and prepared objects (and in SPL, among variables) | Identifier |
| statement_id_var | Host variable storing statement_id | Must have been previously declared as a character data type | Language specific |
| statement_text | Text of the SQL statement(s) to prepare | See Preparing Multiple SQL Statements and Statement Text. | Quoted String. |
| statement_var | Host variable storing the text of one or more SQL statements | Must be a character data type. Not valid if the SQL statement(s) contains the Collection-Derived Table segment. | Language specific |
Usage
Use this statement in ESQL/C or SPL routines.
The PREPARE statement enables your
program to assemble the text of one (or for ESQL/C, more than one) SQL statement
at runtime, to declare an identifier for the resulting prepared
object, and to make it executable. This dynamic form of SQL is
accomplished in three steps:
- The PREPARE statement accepts statement text as input, either as a quoted string, or an ESQL/C character variable, or (in SPL) as the value to which a character expression evaluates. Statement text can contain question-mark ( ? ) placeholders to represent values that are to be defined when the statement is executed.
- The OPEN statement (and in ESQL/C routines, the EXECUTE statement) can supply the required input values and execute the prepared statement once or many times.
- Resources allocated to the prepared statement can be released later using the FREE statement.
For more information about the replacement of placeholders in prepared statements with runtime values, see the section Preparing Statements That Receive Parameters.
The same collating order that is current when you create a prepared object is also used when that object is executed, even if the execution-time collation of the session (or of DB_LOCALE) is different.
1 ESQL/C only
2 SPL only