HP NonStop SQL/MP, Programming Manual

Page: 93 / 331

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-25
Using Foreign Cursors
A reference to a foreign cursor contains two parts, a procedure name and a cursor
name. This example references a foreign cursor,
list_by_partnum,
which is
declared in the procedure
update_inv:
update_inv.list_by_partnum
A foreign cursor reference can appear in an OPEN, FETCH, or CLOSE cursor
statement. It references a cursor that is declared in another procedure, which is not
necessarily in the same compile source file. References to a dynamic foreign cursor
are resolved at run time by the SQL Executor.
The cursor declaration and the PREPARE statement must be in the same procedure
so that the resolution between the PREPARE and the cursor declaration can occur to
detect whether a statement name has been prepared and to maintain proper
association between a procedure and a particular statement name.
This example declares a cursor
list_by_partnum
:
update_inv(void)
{
EXEC SQL BEGIN DECLARE SECTION;
struct parts_type
{
/* define host variables here */
} parts_rec;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE list_by_partnum CURSOR FOR
SELECT partnum, /* defined above */
partdesc,
price,
qty_available
FROM =parts
WHERE partnum >= :parts_rec.partnum
ORDER BY partnum
BROWSE ACCESS;
EXEC SQL PREPARE dynamic_statement FROM :hv_text;
}
These statements open, fetch, and close a foreign cursor
list_by_partnum
that are
declared in the procedure
update_inv
:
/* Loop while not EOF: */
void update_inv_total(void)
{
/* describe input and output here */
exec sql open update_inv.list_by_partnum using descriptor input-
sqlda;
}
{
/* describe input and output here */
exec sql fetch update_inv.list_by_partnum using descriptor
output-sqlda;