Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## 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 ;

> `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`.

> Syntax
>     { :host_variable | quoted_literal }

> Examples

> 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 ;

> #### 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:

> - 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.

> ##### Authorization

> Must have DBA privileges or authorization for the SQL statement being used.

> ##### Related statements

> EXECUTE, OPEN, CLOSE, FETCH

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095