SQL Programming Manual for Pascal

Introduction
HP NonStop SQL Programming Manual for Pascal—528614-001
1-4
Using Dynamic SQL
Figure 1-1 shows an example of static SQL statements embedded in a Pascal
program. In this example, the program inserts a row into the PARTS table, which has
the columns PARTNUM, PRICE, and PARTDESC. This example also declares data
items as host variables so that they can contain input values for the columns of the
row.
For other examples of static SQL programming, see Section B, Examples of Static
NonStop SQL Programs.
Using Dynamic SQL
Dynamic SQL operations enable a Pascal program to construct, compile, and execute
an SQL statement that is unknown or incomplete until the program runs. With static
SQL operations, you code the SQL statement to be executed in the source file; with
dynamic SQL, you code only the host variable that will contain the SQL statement.
A dynamic SQL operation requires some input, usually from an operator at a terminal,
to construct the final statement. The SQL statement is constructed from the operator’s
input, compiled by the SQL compiler, and then executed by using an EXECUTE or
EXECUTE IMMEDIATE statement.
Figure 1-2 on page 1-5 shows a dynamic INSERT statement similar to the static
INSERT statement in Figure 1-1. In Figure 1-1, the static INSERT statement is
embedded in the source program code; however, in Figure 1-2 on page 1-5, the
program dynamically builds the INSERT statement from information entered by an
operator.
Figure 1-1. Static SQL Statements in a Pascal Program
{ Pascal variable declarations }
EXEC SQL BEGIN DECLARE SECTION;
VAR IN_PARTS_REC : RECORD { Host variable }
IN_PARTNUM : INT16;
IN_PRICE : INT32;
IN_PARTDESC : FSTRING(18);
EXEC SQL END DECLARE SECTION;
{ Pascal procedure code: }
. . .
BEGIN
IN_PARTS_REC.IN_PARTNUM := 4120;
IN_PARTS_REC.IN_PRICE := 6000000;
IN_PARTS_REC.IN_PARTDESC := 'V8 DISK OPTION';
EXEC SQL INSERT INTO SALES.PARTS
( PARTNUM, PRICE, PARTDESC )
VALUES (:IN_PARTS_REC.IN_PARTNUM,
SETSCALE(:IN_PARTS_REC.IN_PRICE, 2),
END;
VST0101.vsd