One of the advantages of SQL over native IO is that it allows you to handle sets of data rather than having to read and update each row individually. However, there can be cases where you want to handle your data line by line. To do this with SQL, you need to use a cursor.
I have a couple of examples below, both of which are written in fully free RPGLE. Going forward, all examples will be fully free because, as developers, we should all be putting the past behind us.
I am basing this on the previously mentioned Employees file, to which I have added an extra field named EMPVALID. This is numeric — a zero indicates an invalid employee number and a one indicates a valid employee number. Any number of validations or codes could be added, of course, but I want to keep things simple.
So first of all, here’s a simple program that will read and display each invalid employee.
**Free
// Employee records data structure
dcl-ds EMPDS extname('EMPLOYEES')
end-ds;
// Ensure the cursor is not already open
// This isn't really necessary here, but it is good practice
exec SQL close C1;
// Declare and open the cursor
exec SQL declare C1 cursor for
select EMPNUMBER, EMPNAME, EMPVALID
from EMPLOYEES
where EMPVALID = 0
for fetch only;
exec SQL open C1;
// Read the file
dou SQLCOD < 0 or SQLCOD = 100;
exec SQL fetch C1 into :EMPDS;
if SQLCOD >= 0 and SQLCOD <> 100;
dsply (EMPNAME);
endif;
enddo;
// Close the cursor and exit
exec SQL close C1;
*inlr = *on;
This is fine as far as it goes, but what we really want to do is check whether the employee numbers are valid and update the EMPVALID accordingly. For this, I will use the %check BIF.
**Free
// Employee records data structure
dcl-ds EMPDS extname('EMPLOYEES')
end-ds;
// A display field to get around the limitations of the DSPLY OpCode
dcl-s DISPLAY CHAR(52);
// And we need a constant, digits, containing the valid characters
dcl-c DIGITS '0123456789';
// Ensure the cursor is not already open
// This isn't really necessary here, but it is good practice
exec SQL close C1;
// Declare and open the cursor
// Note that the last clause is now a 'for update of...'
exec SQL declare C1 cursor for
select EMPNUMBER, EMPNAME, EMPVALID
from EMPLOYEES
where EMPVALID = 0
for update of EMPVALID;
exec SQL open C1;
// Read the file
dou SQLCOD < 0 or SQLCOD = 100;
exec SQL fetch C1 into :EMPDS;
if SQLCOD >= 0 and SQLCOD <> 100;
if %check(DIGITS: EMPNUMBER) > 0;
DISPLAY = %trim(EMPNAME) + ' is valid';
dsply (DISPLAY);
// And this is where we set EMPVALID = 1
// for the current record
exec SQL
update EMPLOYEES
set EMPVALID = 1
where current of C1;
endif;
endif;
enddo;
// Close the cursor and exit
exec SQL close C1;
*inlr = *on;
And this is the output:
DSPLY Bugs Bunny is valid DSPLY Elmer Fudd is valid DSPLY Speedy Gonzales is valid DSPLY Sylvester is valid
And looking at the file, I can see everything is updated, as below:
EMPNUMBER EMPNAME EMPVALID 00001 Bugs Bunny 1 OOOO2 Elmer Fudd 1 0000000003 Daffy Duck 0 -4 Speedy Gonzales 1 00005 Sylvester 1
I told you that Elmer Fudd was tricksey.
As a rule, updating sets of data is much more efficient and should certainly be preferred for any new development. However, there are plenty of programs out there for which the business rules are tucked away in some validation subroutine which has been written to process one line at a time. In these cases, being able to process line by line with an SQL cursor gives you a handy first step towards modernising the application.
I have also found cursors to be useful when building subfiles, but that’s a whole other post.