SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-34
SELECT Syntax Consideration for IN EXCLUSIVE
MODE and IN SHARE MODE
For dynamic SQL cursors, all statements referencing the cursor must be in the same
procedure where the cursor was declared.
A cursor remains open until it is closed. If a static SQL program exits a procedure with
an open cursor, procedures that execute later and that are within the same compilation
unit can still refer to the cursor. If a dynamic SQL cursor is opened and used in one call
to the procedure where it is declared, you can still use the cursor in subsequent calls
without opening the cursor again.
When to Initialize 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
in its WHERE clause, you must initialize the values of the host variables either before
you open the cursor with an OPEN statement or else in the USING clause of the
OPEN statement.
The SQL executor places the 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 type expected,
overflow or truncation errors can result when the cursor is opened.
•
If the variables are of the expected type but they contain values left from a previous
usage 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.
When to Close a Cursor. Only an explicit CLOSE statement or a FREE RESOURCES
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:
1. Close any open cursors before returning control to a requester.
2. If your program is a server and a TMF transaction was started in a requester, close
any cursors to release space used by the cursors and to free locks before returning
control to the requester.
SELECT Syntax Consideration for IN EXCLUSIVE MODE and IN
SHARE MODE
If the keyword IN is included in the EXCLUSIVE or SHARE MODE option and the
option follows a host variable, the statement gets a syntax error because of ambiguity
about the host variable. Omit the word IN in these situations.
For example, this statement gets a syntax error because NAME2 IN EXCLUSIVE is
interpreted as a host variable:
EXEC SQL SELECT A INTO :NAME FROM T
WHERE A > :NAME2 IN EXCLUSIVE MODE;