To allow an application to retrieve a set of rows, SQL uses a mechanism called a cursor.
To help understand the concept of a cursor, assume that the database manager builds a result table to hold all the rows retrieved by executing a SELECT statement. A cursor makes rows from the result table available to an application, by identifying or pointing to a current row of this table. When a cursor is used, an application can retrieve each row sequentially from the result table until an end of data condition, that is, the NOT FOUND condition, SQLCODE +100 (SQLSTATE 02000) is reached. The set of rows obtained as a result of executing the SELECT statement can consist of zero, one, or more rows, depending on the number of rows that satisfy the search condition.
The steps involved in processing a cursor are as follows:
An application can use several cursors concurrently. Each cursor requires its own set of DECLARE CURSOR, OPEN, CLOSE, and FETCH statements.
See "Example Cursor Program" for an example of how an application can select a set of rows and, using a cursor, process the set one row at a time.
The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to be retrieved using a SELECT statement.
The application assigns a name for the cursor. This name is referred to in subsequent OPEN, FETCH, and CLOSE statements. The query is any valid select statement.
Figure 9 shows a DECLARE statement associated with a static SELECT statement.
Figure 9. Declare Cursor Statement
| Language | Example Source Code |
|---|---|
| C/C++ | EXEC SQL DECLARE C1 CURSOR FOR SELECT PNAME, DEPT FROM STAFF WHERE JOB=:host_var; |
| COBOL | EXEC SQL DECLARE C1 CURSOR FOR
SELECT NAME, DEPT FROM STAFF
WHERE JOB=:host-var END-EXEC.
|
| FORTRAN | EXEC SQL DECLARE C1 CURSOR FOR + SELECT NAME, DEPT FROM STAFF + WHERE JOB=:host_var |
| Note: | The placement of the DECLARE statement is arbitrary, but it must be placed above the first use of the cursor. |
The actions of a COMMIT or ROLLBACK operation vary for cursors, depending on how the cursors are declared.
If a cursor is determined to be read only and uses a repeatable read isolation level, repeatable read locks are still gathered and maintained on system tables needed by the unit of work. Therefore, it is important for applications to periodically issue COMMIT statements, even for read only cursors. For information on the repeatable read isolation level, see "Repeatable Read".
If an application completes a unit of work by issuing a COMMIT statement, all open cursors, except those declared using the WITH HOLD option, are automatically closed by the database manager.
A cursor that is declared WITH HOLD maintains the resources it accesses across multiple units of work. The exact effect of declaring a cursor WITH HOLD depends on how the unit of work ends.
If the unit of work ends with a COMMIT statement, open cursors defined WITH HOLD remain OPEN. The cursor is positioned before the next logical row of the result table. In addition, prepared statements referencing OPEN cursors defined WITH HOLD are retained. Only FETCH and CLOSE requests associated with a particular cursor are valid immediately following the COMMIT. UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF statements are valid only for rows fetched within the same unit of work. If a package is rebound during a unit of work, all held cursors are closed.
If the unit of work ends with a ROLLBACK statement, all open cursors are closed, all locks acquired during the unit of work are released, and all prepared statements that are dependent on work done in that unit are dropped.
For example, suppose that the TEMPL table contains 1000 entries. You want to update the salary column for all employees, and you expect to issue a COMMIT statement every time you update 100 rows.
EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY FROM TEMPL FOR UPDATE OF SALARY
EXEC SQL OPEN EMPLUPDT . . . EXEC SQL FETCH EMPLUPDT INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,
EXEC SQL UPDATE TEMPL SET SALARY = :newsalary WHERE CURRENT OF EMPLUPDT
You should include code in your application to detect and handle an SQLCODE -501 (SQLSTATE 24501), which can be returned on a FETCH or CLOSE statement if your application either:
If an application invalidates its package by dropping a table on which it is dependent, the package gets rebound dynamically. If this is the case, an SQLCODE -501 (SQLSTATE 24501) is returned for a FETCH or CLOSE statement because the database manager closes the cursor. The way to handle an SQLCODE -501 (SQLSTATE 24501) in this situation depends on whether you want to fetch rows from the cursor.
This sample program shows the SQL statements that define and use a cursor. The cursor is processed using static SQL.
Since REXX does not support static SQL, a sample is not provided. See "Example Dynamic SQL Program" for a REXX example that processes a cursor dynamically.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char pname[10];
short dept;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "Sample C program: CURSOR \n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: cursor [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff WHERE job='Mgr'
FOR UPDATE OF job;
EXEC SQL OPEN c1; (2)
CHECKERR ("OPEN CURSOR");
do {
EXEC SQL FETCH c1 INTO :pname, :dept; (3)
if (SQLCODE != 0) break;
printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
pname, dept );
} while ( 1 );
EXEC SQL CLOSE c1; (4)
CHECKERR ("CLOSE CURSOR");
EXEC SQL ROLLBACK;
CHECKERR ("ROLLBACK");
printf( "\nOn second thought -- changes rolled back.\n" );
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : CURSOR.SQC */
Identification Division.
Program-ID. "cursor".
Data Division.
Working-Storage Section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 pname pic x(10).
77 dept pic s9(4) comp-5.
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: CURSOR".
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL DECLARE c1 CURSOR FOR (1)
SELECT name, dept FROM staff
WHERE job='Mgr'
FOR UPDATE OF job END-EXEC.
EXEC SQL OPEN c1 END-EXEC. (2)
move "OPEN CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
EXEC SQL CLOSE c1 END-EXEC. (4)
move "CLOSE CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL ROLLBACK END-EXEC.
move "ROLLBACK" to errloc.
call "checkerr" using SQLCA errloc.
DISPLAY "On second thought -- changes rolled back.".
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :PNAME, :DEPT END-EXEC. (3)
if SQLCODE not equal 0
go to End-Fetch-Loop.
display pname, " in dept. ", dept,
" will be demoted to Clerk".
End-Fetch-Loop. exit.
End-Prog.
stop run.
program cursor
implicit none
include 'sqlenv.f'
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
character*10 pname
integer*2 dept
character*8 userid
character*18 passwd
EXEC SQL END DECLARE SECTION
character*80 errloc
print *, 'Sample FORTRAN program: CURSOR'
print *, 'Enter your user id (default none):'
read 101, userid
101 format (a8)
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO sample
else
print *, 'Enter your password :'
read 101, passwd
EXEC SQL CONNECT TO sample USER :userid USING :passwd
end if
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
EXEC SQL DECLARE c1 CURSOR FOR (1)
c SELECT name, dept FROM staff WHERE job='Mgr'
c FOR UPDATE OF job
EXEC SQL OPEN c1 (2)
errloc = 'OPEN'
call checkerr (sqlca, errloc, *999)
100 continue
EXEC SQL FETCH c1 INTO :pname, :dept (3)
if (sqlcode .ne. 0) goto 200
print *, pname, ' in dept. ', dept, ' will be demoted to Clerk.'
goto 100
200 EXEC SQL CLOSE c1 (4)
errloc = 'CLOSE'
call checkerr (sqlca, errloc, *999)
EXEC SQL ROLLBACK
errloc = 'ROLLBACK'
call checkerr (sqlca, errloc, *999)
print *, 'On second thought -- changes rolled back.'
EXEC SQL CONNECT RESET
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end