Data Types

Data Structures

Chapter 4: Cursors

Where the result returned by a SELECT statement includes more than one row of data, that is the results set, you must declare and use a cursor. A cursor indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.

A cursor enables you to:

Fetch rows of data one at a time
Perform updates and deletions at a specified position within a results set.

The example below demonstrates the following sequence of events:

The DECLARE CURSOR statement associates the SELECT statement with the cursor Cursor1.
The OPEN statement opens the cursor, thereby executing the SELECT statement.
The FETCH statement retrieves the data for the current row from the columns au_fname and au_lname and places the data in the host variables first_name and last_name.
The program loops on the FETCH statement until no more data is available.
The CLOSE statement closes the cursor.

EXEC SQL DECLARE Cursor1 CURSOR FOR
   SELECT au_fname, au_lname FROM authors
END-EXEC
...
EXEC SQL
   OPEN Cursor1
END-EXEC
...
perform until sqlcode not = zero
   EXEC SQL
      FETCH Cursor1 INTO :first_name,:last_name
   END-EXEC
      display first_name, last_name
end-perform
...
EXEC SQL
   CLOSE Cursor1
END-EXEC

4.1 Declaring a Cursor

Before a cursor can be used, it must be declared. This is done using the DECLARE CURSOR statement in which you specify a name for the cursor and either a SELECT statement or the name of a prepared SQL statement.

Cursor names must conform to the rules for identifiers on the database that you are connecting to, for example, some databases do not allow hyphens in cursor names.

EXEC SQL
   DECLARE Cur1 CURSOR FOR
      SELECT first_name FROM employee WHERE last_name = :last-name
END-EXEC

This example specifies a SELECT statement using an input host variable (:last-name). When the cursor OPEN statement is executed, the values of the input host variable are read and the SELECT statement is executed.

EXEC SQL
   DECLARE Cur2 CURSOR FOR stmt1
END-EXEC
...
move "SELECT first_name FROM emp WHERE last_name=?" to prep.
EXEC SQL
   PREPARE stmt1 FROM :prep
END-EXEC
...
EXEC SQL
   OPEN Cur2 USING :last-name
END-EXEC

In this example, the DECLARE CURSOR statement references a prepared statement (stmt1). A prepared SELECT statement can contain question marks (?) which act as parameter markers to indicate that data is to be supplied when the cursor is opened. The cursor must be declared before the statement is prepared.

COBSQL
A cursor can be declared in either the data division or the procedure division of your program. The DECLARE CURSOR statement does not generate any code but if a cursor is declared within the procedure division, COBSQL generates an animation breakpoint for the DECLARE CURSOR statement.

4.1.1 Object Oriented COBOL Syntax

Within an Object Oriented (OO) program, you can declare a cursor anywhere that it is valid to declare a data item. Cursors are local to the object that they are opened in, that is, two instances of an object opening the "same" cursor each get their own cursor instance.

You can open a cursor in one method, fetch it in a second and close it in a third but it must be declared in object-storage if you want to do this.

Notes:

COBSQL

Object oriented COBOL syntax is not supported by COBSQL.
Some versions of the Informix precompiler can generate incorrect code if a cursor is declared within the Working-Storage section. We therefore recommend that you only declare cursors in the procedure division when using INFORMIX.

4.2 Opening a Cursor

Once a cursor has been declared, it must be opened before it can be used. This is done using the OPEN statement, for example:

EXEC SQL
   OPEN Cur1
END-EXEC

If the DECLARE CURSOR statement references a prepared statement that contains parameter markers, the corresponding OPEN statement must specify the host variables or the name of an SQLDA structure that will supply the values for the parameter markers, for example:

EXEC SQL
   OPEN Cur2 USING :last-name
END-EXEC

If an SQLDA data structure is used, the data type, length, and address fields must already contain valid data when the OPEN statement is executed.

COBSQL
When a cursor is opened, no locks are applied to the tables the data is being selected from.

COBSQL
An Oracle database allows a cursor to be re-opened before it is closed such that the SELECT statement is re-evaluated. If the program has been compiled in ANSI mode, however, re-opening the cursor before it has been closed generates an error. For more information on the MODE precompiler directive, refer to the Programmer's Guide to the ORACLE Precompilers.

4.3 Using a Cursor to Retrieve Data

Once a cursor has been opened, it can be used to retrieve data from the database. This is done using the FETCH statement. The FETCH statement retrieves the next row from the results set produced by the OPEN statement and writes the data returned to the specified host variables (or to addresses specified in an SQLDA structure). For example:

perform until sqlcode not = 0  
   EXEC SQL
      FETCH Cur1 INTO :first_name
   END-EXEC
   DISPLAY 'First name: 'fname
   DISPLAY 'Last name : 'lname
   DISPLAY SPACES
end-perform

When the cursor reaches the end of the results set, a value of 100 is returned in SQLCODE in the SQLCA data structure and SQLSTATE is set to "02000".

