SQL/MP Programming Manual for COBOL

Introduction
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
1-6
Dynamic SQL Operations
Dynamic SQL Operations
With static SQL operations, you code the actual SQL statement in the COBOL source
file. However, with dynamic SQL statements, a program can construct, compile, and
execute an SQL statement at run time. You code a host variable as a placeholder for
the dynamic SQL statement, which is usually unknown or incomplete until run time.
A dynamic SQL statement requires some input, often from a user at a terminal, to
construct the final statement. The statement is constructed at run time from the user’s
input, compiled by the SQL compiler using a PREPARE statement, and then executed
using an EXECUTE statement (or compiled and executed using an EXECUTE
IMMEDIATE statement).
Example 1-2 shows a dynamic SQL operation that uses an INSERT statement similar
to the static INSERT statement in Example 1-1 on page 1-4. In Example 1-1, the static
INSERT statement is embedded in the source program code. In Example 1-2, the
program dynamically builds the INSERT statement from information entered by a user.
Example 1-2 accesses the PARTS table, which exists on a different subvolume. A user
enters this information in the INTEXT variable to indicate the location of the PARTS
table and other values needed to construct the INSERT statement:
INSERT INTO $VOL5.SALES.PARTS
(PARTNUM, PRICE, PARTDESC)
VALUES (4120, 60000.00, "V8 DISK OPTION")
The example builds the INSERT statement from information in the INTEX variable and
moves the statement to the host variable named OPERATION. The host variable
OPERATION is available to both COBOL and SQL statements. The example uses the
EXECUTE IMMEDIATE statement to compile and execute the INSERT statement in
OPERATION. (This example could also have used a PREPARE statement to compile
the statement and an EXECUTE statement to execute it.)
For more information, see Section 10, Dynamic SQL Operations.
Example 1-2. Dynamic SQL Statement in a COBOL Program
DATA DIVISION.
WORKING-STORAGE SECTION.
01 INTEXT PIC X(200).
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 OPERATION PIC X(200).
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
400-INSERT-DATA.
MOVE INTEXT TO OPERATION.
EXEC SQL EXECUTE IMMEDIATE :OPERATION END-EXEC.