PREPARE
Valid in: ESQL, OpenAPI, ODBC, JDBC, .NET
The PREPARE statement prepares and names a dynamically constructed OpenSQL statement for execution.
The PREPARE statement has the following format:
EXEC SQL PREPARE statement_name
[INTO descriptor_name [USING NAMES]]
FROM string_constant | string_variable;
The PREPARE statement encodes the dynamically constructed OpenSQL statement string in the from clause and assigns it the specified statement_name.
When the program subsequently executes the prepared statement, it uses the name to identify the statement, rather than the full statement string. Both the name and statement string can be represented by either a string constant or a host language variable.
Within the statement string, replace constant expressions in WHERE clauses, INSERT VALUES clauses, and UPDATE SET clauses with question marks. When the statement executes, these question marks are replaced with specified values. Question marks cannot be used in place of table or column names or reserved words.
To illustrate, the following example prepares and executes a DELETE statement on a dynamically defined table:
statement_buffer =
'delete from ' + table_name + ' where code = ?';
exec sql prepare del_stmt from :statement_buffer;
…
exec sql execute del_stmt using :code;
The value in the variable, code, replaces the ? in the WHERE clause of the prepared DELETE statement.
Illustrating incorrect usage, the following example is wrong because it includes a parameter specification in place of the table name:
exec sql prepare bad_stmt
from 'delete from ? where code = ?';
Whenever an application executes a prepared statement that contains parameters specified with questions marks, the program must supply values for each question mark. If the statement string is blank or empty, OpenSQL returns a runtime syntax error.
If the statement name identifies an existing prepared statement, the existing statement is destroyed and the new statement takes effect. This rule holds across the dynamic scope of the application. The statement name must not identify an existing statement name that is associated with an open cursor. The cursor must be closed before its statement name can be destroyed. Once prepared, the statement can be executed any number of times.
However, if a transaction is rolled back or committed, the prepared statement becomes invalid. If the prepared statement is to be executed only once, EXECUTE IMMEDIATE should be used on the statement string. If the prepared statement is to be executed repeatedly, the prepare and execute sequence should be used.
The following statements cannot be prepared and executed dynamically:
In addition, you cannot prepare and dynamically execute OpenSQL statements that include the keyword REPEATED.
If the statement string is a SELECT statement, the select must not include an INTO clause. The SELECT statement string can include the different clauses of the cursor SELECT statement, such as the FOR UPDATE and ORDER BY clauses.
As with EXECUTE IMMEDIATE, the statement string must not include exec sql, any host language terminators, or references to variable names. If your statement string includes embedded quotes, it is easiest to specify the string in a host language variable. If you specify a string that includes quotes as a string constant, remember that quoted characters within the statement string must follow the OpenSQL string delimiting rules. Consequently, even if your host language delimits strings with double quotes, the quoted characters within the statement string must be delimited by single quotes. For complete information about embedding quotes within a string literal, see the Embedded SQL Companion Guide.
The INTO descriptor_name clause is equivalent to issuing the DESCRIBE statement after the statement is successfully prepared. For example, the PREPARE statement
exec sql prepare prep_stmt
into sqlda from :statement_buffer;
is equivalent to the following PREPARE and DESCRIBE statements:
exec sql prepare prep_stmt from :statement_buffer;
exec sql describe prep_stmt into sqlda;
The INTO clause returns the same information as does the DESCRIBE statement. If the prepared statement is a SELECT, the descriptor will contain the data types, lengths, and names of the result columns. If the statement was not a SELECT, the descriptor's sqld field will contain a zero. For more information about the results of describing a statement, see the chapter "Dynamic OpenSQL” and Describe.
This statement must be terminated according to the rules of your host language.