Special Files Can Do It All, Part 2

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.

–Jim Horn

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.

–Ted

RELATED STORIES

Special Files Can Do It All

Use Special Files to Access the IFS

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