SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-4
Uses for Dynamic SQL
Uses for Dynamic SQL
Dynamic SQL can be very useful if an application requires:

Flexibility to construct SQL statements at run time (for example, an interactive
interface similar to SQLCI but designed for an inexperienced user).

Flexibility to defer an association with a database until run time (for example, an
application that switches between several copies of identical databases). For this
application, use a dynamic SQL program with run-time TACL DEFINEs.

Restriction of access to data in a table. For example, the program might code an
UPDATE statement for certain columns in a table but allow the user to enter any
selection criteria (WHERE clause) at run time.

Client-server support with deferral of definition of SQL statements until run time.
For example, the user of an application on a personal computer wants to
manipulate data in a database on a host system. Such an application cannot use
SQLCI. The user formulates an SQL statement on the personal computer, and the
application sends the statement to a server process on the host system over
Multilan or another communications protocol.
If you plan to execute an SQL statement only once, execute the statement dynamically
and save any memory that would have stored the execution plan.
A dynamic SQL application can accept statements directly from the user or through a
screen interface like Pathway, or the program can build the statements with little or no
user input. The application might process an entire SQL statement, or it might process
only part of a statement (such as the WHERE clause) and explicitly code the
remainder of the statement in the program. For more information about using dynamic
SQL within a Pathway server, see Constructing a Server that Interfaces With Pathway
on page 10-35.
A program that uses dynamic SQL to process input directly from a user can be similar
to SQLCI, requiring the user to understand SQL syntax to formulate a complete SQL
statement. The statement can contain input parameters. If it does, the program can
prompt the user for the parameter values.
You can also write a program for direct user input so that the user does not have to
understand SQL syntax. In this case, the program prompts the user for the necessary
values (or displays a screen on which the user enters the values) and then constructs
the SQL statement by concatenating these values to known syntax elements.
For example, a program can handle any CREATE TABLE statement by concatenating
the string “CREATE TABLE” and punctuation (for example commas and colons) to the
Generate an application plan SQLCOMP Run time
Store the application plan SQLCOMP Not applicable
Execute the statement Run time Run time
SQL Operation Static SQL Operation Dynamic SQL Operation