SQL Programming Manual for TAL

HP NonStop SQL Programming Manual for TAL—527887-001
7-1
7
Dynamic NonStop SQL Operations
Dynamic SQL operations allow a program to construct, compile, and execute an SQL
statement that is unknown until the program is executing. This section describes
concepts that are important for understanding how dynamic SQL operations work.
A static SQL statement appears in the TAL source program at compile time. A program
using static SQL statements receives input values from host variables and sends
output values to host variables. A static SQL statement is:
EXEC SQL
INSERT INTO EMPLOYEE_TABLE VALUES ('BROWN',6400);
In contrast, all or part of a dynamic SQL statement is input or generated, stored in a
character host variable, compiled, and executed at run time. A program using dynamic
SQL sends input values to SQL through SQL input parameters and receives output
values from SQL through host variables or data buffers defined in the program. A
dynamic SQL example is:
User enters an INSERT statement
Program reads INSERT statement into command buffer
EXEC SQL
PREPARE statement-name FROM : statement-buffer;
EXEC SQL
EXECUTE statement-name; ! Insertion performed
You can perform most of the same operations using dynamic SQL that you can
perform with static SQL. You can use DDL, DML, and DCL statements in both modes;
cursors work similar in both modes.
After compilation, SQL executes the statements in the same way whether they are
dynamic SQL statements or static SQL statements. With dynamic SQL, however, you
must perform some operations, such as building descriptors for host variables, that the
TAL compiler performs for you if you are using static SQL.
For examples of TAL programs that use dynamic SQL operations, see Appendix C,
Examples of Dynamic NonStop SQL Programs