Data Manipulation with SQL › Using a Cursor › Fetching a Row

Fetching a Row

Opening the Cursor

Before the program can fetch cursor rows, it must open the cursor with an OPEN statement:

EXEC SQL
  OPEN EMP_SUM
END-EXEC.

How You Fetch a Row

The program fetches a row with a FETCH statement that names the cursor and includes an INTO clause that specifies the host variables to receive the fetched row:

EXEC SQL
  FETCH EMP_SUM
    INTO :EMP-ID,
         :MANAGER-ID :MANAGER-ID-I,
         :EMP-FNAME,
         :EMP-LNAME,
         :DEPT-ID
END-EXEC.

Cursor Position

Cursor position refers to a current position relative to a row of the cursor. When a FETCH statement is executed, the values assigned to the host variables are retrieved from the row that follows the cursor position.

When the program opens the cursor, cursor position is before the first row of the result table. When a row is fetched, the cursor position moves to that row and the column values for that row are moved into the host variables.

If another FETCH statement is executed while the cursor remains open, cursor position moves to the next row.

When There Are No More Rows

Cursor position advances row by row with each FETCH. If there is no row following the cursor position and a FETCH statement is executed, the DBMS returns 100 to SQLCODE. When this condition occurs, the program should end iterative logic for fetching cursor rows.

Testing for No More Cursor Rows

To test for no more cursor rows, test for SQLCODE = 100. If the test result is true, set a variable to indicate this condition, as shown in the use of END-FETCH in the following example.

Referencing a variable such as END-FETCH in subsequent program logic is recommended because the program controls the variable value, whereas the DBMS controls the value of SQLCODE.

 WORKING-STORAGE SECTION.

      77   END-FETCH    PIC X VALUE 'N'.
  .
  .
  .
 PROCEDURE DIVISION.
  .
  .
  .
     ***** Perform paragraph until no more cursor rows to process
      PERFORM FETCH-CURSOR UNTIL END-FETCH = Y.
  .
  .
  .
 FETCH-CURSOR.

      EXEC SQL
        FETCH EMP_SUM INTO
          EMP-ID,
          MANAGER-ID MANAGER-ID-I,
          EMP-FNAME,
          EMP-LNAME,
          DEPT-ID
      END-EXEC.

     ***** Test for no more cursor rows
      IF SQLCODE = 100 MOVE 'Y' TO END-FETCH.
  .
  .
  .

Closing a Cursor

The program can close a cursor with the CLOSE statement:

EXEC SQL
  CLOSE EMP_SUM
END-EXEC.

Automatic Closing of a Cursor

The COMMIT and ROLLBACK statements automatically close all open cursors used by the application program.

Invalid Cursor State

The DBMS returns an invalid cursor state condition and ignores the statement if the program issues:

• An OPEN statement for a cursor that is open
• A CLOSE statement for a cursor that is closed
• A FETCH statement for a cursor that is closed
• A FETCH statement when the cursor position is after the last row (which means that the DBMS already returned 100 to SQLCODE)

Summary of Cursor Management

This diagram summarizes how the program uses a cursor:

┌────────────┐
│            │
│  Declare   │
│  cursor    │
│            │
└──────┬─────┘
       │
       │
       │
┌──────▼─────┐
│            │
│  Open      │
│  cursor    │
│            │
└──────┬─────┘
       │
       ◄──────────┐
       │          │ Iterative
┌──────▼─────┐    │ logic
│            │    │
│  Advance   ├────┘
│  cursor    │
│            │
└──────┬─────┘
       │
       │
       │
┌──────▼─────┐
│            │
│  Close     │
│  cursor    │
│            │
└────────────┘