SQL/MP Programming Manual for COBOL

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for COBOL—529758-003

4-23

CLOSE Statement

The CLOSE statement closes an open SQL cursor. After the CLOSE statement

executes, the result table established by the OPEN statement no longer exists. To use

the cursor again, you must reopen it using an OPEN statement.

A program does not require special authorization to execute a CLOSE statement.

A CLOSE statement must be within the scope of all other SQL statements, including

the DECLARE CURSOR, OPEN, FETCH, INSERT, DELETE, and UPDATE

statements, that refer to the cursor.

This CLOSE statement closes the LIST-BY-PARTNUM cursor:

...

EXEC SQL CLOSE LIST-BY-PARTNUM END-EXEC.

Only an explicit CLOSE statement (or a FREE RESOURCES statement) closes an

open SQL cursor. The CLOSE operation releases the resources used by the cursor

and frees any locks the cursor holds. If you are planning to reuse a cursor later in your

program, you can usually leave it open to save the overhead of opening it. However, if

your program is a Pathway server, always close an open cursor before returning

control to the requester, especially if the requester initiated a TMF transaction.

Using Foreign Cursors

Foreign cursors are cursors that are not declared in the program or procedure in which

they are referenced. Foreign cursors can be static or dynamic.

A reference to a foreign cursor contains two parts, a procedure name part and a cursor

name part. This example refers to a foreign cursor named LIST-BY-PARTNUM which is

declared in the procedure 3000-UPDATE-INVENTORY:

3000-UPDATE-INVENTORY.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 source file. References to a dynamic foreign cursor are

resolved at run time by the SQL executor.

The prepare and dynamic cursor declarations 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 or not, and to maintain proper

association between a procedure and a particular statement name.