SQL/MP Programming Manual for COBOL85

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
10-5
Using Parameters
•
Use the PREPARE and EXECUTE statements to prepare a statement for
execution, save the information about the statement, and then execute the
statement as often as needed with these considerations:
°
The PREPARE statement associates a statement name with the SQL
statement specified by an SQL identifier and dynamically compiles an SQL/MP
statement. After a statement is prepared, the program can execute the
statement with the EXECUTE statement or (for SELECT statements) with a
cursor.
When you prepare a statement, the database environment generates an
access plan and a description of the result set. Preparation is useful if you want
to execute a statement multiple times with the previously generated access
plan; minimizing processing overhead.
°
The EXECUTE statement executes a previously prepared dynamic SQL
statement. You can use EXECUTE for any DDL, DML, or DCL statement
except SELECT. (Use a cursor to process a SELECT statement.)
Applications must process SELECT and nonSELECT statements differently. You do
not necessarily know what type of statement your program is processing; the
statement could, for example, be a SELECT, UPDATE, or DELETE statement. To
determine whether a statement is a SELECT statement, check the SQLSA. SELECT
and cursor statements always have at least one returned value.
This example functions the same as the example in Figure 10-1 on page 10-2, except
that it uses the PREPARE and EXECUTE statements instead of EXECUTE
IMMEDIATE:
(define the host variable)
DISPLAY "Enter statement to be executed:".
* User enters INSERT statement here
ACCEPT VAL of STATEMENT-BUFFER
(determine length of VAL)
EXEC SQL PREPARE S1 FROM :STATEMENT-BUFFER END-EXEC.
...
EXEC SQL EXECUTE S1 END-EXEC.
(Insertion performed)
Using Parameters
A parameter is an SQL identifier that serves as a place holder in a dynamic SQL
statement for a value substituted when the statement executes. (You can also use a
parameter with SQLCI.) You use parameter markers, with names preceded by question
marks (?), in place of each parameter; for example:
SELECT * FROM MONTHLY_PAYROLL WHERE NAME = ?NM.