The simplest SQL statements to execute dynamically are those that do not return any result other than values in the SQLCA. No output host variables are used. This is the case with all data definition and data control statements, and with all data manipulation statements except SELECT.
Suppose an inventory control program is designed around the following table:
CREATE TABLE INVENTORY
(PARTNO SMALLINT NOT NULL,
DESCRIPTION VARCHAR(24) ,
QONHAND INTEGER )
The program reads SQL DELETE statements similar to these from a terminal:
DELETE FROM INVENTORY WHERE PARTNO =221 DELETE FROM INVENTORY WHERE PARTNO =315 DELETE FROM INVENTORY WHERE PARTNO =807
After reading a statement, the program immediately executes it.
SQL statements must be prepared before they can be executed. Because the SQL statements are read at run time, they have not been prepared. An SQL statement called EXECUTE IMMEDIATE causes an SQL statement to be prepared and executed--all at run time. Here is a pseudocode solution to the above problem:
EXEC SQL BEGIN DECLARE SECTION
DECLARE DSTRING VARYING CHARACTER (80)
.
.
EXEC SQL END DECLARE SECTION
READ DSTRING FROM TERMINAL
EXEC SQL EXECUTE IMMEDIATE :DSTRING
A DELETE statement is read into a host variable called DSTRING. DSTRING is then used as a parameter in the EXECUTE IMMEDIATE statement, causing the DELETE statement to be immediately prepared and executed.
A host variable can be used as a parameter for the EXECUTE IMMEDIATE
statement. The table below shows how the host variable must be declared
in the different languages:
Figure 62. Declaring of Host Variables
| Language |
| Varying-Length Variable | String Constant |
|---|---|---|---|
| Assembler |
| X |
|
| C |
| X |
|
| COBOL |
| X | X |
| FORTRAN | X |
| X |
| PL/I | X | X | X |
The Fixed-Length Variable refers to CHAR host variables, Varying-Length Variable refers to VARCHAR host variables, and String Constant refers to quoted character string constants. The following is an example of a String Constant dynamic statement:
EXECUTE IMMEDIATE 'DELETE FROM INVENTORY WHERE PARTNO=201'
The SQL statement submitted to EXECUTE IMMEDIATE must not contain host language delimiters or SQL delimited identifiers. That is, the statement must be in basic form. Avoid using either delimited identifiers or strings of DBCS characters in statements specified in string constants.
| Note: | The preferred method is to use a host variable rather than the string constant. |
The EXECUTE IMMEDIATE statement itself, however, must have appropriate delimiters. For example, in COBOL all SQL statements must be preceded by EXEC SQL, and followed by the END-EXEC keyword as follows:
EXEC SQL EXECUTE IMMEDIATE
'DELETE FROM INVENTORY WHERE PARTNO = 201'
END-EXEC.
If the host language you are using permits it, you can concatenate a constant to a variable. For example, PL/I uses two vertical bars (||) as the concatenation symbol:
EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM INVENTORY WHERE' || PREDS;
| Note: | The concatenation symbol used here is not the same as the concatenation operator discussed in Chapter 3, Coding the Body of a Program. |
The "EXEC SQL" and the semicolon (;) are the host language delimiters for PL/I. At run time, the variable PREDS should contain a character string representing one or more predicates that complete the DELETE statement. The variable PREDS must not be used as a host variable, since it is being concatenated to the constant string.
In the example above, note that the DELETE statements that were dynamically executed contained no host variables. That is, they were executed only once, with a single value for PARTNO. Suppose that you wanted to execute the DELETE statement repeatedly with different values, without having to key in the entire statement each time. Consider how it might be done if you coded the DELETE statement directly in a program:
READ PART FROM SYSIPT
DO WHILE (PART ¬= 0)
EXEC SQL DELETE FROM INVENTORY WHERE PARTNO = :PART
READ PART FROM SYSIPT
END-DO
The loop is repeated until a PART of 0 is read.
Now, suppose that you wish to read both the DELETE statement and the part numbers from a terminal for dynamic execution. When this is done, the DELETE statement itself should not contain host variables; rather, it should contain question marks (?) to indicate where the value is to be substituted:
DELETE FROM INVENTORY WHERE PARTNO = ?
This type of statement is called a parameterized SQL statement (a parameter is an input host variable). Thus far, none of the dynamic statements contained any parameter markers, and they could be executed using EXECUTE IMMEDIATE.
Parameterized SQL statements require a slightly more complex facility called PREPARE and EXECUTE. This facility can be thought of as an EXECUTE
IMMEDIATE performed in two steps. The first step (PREPARE) causes the parameterized statement to be prepared, and gives it a name of your choosing. (This name should not be declared as a host variable.) The second step (EXECUTE) causes the statement to be executed using values that you supply for the parameters. After a statement is prepared, it can be executed many times. Here is the pseudocode:
You must not execute a dynamically defined statement after ending the logical unit of work in which the statement was prepared. If you do, an error is issued.
In routines similar to the above example, the number of parameters and their data types must be known, because the host variables that provide input data are declared when the program is being written.
Naturally, this greatly limits the number of different SQL statements that you can read in. In the above example, the only SQL statements that can be executed are those containing a single parameter. This single parameter is defined as a 15-bit integer in the program, and must be used as such. For example, the pseudocode above can also process the statements below. (At the terminal, the user types in a statement followed by values for the parameter markers.)
INSERT INTO INVENTORY (PARTNO) VALUES(?)
For each value you provide for "?", the INSERT statement is executed, and a new row is inserted into INVENTORY. The value you provide is placed in the PARTNO column. The other columns of the table are given the null value (provided they are nullable).
UPDATE INVENTORY SET DESCRIPTION = 'GEAR' WHERE PARTNO = ?
For each value you provide for "?", the UPDATE statement is executed, and the DESCRIPTION column of the INVENTORY table is set to 'GEAR'.
UPDATE INVENTORY SET QONHAND = 0 WHERE PARTNO = ?
For each value you provide for "?", the UPDATE statement is executed, and the QONHAND column in the INVENTORY table is set to 0.
Obviously there are some applications for this kind of dynamic statement processing, but they are quite specialized. Suppose new parts are added to the inventory. Each part is a different kind of gear, and none of the parts are yet in the warehouse. The input stream for the pseudocode above would be as follows:
INSERT INTO INVENTORY (PARTNO) VALUES (?) 301 302 303 304 0 UPDATE INVENTORY SET DESCRIPTION = 'GEAR' WHERE PARTNO = ? 301 302 303 304 0 UPDATE INVENTORY SET QONHAND = 0 WHERE PARTNO = ? 301 302 303 304 0