SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-5
Determining When to Use Dynamic SQL
table name, column names, data types, and options entered by a user and stored in
local variables. The program user sees only a series of prompts, such as ENTER THE
TABLE NAME, ENTER THE FIRST COLUMN NAME, and so forth.
Determining When to Use Dynamic SQL
If you do not know the whole text of an SQL statement at development time but there
are only a few alternatives, you might want to program the alternatives into your
application. Otherwise, for applications that require greater flexibility, use dynamic SQL
statements.
Features of Dynamic SQL
When you write a program that uses dynamic SQL, you use many of the same SQL
statements as you would in static SQL. You can perform most of the same operations
using dynamic SQL statements that you perform with static SQL statements. You can
use DDL, DML, and DCL statements in both modes.
The difference between the two modes is that all or part of a dynamic SQL statement is
obtained from the user or generated by your program, stored in a character host
variable, compiled, and executed at run time. With dynamic SQL statements you must
perform some additional operations, such as building descriptors for host variables,
that the HP COBOL compiler performs for you when you use static SQL statements.
After compilation, NonStop SQL/MP executes statements in the same way whether
they are dynamic SQL statements or static SQL statements. SQL/MP places results of
dynamic SELECT statements into output parameters. You can use the DESCRIBE
statement to obtain information about the output parameters (also called select
columns).
Processing Database Requests
Use these SQL statements to execute an SQL database request dynamically:

Use the EXECUTE IMMEDIATE statement to compile and execute a statement
whose text is contained in a host variable. Execution is performed in one step,
which is very useful if you plan to execute an SQL statement only once. SQL/MP
does not store any information about the statement.

Use the PREPARE and EXECUTE statements to prepare a statement for
execution, save the information about the statement, and then execute the
statement as often as needed with these considerations:

The PREPARE statement associates a statement name with the SQL
statement specified by an SQL identifier and dynamically compiles an SQL/MP
statement. After a statement is prepared, the program can execute the
statement with the EXECUTE statement or (for SELECT statements) with a
cursor.