SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-13
Using the SELECT Statement in Programs
FETCH listnext
INTO :part-no,
:part-desc,
:price,
:qty_available
END-EXEC.
EXEC SQL
CLOSE listnext
END-EXEC.
A row is returned each time the FETCH statement is executed. This example retrieves
all the rows with partnum values greater than the :hvkey value.
Initializing a Cursor
Opening a cursor causes the set of rows in the query result to be defined and ordered.
If a cursor SELECT statement contains host variables, you must initialize the values of
the host variables before you open the cursor with an OPEN statement.
The SQL executor copies input variables into its buffers when it opens the cursor. If
you do not initialize the variables before the OPEN statement, several things can
happen:
•
If the variables contain values that do not conform to the data types expected,
overflow or truncation errors can result when the cursor is opened.
•
If the variables are of the expected types but they contain values left from a
previous use of the program, these bad values are used as a starting point for
subsequent FETCH operations. As a result, the returned rows do not begin at the
expected location.
Closing a Cursor
A FREE RESOURCES, COMMIT WORK, ROLLBACK WORK, or an explicit CLOSE
statement closes an open cursor. As a general rule, you can leave cursors open to
save the overhead of reopening a cursor you plan to use again. In some cases,
however, you should explicitly close open cursors. In particular, when you use cursors
in Pathway applications, you should follow these rules:
•
Close any open cursor before returning control to a requester.
•
If your program is a server and a TMF transaction was started by a requester,
direct your program to close cursors, to release space used by the cursors and to
free locks before returning control to the requester.
Note. If a single-row SELECT statement is sufficient, do not use a cursor because it requires
three calls (OPEN, FETCH, and CLOSE) instead of one and therefore performs less efficiently
for the same result.