PreviousData Types Data StructuresNext

Chapter 4: Cursors

When you write code in which the result returned by a SELECT statement includes more than one row of data (the results set), you must declare and use a cursor.

A cursor is a mechanism that enables you to fetch rows of data one at a time and to perform updates and deletions at a specified position within a results set. The cursor is so called because it indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.

In the following example the SELECT statement is associated with the cursor Cursor1 using the DECLARE CURSOR statement and then the cursor is opened using the OPEN statement. This executes the SELECT statement. The FETCH statement retrieves the data for the current row from the columns au_fname and au_lname and places it in the host variables first_name and last_name. The program loops on the FETCH statement until no more data is available. Finally the cursor is closed.

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 the Object-Storage Section if you want to do this.


Notes:

COBSQL


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

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 from which the data is being selected. 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 - OpenESQL.

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

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:


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:

It is worth pointing out that there can be problems with deadlocks 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 allow 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.


Note:

COBSQL Oracle, Sybase and Informix allow cursors to be defined with a number of different clauses, for example FOR READ ONLY, FOR UPDATE etc. These clauses effect the isolation level of the cursor and how it will act 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.



Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.

PreviousData Types Data StructuresNext