SQL/MP Programming Manual for COBOL85

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
10-6
Using Parameters
The SQL statement is compiled without the actual input values, which are substituted
for the parameter when the SQL statement executes. For the syntax for a parameter,
see the SQL/MP Reference Manual.
Input Parameters
A dynamic SQL statement can contain input parameters, which allow your program to
construct SQL statements at run time. Input parameters convey data from the host
program to the SQL statement; they might denote criteria to be used in a WHERE
clause, values to be inserted into the database, or values used to update or delete
database records.
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