PREPARE statement

Use the PREPARE statement to parse, validate, and generate an execution plan for one or more SQL statements at runtime.


  • 1 ESQL/C only
  • 2 SPL only
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


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:
  1. 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.
  2. 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.
  3. 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.