SQL Programming Manual for Pascal

HP NonStop SQL Programming Manual for Pascal—528614-001
7-1
7 Using Dynamic SQL
Dynamic SQL is an advanced programming technique that allows an application
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 Pascal 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);
... Insertion performed
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:
WRITELN('Enter statement to be executed: ');
READLN(CMD_BUFFER);
{ User inputs INSERT statement }
EXEC SQL
PREPARE S1 FROM :CMD_BUFFER;
EXEC SQL
EXECUTE S1;
... 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 the same way 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
Pascal compiler performs for you if you are using static SQL.
For examples of Pascal programs that use dynamic SQL operations, see Appendix C,
Examples of Dynamic NonStop SQL Programs.
Applications for Dynamic SQL
Applications programs that use dynamic SQL operations can be useful in a number of
contexts. For example:
•
You can develop an interactive interface that is similar to SQLCI, but is designed
for an inexperienced user.