Reading and processing AS400 file using imbedded SQLRPGLE

Here's a brief example of how to select records in SQLRPGLE from an AS400 file, read through them, and perform processing on them.  It is a very powerful way of manipulating data once you get started with it.

So, the source code is below.  I put comments around the source code, in my attempt to explain how the code works.  If you have any questions, comment or drop me a line!

ffile1     uf a e           k disk                                   

 *  The file above  matches the file below in layout.  In this example we are copying from

 *  file2 to file1 based on the SQL query below                                                                

d FileDS        e ds                  extname(file2)              

 * file2 is the input file that we will be reading & processing through SQL                      

c/exec sql    

 * In SQLRPGLE, at the beginning of every SQL statement, the exec statement 

 * must be performed

c+  declare C1 cursor for                                   

c+  select * from file1 where                            

c+  field1 = 'AAA' and field2 = 'BBB'   

c/end-exec           

 * In the above SQL, we are declaring a cursor called C1.  We are selecting from file1 

 * where field1 & field 2 equals the specified values.                                             

c/exec sql 
c+ open C1                                                        

c/end-exec

* The above SQL command opens the cursor and gets it ready for use.                                                         

                                                                   

c/exec sql                                                         

c+  fetch C1 into :FileDS                                          

c/end-exec                                                         

* The above SQL command reads the first record of the series into the FileDS data structure

* defined at the top of the program.  This is based on file2, which has the same fields

* as file1.  Basically it is an empty copy of file1.

                                                                   

*  if sqlcod = 0, it means that we have a record to process.  Once sqlcod is not equal to 0, 

*  it means that the end of record set has been reached, and the do loop will end.                  

c                   dow       sqlcod = 0         

*  While inside the do loop, we can do whatever processing we want.  We have 

* selected the records using the SQL query, and are only processing the records
* that were selected.  In this case, I am writing to file1 because the record format
* and fields are identical to file2.  

c                   write     file1                               

c/exec sql     

* Next, we just fetch the next record in and go through the do loop again.                                                    

c+  fetch C1                                                       

c+  into :FileDS                                                   

c/end-exec                                                         

c                   enddo     

* When finished, we need to close the cursor.  Leaving the cursor open will cause

* undesirable results.  If the program is called again, and it attempts to open the 

* cursor while it was already opened previously, it will cause an error.                                     

c/exec sql                                               

c+  close C1                                             

c/end-exec                                               

*                 - Finally, when all is complete, set on *INLR and end the program                                                        

c                   eval      *inlr = '1'      

I hope someone finds this useful.  Once you get the hang of it, you can do amazing things!