SQL/MP Programming Manual for COBOL85

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
10-3
Uses for Dynamic SQL
Dynamic SQL can be less efficient than static SQL because more work is deferred until
run time. This table compares stages of processing for static and 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
SQL Operation Static SQL Operation Dynamic SQL Operation
Parse the SQL statement At compile time (or as
invoked by COBOL85)
Run time
Validate the statement At compile time (or as
invoked by COBOL85)
Run time
Optimize the statement SQLCOMP Run time
Generate an application plan SQLCOMP Run time
Store the application plan SQLCOMP Not applicable
Execute the statement Run time Run time