Use the EXECUTE IMMEDIATE statement to perform tasks equivalent to what the PREPARE, EXECUTE, and FREE statements accomplish, but as a single operation.

Use this Dynamic SQL statement with IBM® Informix® ESQL/C and SPL.


Read syntax diagramSkip visual syntax diagram
>>-EXECUTE IMMEDIATE-------------------------------------------->

>--+- ' -statement--+-------------------------+--+----+-- ' +--><
   |                |           .- ;--------. |  '- ; '     |   
   |                |  (1)      V           | |             |   
   |                '------- ; ---statement-+-'             |   
   |  (2)                                                   |   

  1. ESQL/C only
  2. SPL only
Element Description Restrictions Syntax
char_expr Expression that evaluates to a character data type Must evaluate to a CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR data type Expression
statement Text of a valid SQL statement See the same sections that are listed below for statement_var See this chapter.
statement _var Variable containing statement or (in ESQL/C) a semicolon-separated list of statements Must be a previously declared variable of type CHAR, NCHAR, NVARCHAR, or VARCHAR (or in SPL, LVARCHAR). See EXECUTE IMMEDIATE and Restricted Statements and Restrictions on Valid Statements. Language specific


The EXECUTE IMMEDIATE statement dynamically executes a single SQL statement (or in ESQL/C routines, a semicolon-separated list of SQL statements) 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 specified database.

Within ESQL/C routines, the statement text specified by the variable or quoted string can include more than one SQL statement, if consecutive statements are separated by a semicolon ( ; ) delimiter. In SPL routines, however, only one statement can be included. The statement cannot be an SPL statement, but can be any SQL statement that is not listed in the sections EXECUTE IMMEDIATE and Restricted Statements or Restrictions on Valid Statements.

The specification that follows the IMMEDIATE keyword, if valid, is parsed and executed; then all data structures and memory resources are released immediately. Unless you use EXECUTE IMMEDIATE, these operations would otherwise require separate PREPARE, EXECUTE, and FREE statements.

The session environment values (such as the EXTDIRECTIVES, OPTCOMPIND, or USELASTCOMMITTED settings of the ESQL/C or SPL routine that issues the EXECUTE IMMEDIATE statement) override the corresponding ONCONFIG parameter values, if these are different.

In ANSI/ISO-compliant databases that support implicit transactions, the EXECUTE IMMEDIATE statement does not, by default, begin a new transaction. Execution of the specified SQL statement text, however, can begin a new transaction.