Progress
Embedded SQL-92
Guide and Reference


PREPARE Statement

Application programs use a PREPARE statement to prepare another SQL statement for execution. When using PREPARE, the SQL engine parses the SQL statement to check for syntax errors. Then the engine assigns a statement identifier to the SQL statement.

An SQL statement that you PREPARE can be specified either as a character string or as a reference to a host variable. If the SQL statement is specified using a host variable reference, the host variable must be a character array.

PREPARE statements can be used for both SELECT statements and other statements. The SQL statement is prepared once, but is executed as often as necessary within the same transaction. If you COMMIT or ROLLBACK the current transaction and you want to re-execute the SQL statement, you must PREPARE it again.

EXAMPLE

The following code fragment shows how to use a PREPARE statement:

EXEC SQL BEGIN DECLARE SECTION ;
     char sql_str [256] ;
EXEC SQL END DECLARE SECTION ;
 
strcpy (sql_str, "delete from customer where cust_no = :p1") ;
 
EXEC SQL PREPARE delstmt FROM :sql_str ; 

The statement identifier used in the PREPARE statement is delstmt. This identifier is used for references to the statement in other ESQL statements. The host variable, sql_str, is declared in the DECLARE SECTION.

The host variable reference made in the SQL statement, such as p1, is a dynamic parameter. The name of the host variable used in the prepared SQL statement is not related to the name of the host variable that would contain the corresponding value. For example, in the prepared SQL statement string, you might use the name p1, and the name of the host variable used to supply the value might be cust_no_v.

EXAMPLE

The following example shows how to use a character string in the PREPARE statement:

EXEC SQL
   PREPARE selstmt FROM "select * from customer" ; 

Note that the dynamic SQL statements must not contain the terminating semicolon. The SQL statements that you cannot process with a PREPARE statement are:

You can execute other dynamic SQL statements using a two-step process. The first step is to prepare the statement with the PREPARE statement. The second step is to execute the prepared statement by supplying values, if any, to the input host variables specified in the PREPARE statement. This second step uses the EXECUTE statement.


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