Execute Immediate

Valid in: ESQL

The Execute Immediate statement executes an SQL statement specified as a string literal or in a host language variable.

Syntax

The Execute Immediate statement has the following format:

EXEC SQL EXECUTE IMMEDIATE statement_string

[INTO variable {, variable} | USING [DESCRIPTOR] descriptor_name

[EXEC SQL BEGIN;

program_code

EXEC SQL END;]]

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 the following statements:

exec sql prepare statement_name
from :statement_buffer;
exec sql execute statement_name;
'Forget' the statement_name;

The Execute Immediate can be used:

If a dynamic statement needs to be executed just once in your program
When a dynamic select statement is to be executed and the result rows are to be processed with a SELECT loop
In DROP statements, where the name of the object to be dropped is not known at the time the program is compiled

If the statement string is to be executed 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 "Working with Embedded SQL."

The EXECUTE IMMEDIATE statement must be terminated according to the rules of the host language. If the statement string is blank or empty, the DBMS Server returns a runtime syntax error.

The following SQL statements cannot be executed using EXECUTE IMMEDIATE:

CALL
CLOSE
CONNECT
DECLARE
DISCONNECT
ENDDATA
FETCH
GET DATA
GET DBEVENT
HELP
INCLUDE
INQUIRE_SQL
OPEN
PREPARE TO COMMIT
PUT DATA
SET_SQL
WHENEVER
Other dynamic SQL statements

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 a string that includes quotes as a string constant is to be specified, remember that quoted characters within the statement string must follow the SQL string delimiting rules.

If your host language delimits strings with double quotes, the quoted characters within the statement string must be delimited by the SQL 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. Use this option when the program knows the data types and lengths of the result columns before the SELECT executes. The data type of the variables must be compatible with the associated result columns. For information about the compatibility of host language variables and SQL data types, see the Embedded SQL Companion Guide.

Note: To use long varchar variables in the INTO clause, specify a DATAHANDLER clause in place of the host language variable. The syntax for the DATAHANDLER clause is as follows:

datahandler(handler_routine ([handler_arg]))[:indicator_var]

If the program does not know the types and lengths of the result columns until runtime, specify the USING clause. 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 specifying the USING clause, the result column values are placed in the variables to which the sqlvar elements point.

If the USING clause is to be used, 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 use that information to allocate the necessary variables before executing the select.

If the SELECT statement returns more than one row, include the BEGIN and END statement block. This block defines a select loop. The DBMS Server processes each row that the SELECT returns using the program code that you specify 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 a select loop is not supplied, the application receives only the first row and an error to indicate that others were returned but unseen.

Permissions

All users are permitted to use this statement.

Locking

The locking behavior of the Execute Immediate statement is dependent on which statement is executed.

Related Statements

Execute

Prepare

Examples: Execute Immediate

The following are Execute Immediate statement examples:

The following example saves a table until the first day of the next year. Next_year and current_year are integer variables.
/* There is no need for a FROM clause in this
** SELECT
*/
exec sql select date_part('year', date('now'))
into :current_year;

next_year = current_year + 1;

statement_buffer = 'save ' + table_name +
' until Jan 1 ' + next_year;
exec sql execute immediate :statement_buffer;
The following example reads an SQL statement from the terminal into a host string variable, statement_buffer. If the statement read is 'quit' the program ends. If an error occurs upon execution, the program informs the user.
exec sql include sqlca;

read statement_buffer from terminal;
loop while (statement_buffer <> 'QUIT')

exec sql execute immediate :statement_buffer;
    if (sqlcode = 0) then
    exec sql commit;
    else if (sqlcode = 100) then
    print 'No qualifying rows for statement:';
    print statement_buffer;
    else
    print 'Error        :', sqlcode;
    print 'Statement    :', statement_buffer;
    end if;
    read statement_buffer from terminal;
end loop;