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.
Declare Cursor Statement shows a DECLARE statement associated with a static SELECT statement.
EXEC SQL DECLARE C1 CURSOR FOR SELECT PNAME, DEPT FROM STAFF WHERE JOB=:host_var;
#sql iterator cursor1(host_var data type);
#sql cursor1 = { SELECT PNAME, DEPT FROM STAFF
WHERE JOB=:host_var };
EXEC SQL DECLARE C1 CURSOR FOR
SELECT NAME, DEPT FROM STAFF
WHERE JOB=:host-var END-EXEC.
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.
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.
When an application closes a cursor using the WITH RELEASE option, DB2 attempts to release all READ locks that the cursor still holds. The cursor will only continue to hold WRITE locks. If the application closes the cursor without using the RELEASE option, the READ and WRITE locks will be released when the unit of work completes.
This sample program shows the SQL statements that define and use a cursor. The cursor is processed using static SQL. The sample is available in the following programming languages:
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 "utilemb.h"
EXEC SQL INCLUDE SQLCA;
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;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 3)
{
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("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)
EMB_SQL_CHECK("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)
EMB_SQL_CHECK("CLOSE CURSOR");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
printf( "\nOn second thought -- changes rolled back.\n" );
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : CURSOR.SQC */
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator CursorByName(String name, short dept) ;
#sql iterator CursorByPos(String, short ) ;
class Cursor
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
public static void main(String argv[])
{ try
{ System.out.println (" Java Cursor Sample");
String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname
Connection con = null;
// Set the connection
if (argv.length == 0)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2)
{ String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else
{ throw new Exception("\nUsage: java Cursor [username password]\n");
}
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
// Enable transactions
con.setAutoCommit(false);
// Using cursors
try
{ CursorByName cursorByName;
CursorByPos cursorByPos;
String name = null;
short dept=0;
// Using the JDBC ResultSet cursor method
System.out.println("\nUsing the JDBC ResultSet cursor method");
System.out.println(" with a 'bind by name' cursor ...\n");
#sql cursorByName = {
SELECT name, dept FROM staff WHERE job='Mgr' }; (1)
while (cursorByName.next()) (2)
{ name = cursorByName.name(); (3)
dept = cursorByName.dept();
System.out.print (" name= " + name);
System.out.print (" dept= " + dept);
System.out.print ("\n");
}
cursorByName.close(); (4)
// Using the SQLJ iterator cursor method
System.out.println("\nUsing the SQLJ iterator cursor method");
System.out.println(" with a 'bind by position' cursor ...\n");
#sql cursorByPos = {
SELECT name, dept FROM staff WHERE job='Mgr' }; (1) (2)
while (true)
{ #sql { FETCH :cursorByPos INTO :name, :dept }; (3)
if (cursorByPos.endFetch()) break;
System.out.print (" name= " + name);
System.out.print (" dept= " + dept);
System.out.print ("\n");
}
cursorByPos.close(); (4)
}
catch( Exception e )
{ throw e;
}
finally
{ // Rollback the transaction
System.out.println("\nRollback the transaction...");
#sql { ROLLBACK };
System.out.println("Rollback done.");
}
}
catch( Exception e )
{ System.out.println (e);
}
}
}
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.