| Data Types | Data Structures | |
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
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.
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.
Note: Object oriented COBOL syntax is not supported by COBSQL.
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.
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.
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. See the section to be supplied for more details.
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.
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:
The SET SCROLLOPTION statement selects how the row membership of the results set of a cursor is determined.
With concurrent access, data would soon become unreliable without some kind of control. To activate concurrency control, use the SET CONCURRENCY 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.
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.
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 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 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 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
Both Oracle and Sybase 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.
Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Data Types | Data Structures | |