Wednesday, February 29, 2012

RPGLE Embedded SQL, update file

Another long hard day of chasing my tail. I am learning how to embed SQL in  RPGLE, it has become quite a challenge. I have had great success with looking up my issues and a few helpful comments to keep me going. This afternoon I got stuck with Fetch, DUH, Fetch into data structure. That hurt.

The problem:
Read records in file, update file field to identify type of sales. Then read the file again summarized quantity of item, color, sales type and write XML records to an IFS file.

Should I update the sales type in the same program? Say, one SQL subroutine to update and then another SQL subroutine to sum and write the same file? Or should I just have two separate programs?

After taking a break it took me five minutes to find the last piece of the puzzle

This program reads records, but will not allow updates or deletes. To make the program update-capable, a few simple changes are necessary.
First, the cursor declaration must remove the "For Fetch Only" restriction. To be able to update all fields in the record, use the following example:
  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile;
Or, you may restrict the updates to specific field(s):
  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile
                            For Update of Myfield;
When your program is ready to update or delete a record, you will use a special syntax of the SQL Update or Delete statement to associate it with the currently fetched record:
  Exec Sql Update Myfile
                  Set Myfield = Newvalue
           Where Current of Mycursor;
  Exec Sql Delete From Myfile
           Where Current of Mycursor;
Based on what I have now I believe I can finish tomorrow. My personal deadline is today but overall I am still on track.
It should be noted that no ethically-trained software engineer would ever consent to write a DestroyBaghdad procedure.  Basic professional ethics would instead require him to write a DestroyCity procedure, to which Baghdad could be given as a parameter.  ~Nathaniel S. Borenstein

1 comment:

  1. Hi friends,

    RPGLE program with five modules updating different set of files. Main module calls others each time a customer is being processed to change some specific detail in many files.