SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-7
Using Parameters
Input parameters function in much the same way as host variables in embedded SQL.
An input parameter can appear in an SQL expression wherever a constant can appear.
You specify input parameters in a statement as either a question mark (?) or a question
mark plus a name (?VAL). Before execution, you assign values to the place held by
each parameter. Unlike static SQL, dynamic parameters do not require length or data
type definition before program compilation.
When you submit an SQL statement dynamically, you do not necessarily know the
number or types of parameters. To obtain information about input parameters and
obtain pointers to the names of the input values, use the DESCRIBE INPUT statement.
You can obtain the number of input values from the SQLSA.
Output Parameters
SQL/MP returns data to your program through output parameters, which are user-
defined areas in the program. Output parameters can be host variables or individual
data buffers to which the program’s output SQLDA structure points. Output parameters
usually contain columns returned from a SELECT operation.
When you submit an SQL statement dynamically, you do not always know the number
or types of parameters. To obtain information about the output parameters, use the
DESCRIBE statement with an output SQLDA structure. You can obtain the number of
output values from the SQLSA.
Using a Parameter List
To ensure a one-to-one correspondence between a parameter list and the host
variables you use to supply values for the parameters, use unnamed parameters. If
duplicate parameter names appear in a statement, the names require a value for only
the first occurrence, and the duplicate occurrences receive the same value.
For example, suppose that this UPDATE statement is stored in the host variable
UPDATE-STATEMENT:
UPDATE ATABLE SET COL1 = ?A, COL2= ?A, COL3 = ?B
A PREPARE statement prepares the statement in the host variable named
UPDATE-STATEMENT:
EXEC SQL
PREPARE EXECUTE-STATEMENT FROM :UPDATE-STATEMENT
END-EXEC.
To supply values for the UPDATE statement at run time, the program uses the two host
variables HOST-VAR1 and HOST-VAR2:
EXEC SQL
EXECUTE EXECUTE-STATEMENT USING :HOST-VAR1, :HOST-VAR2
END-EXEC.
The value in HOST-VAR1 is used for both instances of parameter ?A. The value in
HOST-VAR2 is used for parameter ?B. For three host variables, SQL/MP uses the