Using SQL Cursors

An SQL cursor is an object that handles the set of rows produced by a SELECT statement.

There are two kind of SQL cursors:
  • Sequential cursor - reads through the active set only once each time it is opened, by moving the cursor forward one row each time a row is requested.
  • Scroll cursor - fetches the rows of the active set in any sequence. To implement a scroll cursor.

The scope of a cursor is the module in which it is declared. Cursor names must be unique within a module.

In order to fetch rows into a program array, you want to use a forward-only cursor and do the following sequence of instructions:
  1. DECLARE ... FROM - the program declares a cursor for the STRING that contains the SQL SELECT statement.
  2. Use a FOREACH loop to fetch all rows into the program array. The cursor is automatically closed and the end of the loop (END FOREACH)
  3. FREE can be used when the cursor is no longer needed: Fhe program frees the cursor to release the storage area holding the cursor. Once a cursor has been freed, it must be declared again before it can be reopened.

The general sequence of program statements when using a scroll cursor for Query-by-Example is:

  1. DECLARE ... FROM - the program declares a cursor for the STRING that contains the SQL SELECT statement.
  2. OPEN - the program opens the cursor. The active set associated with the cursor is identified, and the cursor is positioned before the first row of the set.
  3. FETCH - the program fetches a row of data into host variables and processes it. The INTO clause must be used to copy the row values into program variables. With a SCROLL cursor, the FETCH instruction can use specif clauses such as PREVIOUS, FIRST, LAST, to fetch a specific row for a give position in the result set.
  4. CLOSE - the program closes the cursor after the last row desired is fetched. This releases the active result set associated with the cursor. The cursor can be reopened.
  5. FREE - when the cursor is no longer needed, the program frees the cursor to release the storage area holding the cursor. Once a cursor has been freed, it must be declared again before it can be reopened.

The cursor program statements must appear physically within the module in the order listed.