SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-8
Using Parameters
value in the first host variable for both occurrences of parameter ?A. The value in the
second host variable is used for parameter ?B, but the value in HOST-VAR3 is unused.
For example, in this statement, SQL/MP uses the value in HOST-VAR1 for both
occurrences of parameter ?A and the value in HOST-VAR2 for parameter ?B. The
value in HOST-VAR3 is ignored.
EXEC SQL
EXECUTE EXECUTE-STATEMENT USING USING :HOST-VAR1,
:HOST-VAR2,
:HOST-VAR3
END-EXEC.
Using Parameters in a Loop
Parameters are often used when a dynamic SQL statement is executed repeatedly
with different input values. In these examples, a dynamic SQL statement uses a
parameter. Because the user of this program can enter any SQL statement, the
program does not have information about the statement during compilation. The
DEFINE =PARTS represents the PARTS table.
1. A user enters this SQL statement from a terminal:
2. UPDATE =PARTS SET PRICE = ?P
3. The program copies the statement into the host variable INTEXT.
4. The program uses the PREPARE and DESCRIBE INPUT statements to put a
description of the parameter in the SQLDA structure IN-SQLDA and to put the
name of the parameter in NAMESBUF, the input names buffer. The prepared
statement is named S1.
EXEC SQL PREPARE S1 FROM :INTEXT END-EXEC.
EXEC SQL
DESCRIBE INPUT S1 INTO :IN-SQLDA NAMES INTO :NAMESBUF
END-EXEC.
5. The program enters a loop and prompts the user to supply values for successive
execution of the statement:
* BEGINNING OF LOOP
* PROMPT THE USER FOR A VALUE USING THE
* PARAMETER NAME FROM THE NAMES BUFFER.
...
Caution. If you use the same parameter name more than once in a statement, SQL/MP gives
each duplicate occurrence of the parameter the same data type, length, and other attributes as
the first occurrence. Therefore, data can be lost in some cases.
For example, during the execution of an INSERT statement, a parameter gets the same data
type and attributes as the column into which the parameter’s value is first inserted. If the
parameter value is truncated to fit into the column, the values of any duplicate occurrences of
the parameter are also truncated, even if a column is large enough to hold the complete value.