As data is fetched from a cursor. locks can be placed on the tables the data is being selected from. For more information about the different types of cursors, the locked data they can read and the locks they put on data, see the section Cursor Options below.

COBSQL
The ORACLE precompiler directive, MODE, affects the value put into SQLCODE when no data is found. For more information on the use of the MODE precompiler directive, refer to the Programmer's Guide to the ORACLE Precompilers.

4.4 Closing a Cursor

When your application has finished using the cursor, it should be closed using the CLOSE statement. For example:

EXEC SQL
   CLOSE Cur1
END-EXEC

Normally, when a cursor is closed, all locks on data and tables are released. If the cursor is closed within a transaction, however, the locks may not be released.

COBSQL
The ORACLE precompiler directive, MODE, affects what happens to a cursor when either the commit or rollback command is used. For more information on the use of the precompiler directive MODE, refer to the Programmer's Guide to the ORACLE Precompilers.

COBSQL
When a cursor is closed, the ORACLE client may deallocate the memory and resources associated with the cursor. The following precompiler optins control the deallocatin of cursors: HOLD_CURSOR, MAXOPENCURSORS and RELEASE_CURSOR. For more information on the use of the precompiler directives, refer to the Programmer's Guide to the ORACLE Precompilers.

4.5 Cursor Options

OpenESQL
The information given here on cursor options is only applicable to OpenESQL.

The behavior and performance of cursors can be tuned using the following embedded SQL statements:

Embedded SQL Statement	Description
SET SCROLLOPTION	Selects how the row membership of the results set of a cursor is determined.
SET CONCURRENCY	Activate concurrency control. (With concurrent access, data would soon become unreliable without some kind of control.). Use this statement before the cursor is opened.

Note: SET SCROLLOPTION and SET CONCURRENCY are part of the Extended SQL Syntax and are not supported by all ODBC drivers.

4.6 Positioned UPDATE and DELETE Statements

Positioned UPDATE and DELETE statements are used in conjunction with cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the corresponding cursor.

EXEC SQL
   UPDATE emp SET last_name = :last-name
      WHERE CURRENT OF Cur1
END-EXEC

This will update last_name in the row that was last fetched from the database using cursor Cur1.

EXEC SQL
   DELETE emp WHERE CURRENT OF Cur1
END-EXEC

This example will delete the row that was last fetched from the database using cursor Cur1.

OpenESQL
With some ODBC drivers, cursors that will be used for positioned updates and deletes must include a FOR UPDATE clause. Note that positioned UPDATE and DELETE are part of the Extended ODBC Syntax and are not supported by all drivers.

COBSQL
With COBSQL, cursors that will be used for positioned updates and deletes must include a FOR UPDATE clause.

4.7 Using Cursors

Cursors are very useful for handling large amounts of data but there are a number of issues which you should bear in mind when using cursors, namely: data concurrency, integrity and consistency.

To ensure the integrity of your data, a database server can implement different locking methods. Some types of data access do not acquire any locks, some acquire a shared lock and some an exclusive lock. A shared lock allows other processes to access the data but not update it. An exclusive lock does not allow any other process to access the data.

When using cursors there are three levels of isolation and these control the data that a cursor can read and lock:

Level Zero
Level zero can only be used by read-only cursors. At level zero, the cursor will not lock any rows but may be able to read data that has not yet been committed. Reading uncommitted data is dangerous (as a rollback operation will reset the data to its previous state) and is normally called a "dirty read". Not all databases will allow dirty reads.
Level One
Level one can be used by read-only cursors or updateable cursors. With level one, shared locks are placed on the data unless the FOR UPDATE clause is used. If the FOR UPDATE clause is used, exclusive locks are placed on the data. When the cursor is closed, the locks are released. A standard cursor, that is a cursor without the FOR UPDATE clause will normally be at isolation level one and use shared locks.
Level Three
Level three cursors are used with transactions. Instead of the locks being released when the cursor is closed, the locks are released when the transaction ends. With level three it is usual to place exclusive locks on the data.

It is worth pointing out that there can be problems with "dead-locks" or "deadly embraces" where two processes are competing for the same data. The classic example is where one process locks data A and then requests a lock on data B while a second process locks data B and then requests a lock on data A. Both processes have data that the other process requires. The database server should spot this case and send errors to one, or both, processes.

COBSQL
Oracle, Sybase and Informix allallow an application to set the isolation level of the cursor and their documentation discusses the types of locks that are applied and how they work. Their documentation also discusses the physical level that the data is locked at. This can be a single row, a set of rows (that is, the page level), or the whole table. Care should be taken when using cursors that scan multiple tables, or tables that are used by most processes, as this will reduce the accessibility of the locked data.

Notes:

COBSQL Oracle, Sybase and Informix enable cursors to be defined with a number of different clauses e.g. FOR READ ONLY, FOR UPDATE etc. These clauses effect the isolation level of the cursor and how it acts when involved in transaction processing. For more information on the effect of these difference clauses, refer to the SQL reference book supplied with your database.

Data Types

Data Structures