Delete records from database(Physical/Logical) files

We have now learned to perform almost all operations on the physical files in ILE RPG. Now we will learn how to delete some record through an RPGIV program. Deleting a record in RPGLE is very much similar to the UPDATE action we learnt on the pervious page. Do delete a record we need to declare the specific file in UPDATE mode.

To delete a record from a physical or logical file we have to follow the following steps.

1. Declare the file in Update mode. You have already learnt this in the very chapter of file processing.
2. Read the specific record. For this we need to use the Combination of SETLL and READ or the CHAIN opcodes. Actually To delete a record a lock on the record is necessary and as you know from the previous page, all read operations on file declared in Update mode result in a lock on the read record (Off course unless the extender N has been used to instruct the program not to take any lock).
3. Perform the actual delete operation on the file.To actually delete the record from file we use the opcode DELETE. This opcode is very much similar to the UPDATE opcode. You need to specify the name of the record format of the file as factor 2 of DELETE opocode.

 **                                                                    
 ** Declare the logfile in UPDATE mode                                 
 **                                                                    
FFilename++IPEASF.....L.....A.Device+.Keywords+++++++++++++++++++++++++
FLOGFILE   UF   E             Disk                                     
FLOGDSPFDLTCF   E             WorkStn                                  
 **                                                                    
 ** Read the file                                                      
 **                                                                    
CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result++++++++Len++D+HiLoEq
C     1             SetLl     LOGFILE                                  
C                   Read      LOGFILE                                90
 **                                                                    
C                   DoW       Not (*INKA Or *In90)                     
 **                                                                    
C                   Eval      $Num1 = num1                             
C                   Eval      $Num2 = num2                      
C                   Eval      $Sum = $Num1 + $Num2              
 **                                                             
 ** Display screen. Pressing F5 will delete the dislayed record 
 **                                                             
C                   ExFmt     LOG02                             
 **                                                             
 ** Ensure F5 has been pressed                                  
 **                                                             
C                   If        *InKe = *On                       
 **                                                             
 ** Delete the record                                           
 **                                                             
C                   Delete    LOGPF01                           
C                   EndIf                                       
 **                                                                    
 ** Read next record                                                   
 **                                                                    
C                   Read      LOGFILE                                90
 **                                                                    
C                   EndDo                                              
 **                                                                    
C                   Eval      *InLr = *On                              
C                   Return                                             

//The sources of the physical and display files

 ** The physical file
A               R LOGPF01                  
A                 USER          10A        
A                 NUM1          15P 0      
A                 NUM2          15P 0      
A               K USER                     

AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++
A                                      DSPSIZ(24 80 *DS3)              
A          R LOG02                                                     
 ** FUNCTION KEY DEFINED                                               
A                                      CF01                            
A                                      CF05                            
 ** SCREEN FIELDS AND LABELS
A                                  2  2'Sum Of Two Numbers'      
A                                      COLOR(WHT)                
A                                      DSPATR(UL)
A                                  5  2'Enter two Numbers and Press Enter
A                                      to get their sum.'              
A                                  7  2'Enter First Number :'          
A            $NUM1         10  0B  7 23                                
A                                  8  2'Enter Second Number:'          
A            $NUM2         10  0B  8 23                                
A                                 10 23'-----------'                   
A                                 11 17'Sum ='                         
A            $SUM          11  0O 11 23COLOR(WHT)                      
A            $SUM          11  0O 11 23COLOR(WHT)                
A                                 23  2'F1=Exit'                 
A                                      COLOR(BLU)    
A                                 23 13'F5=Delete'               
A                                      COLOR(BLU)                          
A          R LOG01                                               
A                                      CF01                      
A                                 11 19'Please Enter Your Name :'
A            $USER         10   B 11 45                          
A                                 23  3'F1=Exit'                 
A                                      COLOR(BLU)                

The opcode DELETE can also take the keylist as factor 1. In this case the record is directly deleted from the physical file wituout and read operation! Using DELETE opcode can be very useful and easier to understand when you have to just delete the records. You are not interested in the values of records. The following example will illustrate this further.

 **                                                                
 ** Declare the logfile in UPDATE mode                             
 **                                                                
FFilename++IPEASF.....L.....A.Device+.Keywords+++++++++++++++++++++
FLOGFILE   UF   E           K Disk                                 
FLOGDSPFDLTCF   E             WorkStn                              
 **                                                                
 ** Display screen to take user name as input.                     
 **                                                                
CL0N01Factor1+++++++Opcode&ExtExtended-factor2+++++++++++++++++++++
C                   DoW       Not *INKA                            
 **                                                                
 ** Display screen. Pressing F5 will delete the dislayed record    
 **                                                                
C                   ExFmt     LOG01                                
 **                                                          
 ** Delete record if Enter has been pressed                  
 **                                                          
C                   If        *InKa = *Off                   
 **                                                          
 ** Delete the record                                        
 **                                                          
C     $User         Delete    LOGPF01                        
 **                                                          
C                   Eval      $User = *Blanks                
C                   EndIf                                    
 **                                                          
C                   EndDo                                    
 **                                                          
C                   Eval      *InLr = *On                    
C                   Return                                   

The above example deletes one record corresponding to the user. Notice that the LOGFILE has been accessed using a key $User. This is because the above delete operation is same as CHAIN + DELETE. So, guess which indicator would be set off on successful DELETE? This is important to know as you will probably build your looping condition when you have to delete all the records corresponding to "$User".

The display file and Physical file remain the same as in the previous example.

RPGLE program will end with error if DELETE operation is used without previous successful READ. If we have specified the keylist as factor 1, then the program might end with error, if the record we want to delete is already locked to some other program.

To handle such type of errors we use the extender 'E' to trap the error on DELETE. The opcode with extender becomes DELETE(E). Once the error is trapped we know that error occured by evaluating %Error built in function. This returns *ON or '1' in case of error.

Alternatively we can specify an indicator at low position.

This completes the basic RPG-ILE tutorial! You might wonder that basic RPGIV tutorial is complete and we have not yet processed any subfile. This is because, considering the importance of subfiles we will learn about subfiles in a separate book!