SQL Programming Manual for TAL

Introduction
HP NonStop SQL Programming Manual for TAL—527887-001
1-6
Using Dynamic SQL Operations
Figure 1-2 shows a dynamic SQL operation that uses an INSERT statement similar to
the static INSERT statement in Figure 1-1 on page 1-5. In Figure 1-1 on page 1-5, the
static INSERT statement is embedded in the source program code; however, in
Figure 1-2, the program dynamically builds the INSERT statement from information
entered by a user.
The program in Figure 1-2 accesses the PARTS table, which has the same file name,
but resides on a different subvolume. When this program runs, it prompts a user for
information to build the INSERT statement. The user enters this information in the
INTEXT variable to specify the specific 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 program builds the INSERT statement from the information in INTEXT and moves
the statement to the host variable OPERATION. The program has declared
OPERATION as a host variable, so that it is available to both TAL and SQL statements.
The program then uses the EXECUTE IMMEDIATE to execute the INSERT statement
in OPERATION.
For more information, see Section 7, Dynamic NonStop SQL Operations and
Section C, Examples of Dynamic NonStop SQL Programs.
Figure 1-2. Dynamic SQL Statements in a TAL Program
! TAL data declarations
EXEC SQL BEGIN DECLARE SECTION;
STRING operation[0:199]; ! Host variable
EXEC SQL END DECLARE SECTION;
! TAL procedure
! Copy intext into :operation host variable
...
...
! Execute the SQL statement:
EXEC SQL EXECUTE IMMEDIATE :operation;
VST0102.vsd