Let's Start Over from the Beginning

Published: September 23, 2009

Hey, Ted:

We have RPG programs that use SQL cursors to sequentially retrieve data. If a program cancels and I call it again, the program resumes processing a cursor where it left off. I have to sign off and back on in order to restart from the top. Why doesn't the program start over from the beginning of the returned data set?

--Lynne

The behavior you're witnessing comes from three contributing factors, Lynne. First, your program was compiled to close the cursor when the activation group is destroyed. Second, your program is running in the default activation group. Third, you are not checking the open of the cursor to determine whether it succeeds or fails.

Fortunately, this is an easy problem to fix.

Let's look at a program like the ones Lynne is talking about.

Fqsysprt   o    f  132        printer
F
D zInput          ds                  inz
D  zCustNumber                   6p 0
D  zLastName                     8a
D  zInitials                     3a
D  zBalanceDue                   7p 2
D  zCreditDue                    7p 2

D  Ratio          s              3p 0

 /free
     exec sql
        declare c1 cursor for
              SELECT cusnum, lstnam, init, baldue, cdtdue
                FROM qiws/qcustcdt
               ORDER BY 1;

     exec sql
        open c1;

     dow '1';
        exec sql
           fetch c1 into :zInput;
        if sqlstt >= '02000';
           leave;
        endif;
        eval(h) Ratio = zCreditDue / zBalanceDue * 100;
        except pline;
     enddo;

     *inlr = *on;
 /end-free

Oqsysprt   e            pline       1
O                       zCustNumber
O                       zLastName        +   1
O                       zInitials        +   1
O                       zCreditDue    j  +   1
O                       zBalanceDue   j  +   1
O                       Ratio         j  +   1

Notice the eval within the do-while loop. I've included this line of code in order to make the program cancel.

Here's the result set from running the query. This is the data that the program reads as input.

CUSNUM   LSTNAM    INIT    BALDUE     CDTDUE
192837   Lee       F L     489.50        .50
389572   Stevens   K L      58.75       1.50
392859   Vine      S S     439.00        .00
397267   Tyron     W E        .00        .00
475938   Doe       J W     250.00     100.00
583990   Abraham   M T     500.00        .00
593029   Williams  E D      25.00        .00
693829   Thomas    A N        .00        .00
839283   Jones     B D     100.00        .00
846283   Alison    J S      10.00        .00
938472   Henning   G K      37.00        .00
938485   Johnson   J A   3,987.50      33.50

The first time I call the program, I get the following output, followed by escape message MCH1211, which tells me that the program attempted to divide by zero.

192837 Lee      F L       .50     489.50    0
389572 Stevens  K L      1.50      58.75    3
392859 Vine     S S       .00     439.00    0

The second time I call the program, I get the following output before I get another MCH1211.

475938 Doe      J W    100.00     250.00   40
583990 Abraham  M T       .00     500.00    0
593029 Williams E D       .00      25.00    0

Notice that the second run of the program did not begin with the first record of the result set.

The program was compiled to close the cursor when the activation group is destroyed. The system destroys named activation groups when the last program in the activation group ends. However, the default activation group, which is intended for use only with OPM programs, is destroyed only when the job ends. Therefore, the cursor remained open between invocations.

If I had checked the SQL status variable, SQLSTT, after the open during the first call, I would have found that SQLSTT contained a value of five zeros, meaning that the open succeeded. But SQLSTT would have had a value of 24502 after the open in the second call, meaning that the cursor was already open in the activation group.

So, how do I fix the problem?

The simplest fix is to change the program so that it closes the cursor when the module ends. I do that by adding the following code to the top of the calcs in the RPG program.

exec sql                         
   set option closqlcsr=*endmod; 

You should also consider running the program in a named activation group. You can easily do this by adding an H spec to the program.

 H dftactgrp(*no) actgrp(??????)

I'll leave it to you to think about what activation group name you should replace the questions marks with. If an RPG program is a standalone application, you can use *NEW.

Also, I encourage you to check the status of the open and to end the program gracefully if it has a value greater than or equal to 02000.

exec sql
   open c1;
if sqlstt >= '02000';
   // do something to handle the failed open

For one error-handling approach that works well, see the article Error Checking and Embedded SQL.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot