SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-10
Declaring a Host Variable
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 one of these database
requests:

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.

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.
Declaring a Host Variable
In a Declare Section in the Data Division, declare a host variable to serve as the buffer
or “container” for the SQL statement:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STATEMENT-BUFFER PIC X(256) VALUE SPACES.
EXEC SQL END DECLARE SECTION END-EXEC.
Declaring the SQLCA and SQLSA Data Structures
In the Data Division, declare the SQLCA and SQLSA data structures using INCLUDE
directives:
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE SQLSA END-EXEC.