Try OpenEdge Now
skip to main content
SQL Reference
ESQL Reference : Embedded SQL : ESQL elements and statements : PREPARE
 
PREPARE
Parses and assigns a name to an ad hoc or dynamically generated SQL statement for execution. You use a PREPARE statement in a series of steps that allows a program to accept or generate SQL statements at run time.

Syntax

PREPARE statement_name FROM statement_string ;

Parameters

statement_name
A name for the dynamically generated statement. DESCRIBE, EXECUTE, and DECLARE CURSOR statements refer to this statement_name. A statement_name must be unique in a program.
statement_string
Specifies the SQL statement to be prepared for dynamic execution. You can use either the name of a C Language string variable containing the SQL statement, or you can specify the SQL statement as a quoted literal. If there is an SQL syntax error, the PREPARE statement returns an error in the SQLCA.
{ :host_variable | quoted_literal }

Notes

*A statement string can have one or more references to input variables. These variables represent values supplied at run time to:
*INSERT and UPDATE statements
*Predicates in DELETE, UPDATE, and SELECT statements
*A program supplies an input variable to a PREPARE statement either as a substitution name or as a parameter marker. For example:
*A substitution name is a name preceded by a colon ( : ) in a statement string. This name does not refer to a C Language variable, but acts only as a placeholder for input variables.
*A parameter marker is a question mark ( ? ) in the statement string, serving as a placeholder for input variables.
*The USING clauses of EXECUTE and OPEN statements identify host language storage. The values in this storage expand a statement string, replacing a substitution name or a parameter marker. You can design your program to execute the same prepared statement many times in a transaction, supplying different values for input variables for each execution. If you COMMIT or ROLLBACK the transaction, you must PREPARE the statement string again.

Example

The first example is a code fragment from the DynUpd function in sample program 3DynUpd.pc, which illustrates dynamic processing of an UPDATE statement:
/*
** Process a dynamic non-SELECT input statement
** PREPARE the statement
** EXECUTE the prepared statement
** COMMIT WORK
*/
EXEC SQL PREPARE dynstmt FROM :sql_stmt_v ;
EXEC SQL EXECUTE dynstmt ;
EXEC SQL COMMIT WORK ;
This example is a code fragment from the DynSel function in sample program 4DynSel.pc, which illustrates dynamic processing of a SELECT statement:
/*
** PREPARE a the dynamic SELECT statement.
** DECLARE cursor for the prepared SELECT statement.
** NOTE: You must set input parameter values before OPEN CURSOR.
** If your query has input parameters, you must define them in
** the DECLARE SECTION.
** OPEN the declared cursor.
** NOTE: For static statements, if a DECLARE CURSOR
** statement contains references to automatic variables,
** the OPEN CURSOR statement must be in the same C function.
**
** Name WHENEVER routine for NOT FOUND condition.
** FETCH a row and print results until no more rows.
*/
EXEC SQL PREPARE stmtid from :sel_stmt_v ;
EXEC SQL DECLARE dyncur CURSOR FOR stmtid ;
EXEC SQL OPEN dyncur ;
EXEC SQL WHENEVER NOT FOUND GOTO seldone ;
* Authorization
* Related statements