Special Files Can Do It All, Part 2
June 6, 2007 Hey, Ted
I just wanted to tell you how much I appreciate your real world ideas and examples. They are the kinds of ideas that can really help smaller shops do the practical, bulletproof kinds of jobs we need to do. I have used your special files technique to replace some old System/36 #GSORT sales analysis programs. I know, it takes us a long time to convert, but some of these programs have not changed in 15 years.
We have S/36 OCL procedures that display a screen to ask for parameters, use #GSORT to create an address-out file using the parameters for selection and sorting, and call a program. We replaced the addrout sorts with special files.
Thanks again for the special-files tip. I think we are going to use it in conjunction with CGI to bring reports to the Web with only a few changes to the back-end code. Should be cool.
Thanks to Jim for sharing his use of special files with the rest of us. A lot of shops have old code that still serves its purpose, and that needs to be tweaked, but completely rewriting the application is out of the question.
I worked up an example based on Jim’s code. If you find any mistakes in it, don’t laugh too hard. It’s been almost 20 years since I wrote System/36 code.
Here’s the RPG II program that reads the sorted data and produces a report.
H SORT4 * FADDROUT IR F 3 3 T EDISK FQCUSTCDTIP F 60 DISK FQSYSPRT O F 132 OF PRINTER * E ADDROUT QCUSTCDT * IQCUSTCDTNS 01 I 1 60CUSTNO I 7 14 LNAME I 15 17 INIT I 37 38 STATE I 49 542BALANC * C** HILOEQ C BALANC COMP 500.00 25 25 OQSYSPRT H 01 1P O OR OF O 13 'CREDIT REPORT' O UDATE Y 25 O 32 'PAGE' O PAGE 4 36 O H 21 1P O OR OF O 5 'STATE' O 16 'CUSTOMER' O 43 'BAL DUE' O D 1 01 O STATE 2 O LNAME 16 O INIT 21 O CUSTNO 28 O BALANCJ 44 O 25 49 '***'
And here’s the OCL that sorts the data and calls the report program.
// LOAD #GSORT // FILE NAME-INPUT,LABEL-QCUSTCDT,DISP-SHRRM // FILE NAME-OUTPUT,LABEL-ADDROUT,RECORDS-100,RETAIN-J // RUN HSORTA 2A I C 48 48NEC1 FNC 37 38 // END // LOAD SORT4 // FILE NAME-ADDROUT // FILE NAME-QCUSTCDT,DISP-SHRRM // RUN
Jim modified the F specs to convert the RPG II program to RPG III, then used CVTRPGSRC to RPG IV. He removed the addrout file and defined his input file as a special file.
H dftactgrp(*no) actgrp(*new) FQCUSTCDT IP F 60 SPECIAL PGMNAME('SORT4SPEC') FQSYSPRT O F 132 PRINTER OFLIND(*INOF) * IQCUSTCDT NS 01 I 1 6 0CUSTNO I 7 14 LNAME I 15 17 INIT I 37 38 STATE I 49 54 2BALANC * C** HILOEQ C BALANC COMP 500.00 25 25 OQSYSPRT H 1P 01 O OR OF O 13 'CREDIT REPORT' O UDATE Y 25 O 32 'PAGE' O PAGE 4 36 O H 1P 2 1 O OR OF O 5 'STATE' O 16 'CUSTOMER' O 43 'BAL DUE' O D 01 1 O STATE 2 O LNAME 16 O INIT 21 O CUSTNO 28 O BALANC J 44 O 25 49 '***'
Jim kept his prompting logic. Instead of building sort specs and calling #GSORT, he used the input fields to create an SQL command, which he stuffed into the local data area. His special-file program, immediately below, retrieved the SQL command, from which it prepared and opened a cursor.
H option(*srcstmt: *nodebugio) dftactgrp(*no) actgrp(*caller) D Buffer e ds extname(QCUSTCDT) D LDA uds dtaara(*LDA) D SqlCommand 501 1000 C *ENTRY PLIST C PARM OPTION 1 C PARM STATUS 1 C PARM ERROR 5 0 C PARM BUFFER C C/EXEC SQL C+ set option closqlcsr=*endactgrp C/end-exec C C select C when option = 'O' C reset Buffer C in lda C C/EXEC SQL C+ prepare p1 from :SqlCommand C/end-exec C C/EXEC SQL C+ declare c1 cursor for p1 C/end-exec C C/EXEC SQL C+ open c1 C/end-exec C C when option = 'C' C/exec sql C+ close c1 C/end-exec C eval *inlr = *on C when option = 'R' C/exec sql C+ fetch c1 into :Buffer C/end-exec C endsl C C select C when sqlstt < '02000' C eval Status = '0' C when sqlstt = '02000' C eval Status = '1' C other C eval Status = '2' C endsl C move sqlstt error C return
I admire Jim’s creativity. In case anybody wants to play with this technique, I’ve put all the example code in a Zip file that you can download. The QCUSTCDT file is in library QIWS.
Thanks again to Jim for sharing his solution.