SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-34
Using Dynamic Cursors
following paragraphs provide some points to consider when you use cursors. The order
for executing statements to use a cursor with dynamic SQL operations is:
PREPARE statement-name { Dynamically compile the }
FROM : host-variable { SELECT statement defining }
{ the cursor }
...Issue DESCRIBE INPUT and DESCRIBE statements
DECLARE cursor-name CURSOR { Declare the cursor }
FOR statement- name
OPEN cursor-name { Open the cursor }
USING DESCRIPTOR input-sqlda { Get parameter values }
{ from input data buffer }
{ in the program }
... Loop until end-of-file:
FETCH cursor-name { Retrieve data }
USING DESCRIPTOR output-sqlda{ Output column values to }
{ output data buffer in the }
{ program }
CLOSE cursor-name { Close the cursor }
Cursor Use Guidelines
•
You can use a host variable wherever you can use the cursor-name and
statement-name parameters. For each new statement and cursor, you store the
name in the host variable before executing the statements.
•
The DECLARE CURSOR, PREPARE, OPEN, FETCH, CLOSE, DELETE WHERE
CURRENT, UPDATE WHERE CURRENT, DESCRIBE INPUT, and DESCRIBE
statements for a particular cursor and its associated statement must all appear in
the same procedure.
•
The PREPARE statement does not have to precede the other statements in the
program listing order; however, the PREPARE statement must be executed after
DECLARE CURSOR and before DESCRIBE, DESCRIBE INPUT, OPEN, FETCH,
and CLOSE.
Using Cursors with a USING DESCRIPTOR Clause
If the program is handling input parameters with values entered at run time, you use
the USING DESCRIPTOR clause with the OPEN statement to specify values for the
parameters in the SELECT statement. The input SQLDA specifies the address of
program data buffers that contain the input parameter values.
You also use the USING DESCRIPTOR clause with the FETCH statement to write
column values to an output buffer specified in the program’s variable declarations. The
output SQLDA specifies the address of program data buffers into which FETCH copies
the data.