Let’s Start Over from the Beginning
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
|
