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.

Categories: Notes

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.