The Fortress Platform :)

When a SELECT statement retrieves multiple rows – called a Result Set, a cursor must be used for processing the individual records. The cursor can be thought of as a Buffer in memory that holds the result table and it also has a cursor that points to the current record being processed.

Working with a cursor involves the below steps:

1. Declare the cursor.
2. Open the cursor.
3. Fetch a record(may be done multiple times).
4. Close the cursor when the processing is completed.

DEMPREC  E   DS                 EXTNAME(EMPPF)

 /Free
   Exec SQL DECLARE @C1 CURSOR FOR SELECT * FROM EMPPF;
   Exec SQL OPEN @C1;

   Exec SQL FETCH @C1 INTO :EMPREC;
   
   DoW SQLCOD = *Zero;
     Exsr Ldsbf;
     Exec SQL FETCH @C1 INTO :EMPREC;
     If SQLCOD < *Zero;
       Exsr ErrSbr;
     EndIf;
   EndDo;

   Exec SQL CLOSE @C1;
   *Inlr = *ON;
 /End-Free

A cursor can be created using a prepared SQL statement as well. The below code snippet shows this:

 //Declare a String of length 512 bytes

DMySQL      S         512

 /Free
   MySQL = 'SELECT * FROM EMPPF;
   
   Exec SQL PREPARE STMT FROM :MYSQL;
   Exec SQL DECLARE CURSOR @C1 FOR STMT;
 
 /End-Free

The main aim of this technique is to improve efficiency. As mentioned earlier, the PREPARE statement is like a mini-compile and eliminates the need to prepare an SQL plan every time the statement is executed.

Updating the Data in the Result Set

In many cases, there will be a requirement to read records and update them as the program proceeds.  Before we can update a record in our Result table, the way it is declared must  be changed. A dynamic keyword should be added to the DECLARE statement.

In free format, it would be written as :

Exec SQL DECLARE @C1 DYNAMIC SCROLL CURSOR FOR STMT1;

Once the dynamic keyword is used, it can be used to update records as shown below

Exec SQL UPDATE EMPPF SET EMPNAME = 'HARI', EMPNUM = 230, 
EMAIL = 'HARI@GMAIL.COM' WHERE CURRENT OF @C1 ;

Once we begin to open the result set for updating, the records get locked and this can lead to record lock conflicts with other applications.

SQL relies on Transaction isolation level to control record locking.

The five different isolation levels available are:

1. No Commit
2. Read Uncommitted
3. Read Committed
4. Repeatable Read
5. Serializable