PREPARE
The PREPARE statement prepares SQL statement from a character string in the host variable for later execution. It also associates the statement with a symbolic SQL name.
Syntax
PREPARE stmt_name [INTO :sqlda] FROM :hvar
Arguments
stmt_name
Is the statement to a subsequent EXECUTE or OPEN statement, or a previous DECLARE CURSOR statement.
sqlda
Is the output SQLDA data structure to be populated.
hvar
Is the host variable that contains the SQL statement.
Remarks
The PREPARE statement processes dynamic SQL statements. Because singleton SELECT statements (SELECT INTO) are not supported in dynamic SQL statements, they are not supported in PREPARE statements.
The statement name cannot be reused in multiple PREPARE statements within the same program module (source code file). Statement names are global within a program module. PREPARE statements cannot be shared by separately compiled programs linked into a single executable module, or by a program and dynamic-link libraries (DLLs) that execute in a single process.
You can use a PREPARE statement in one of two ways:
- You can open a prepared dynamic cursor. (You must declare a dynamic cursor by using the DECLARE CURSOR statement before you prepare a SELECT statement.)
- You can execute a prepared statement.
Prepared statement names are limited to use in a single cursor definition. The following statements used together are not valid:
EXEC SQL DECLARE cursor1 CURSOR FOR prep_select_statement;
EXEC SQL DECLARE cursor2 CURSOR FOR prep_select_statement; // invalid
If the PREPARE statement is used by an EXECUTE statement, hvar cannot contain an SQL statement that returns results.
When you use PREPARE, the SQL statement in :hvar cannot contain host variables or comments, but it can contain parameter markers (?). Additionally, the SQL statement cannot contain SQL keywords that pertain exclusively to Embedded SQL keywords.
The following keywords cannot be used in a PREPARE statement:
| CLOSE | FETCH |
| COMMIT | INCLUDE |
| DESCRIBE | OPEN |
| END-EXEC | PREPARE |
| EXEC SQL | ROLLBACK |
| EXECUTE | WHENEVER |
The INTO :sqlda option merges the functionality of the DESCRIBE statement with the functionality of the PREPARE statement. Therefore, the following sample statements are functionally identical:
EXEC SQL PREPARE gumbo INTO :mysqlda FROM :hamhock;
Or
EXEC SQL PREPARE gumbo FROM :hamhock;
EXEC SQL DESCRIBE gumbo INTO :mysqlda;
Note that the SQLDA data structure is populated only for output data. Even then, the application must set the value of each sqldata entry in the SQLDA data structure to the address of the corresponding program variable. (The sqldata field is part of sqlvar.) The SQLDA data structure used for input parameters must be constructed entirely by the application. For more information, see Using the SQLDA Data Structure.
Examples
EXEC SQL BEGIN DECLARE SECTION;
char prep[] = "INSERT INTO mf_table VALUES(?,?,?)";
char name[30];
char car[30];
double num;
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE prep_stat FROM :prep;
while (SQLCODE == 0)
{
strcpy(name, "Elaine");
strcpy(car, "Lamborghini");
num = 4.9;
EXEC SQL EXECUTE prep_stat USING :name, :car, :num;
}