Embedded SQL dynamic statements

Dynamic SQL statements accept a character-string host variable and a statement name as arguments. The host variable contains the SQL statement text that is processed dynamically.

The statement text is not processed when an application is precompiled. In fact, the statement text does not have to exist at the time the application is precompiled. Instead, the SQL statement is treated as a host variable for precompilation purposes and the variable is referenced during application execution.

Dynamic SQL support statements are required to transform the host variable containing SQL text into an executable form. Also, dynamic SQL support statements operate on the host variable by referencing the statement name. These support statements are:
EXECUTE IMMEDIATE
Prepares and executes a statement that does not use any host variables. Use this statement as an alternative to the PREPARE and EXECUTE statements.

For example consider the following statement in C:

	strcpy (qstring,"INSERT INTO WORK_TABLE SELECT * 
			 FROM EMP_ACT WHERE ACTNO >= 100");
	EXEC SQL  EXECUTE IMMEDIATE :qstring;
PREPARE
Turns the character string form of the SQL statement into an executable form of the statement, assigns a statement name, and optionally places information about the statement in an SQLDA structure.
EXECUTE
Executes a previously prepared SQL statement. The statement can be executed repeatedly within a connection.
DESCRIBE
Places information about a prepared statement into an SQLDA.

For example consider the following statement in C;

       strcpy(hostVarStmt, "DELETE FROM org WHERE deptnumb = 15");
       EXEC SQL PREPARE Stmt FROM :hostVarStmt;
       EXEC SQL DESCRIBE Stmt INTO :sqlda;
       EXEC SQL EXECUTE Stmt;
Note: The content of dynamic SQL statements follows the same syntax as static SQL statements, with the following exceptions:
  • The statement cannot begin with EXEC SQL.
  • The statement cannot end with the statement terminator. An exception to this is the CREATE TRIGGER statement which can contain a semicolon (;).