Informix Guide to SQL: Syntax SQL Statements

Use the EXECUTE IMMEDIATE statement to perform the functions of the PREPARE, EXECUTE, and FREE statements.

Use this statement with ESQL/C.

The EXECUTE IMMEDIATE statement makes it easy to execute dynamically a single simple SQL statement that is constructed during program execution. For example, you can obtain the name of a database from program input, construct the DATABASE statement as a program variable, and then use EXECUTE IMMEDIATE to execute the statement, which opens the database.

The quoted string is a character string that includes one or more SQL statements. The string, or the contents of statement_var, is parsed and executed if correct; then all data structures and memory resources are released immediately. In the usual method of dynamic execution, these functions are distributed among the PREPARE, EXECUTE, and FREE statements.

The maximum length of an EXECUTE IMMEDIATE statement is 64 kilobytes.

You cannot use the EXECUTE IMMEDIATE statement to execute the following SQL statements. Although the EXECUTE PROCEDURE statement appears on this list, the restriction applies only to EXECUTE PROCEDURE statements that return values.

In addition, you cannot use the EXECUTE IMMEDIATE statement to execute the following statements in text that contains multiple statements that are separated by semicolons.

Use a PREPARE and either a cursor or the EXECUTE statement to execute a dynamically constructed SELECT statement.

The following restrictions apply to the statement that is contained in the quoted string or in the statement variable:

• The statement cannot contain a host-language comment.
• Names of host-language variables are not recognized as such in prepared text. The only identifiers that you can use are names defined in the database, such as table names and columns.
• The statement cannot reference a host variable list or a descriptor; it must not contain any question-mark (?) placeholders, which are allowed with a PREPARE statement.
• The text must not include any embedded SQL statement prefix, such as the dollar sign ($) or the keywords EXEC SQL. Although it is not required, the SQL statement terminator (;) can be included in the statement text.
• A SELECT or INSERT statement cannot contain a Collection Derived Table clause. EXECUTE IMMEDIATE cannot process input host variables, which are required for a collection variable. Use the EXECUTE statement or a cursor to process prepared accesses to collection variables.

The following examples show EXECUTE IMMEDIATE statements in ESQL/C. Both examples use host variables that contain a CREATE DATABASE statement. The first example uses the SQL statement terminator (;) inside the quoted string.

sprintf(cdb_text1,  "create  database  %s;",  usr_db_id);

EXEC SQL execute  immediate :cdb_text;

sprintf(cdb_text2,  "create  database  %s",  usr_db_id);

EXEC SQL execute  immediate :cdb_text;

Related statements: EXECUTE, FREE, and PREPARE

For a discussion of quick execution, see the Informix Guide to SQL: Tutorial.