Examples of the EXECUTE IMMEDIATE Statement

The following ESQL/C examples show EXECUTE IMMEDIATE statements in Informix® ESQL/C. Both examples use host variables that contain a CREATE DATABASE statement.

sprintf(cdb_text1, "create database %s", usr_db_id);
EXEC SQL execute immediate :cdb_text1;

sprintf(cdb_text2, "create database %s", usr_db_id2);
EXEC SQL execute immediate :cdb_text2;

The next example shows an SPL program fragment that declares local SPL variables and assigns to them portions of the text of two DDL statements. It then issues an EXECUTE IMMEDIATE statement to drop a table called DYN_TAB, specifying the DROP TABLE statement text in an SPL variable. The second EXECUTE IMMEDIATE statement in this example creates a table of the same name, in this case specifying the CREATE TABLE statement text in a character expression that concatenates the contents of two SPL variables.

   CREATE PROCEDURE myproc()
   DEFINE COLS    VARCHAR(22);
   DEFINE CRTOPER VARCHAR(16);
   DEFINE DRPOPER VARCHAR(16);
   DEFINE TABNAME VARCHAR(16);
   DEFINE QRYSTR  VARCHAR(100);
   ...
   LET CRTOPER = "CREATE TABLE ";
   LET DRPOPER = "DROP TABLE ";
   LET TABNAME = "DYN_TAB";
   LET COLS = "(ID INT, NAME CHAR(20))";
   LET QRYSTR = DRPOPER || TABNAME;
   EXECUTE IMMEDIATE QRYSTR;
	
   EXECUTE IMMEDIATE CRTOPER || TABNAME || COLS;

   END PROCEDURE;