SQL/MP Programming Manual for COBOL85

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
10-4
Determining When to Use Dynamic 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
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 COBOL85 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.