Description
The EXECUTE IMMEDIATE statement executes a dynamically built statement string. Unlike the prepare and execute sequence, this statement does not name or encode the statement and cannot supply parameters. The EXECUTE IMMEDIATE statement is equivalent to:
exec sql prepare statement_name from
:statement_buffer;
exec sql execute statement_name;
'Forget' the statement_name;
EXECUTE IMMEDIATE can be used:
• To execute a dynamic statement once in your program
• To execute a dynamic select statement and process the result rows with a select loop
If you intend to execute the statement string repeatedly and it is not a SELECT statement, use the PREPARE and EXECUTE statements instead. For more information about the alternatives available for executing dynamic statements, see the chapter "Dynamic OpenSQL.” If the statement string is blank or empty, OpenSQL returns a runtime syntax error.
The EXECUTE IMMEDIATE statement must be terminated according to the rules of the host language.
The following OpenSQL statements cannot be executed using 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 choose to 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. 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.
If the statement string is a cursor update or cursor delete, the declaration of the named cursor must appear in the same file as the EXECUTE IMMEDIATE statement executing the statement string.
The INTO or USING clause can only be used when the statement string is a SELECT statement. The INTO clause specifies variables to store the values returned by a select. This option can be used if the program knows the data types and lengths of the result columns before the select executes. The variables must be type compatible with the associated result columns. For information about the compatibility of host language variables and OpenSQL data types, see the Embedded SQL Companion Guide.
Include the USING clause if the program does not know the types and lengths of the result columns until runtime. The USING clause specifies an SQL Descriptor Area (SQLDA), a host language structure having, among other fields, an array of sqlvar elements. Each sqlvar element describes and points to a host language variable. When the USING clause is specified, OpenSQL places the result column values in the variables pointed at by the sqlvar elements.
If you intend to use the USING clause, the program can first prepare and describe the SELECT statement. This process returns data type, name, and length information about the result columns to the SQLDA. Your program can then use that information to allocate the necessary variables before executing the select. For more information and about executing dynamic SELECT statements and some examples of executing a dynamic select, see the chapter "Dynamic OpenSQL.”
If the SELECT statement will return more than one row, include the BEGIN/END statement block. This block defines a select loop. OpenSQL processes each row that the select returns using the program code that you supply in the select loop. The program code inside the loop must not include any other database statements, except the ENDSELECT statement. If the select returns multiple rows and you do not supply a select loop, the application receives only the first row and an error to indicate that others were returned but unseen.