Dynamically executing an SQL statement by using EXECUTE IMMEDIATE

In certain situations, you might want your program to prepare and dynamically execute a statement immediately after reading it.

About this task

Suppose that you design a program to read SQL DELETE statements, similar to these, from a terminal:

DELETE FROM DSN8C10.EMP WHERE EMPNO = '000190'
DELETE FROM DSN8C10.EMP WHERE EMPNO = '000220'

After reading a statement, the program is to run it immediately.

Recall that you must prepare (precompile and bind) static SQL statements before you can use them. You cannot prepare dynamic SQL statements in advance. The SQL statement EXECUTE IMMEDIATE causes an SQL statement to prepare and execute, dynamically, at run time.

Before you prepare and execute an SQL statement, you can read it into a host variable. If the maximum length of the SQL statement is 32 KB, declare the host variable as a character or graphic host variable according to the following rules for the host languages:

In assembler, PL/I, COBOL and C, you must declare a string host variable as a varying-length string.
In Fortran, it must be a fixed-length string variable.

If the length is greater than 32 KB, you must declare the host variable as a CLOB or DBCLOB, and the maximum is 2 MB.

Examples

Example: Using a varying-length character host variable

This excerpt is from a C program that reads a DELETE statement into the host variable dstring and executes the statement:

EXEC SQL BEGIN DECLARE SECTION;
    ...
    struct VARCHAR {
      short len;
      char s[40];
      } dstring;
EXEC SQL END DECLARE SECTION;
...
/* Read a DELETE statement into the host variable dstring. */
gets(dstring);
EXEC SQL EXECUTE IMMEDIATE :dstring;
...

EXECUTE IMMEDIATE causes the DELETE statement to be prepared and executed immediately.

Declaring a CLOB or DBCLOB host variable

You declare CLOB and DBCLOB host variables according to certain rules.

The precompiler generates a structure that contains two elements, a 4-byte length field and a data field of the specified length. The names of these fields vary depending on the host language:

In PL/I, assembler, and Fortran, the names are variable_LENGTH and variable_DATA.
In COBOL, the names are variable–LENGTH and variable–DATA.
In C, the names are variable.LENGTH and variable.DATA.

Example: Using a CLOB host variable

This excerpt is from a C program that copies an UPDATE statement into the host variable string1 and executes the statement:

EXEC SQL BEGIN DECLARE SECTION;
    ...
    SQL TYPE IS CLOB(4k) string1;
EXEC SQL END DECLARE SECTION;
...
/* Copy a statement into the host variable string1. */
strcpy(string1.data, "UPDATE DSN8610.EMP SET SALARY = SALARY * 1.1");
string1.length = 44;
EXEC SQL EXECUTE IMMEDIATE :string1;
...

EXECUTE IMMEDIATE causes the UPDATE statement to be prepared and executed immediately.