SQL/MP Programming Manual for COBOL85

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
10-9
Developing a Dynamic SQL Application
This subsection includes information about how to support parameters and null values
in dynamic programs; the topics might not all apply to your application.
In general, the steps in a dynamic SQL application are:
1. Declare a host variable for the SQL statement to be submitted.
2. Declare the SQLCA and SQLSA data structures.
3. If you plan to support input or output parameters in the SQL statement, perform
these steps:
a. Declare the SQLDA, names buffer, if desired, and collation buffer, if desired, to
describe the parameters.
b. Define buffers for parameter values of different data types.
4. If you plan to execute the statement more than once, or if your statement includes
input or output parameters, prepare the SQL statement to compile the statement
dynamically and assign it a statement name.
5. Determine whether there are parameters in the SQL statement by examining the
SQLSA. Then use the DESCRIBE INPUT and DESCRIBE statements as needed.
6. If there are parameters in the statement, move their descriptions into an SQLDA,
set up the SQLDA structure to point to the storage for variables referenced by the
query, and initialize appropriate field values.
7. If there are input parameters in the statement, prompt the user for input. You can
use the names buffer to prompt the user. Depending on your situation, you might
also want to handle null values on input.
8. Using the input SQLDA (if there were parameters), perform the database request
as follows:
a. Execute the statement (for a statement that is not a SELECT statement). If you
prepared the statement, use an EXECUTE statement. Otherwise, use an
EXECUTE IMMEDIATE statement.
b. Or, process the SELECT statement by issuing a cursor FETCH statement.
9. Display the output. If necessary, handle null results in the output.
The next subsections discuss these steps. Examples illustrate a dynamic SQL program
that handles any statement and allocates memory at run time. The examples use hard-
coded cursor names and statement names (such as C1 and S1) to store the cursor
name and statement name. When a program uses host variables, the program can
dynamically compile multiple statements and make all the statements available for
execution simultaneously.
These examples show several methods of using dynamic SQL statements but are not
intended to represent either the most efficient or the only method to develop a
particular application.