HP NonStop SQL/MP, Programming Manual

Page: 218 / 331

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for C—429847-008
10-20
Using Dynamic SQL Cursors
Using Dynamic SQL Cursors
Dynamic SQL statements use cursors to process SELECT statements in the same way
static SQL statements use cursors. The program reads rows from a table, one by one,
and sends the column values to output data buffers specified in the program. These
paragraphs provide some points to consider when you use cursors.
The order for executing statements to use a cursor with dynamic SQL is shown in this
table:
Follow these guidelines when you declare and use a cursor:

If you are using the COBOL85 or SQL compiler interface, you can use a host
variable wherever you can use the
cursor-name
and
statement-name
parameters. For each new statement and cursor, 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, unless you are using a foreign cursor. See
Using Foreign
Cursors on page 4-24.

The PREPARE statement does not have to precede the other statements in the
program listing order; however, the PREPARE statement must precede the
DECLARE CURSOR statement and any DESCRIBE, DESCRIBE INPUT, OPEN,
FETCH, and CLOSE statements (for extended dynamic SQL statements, where
the cursor and statement names are stored in host variables). Foreign cursors do
not have this restriction.
Operation Description
PREPARE
statement-name
FROM
:host-variable
Dynamically compiles the SELECT
statement defining the cursor
Run DESCRIBE INPUT and DESCRIBE
statements. Retrieve information about the input and
output parameters of the prepared SQL
statement.
DECLARE
cursor-name
CURSOR
FOR
statement-name
Declares the dynamic cursor
OPEN
cursor-name
USING DESCRIPTOR
input-sqlda
Opens the cursor and gets parameter values
from the input data buffer in the program
FETCH
cursor-name
USING DESCRIPTOR
output-sqlda
Retrieves data and outputs column values to
output data buffer in the program
Loop until “not-found” condition occurs. Fetch data for all selected rows
CLOSE
cursor-name
Closes the cursor