RPG: Determine which field changed in a file

50 pts.
Tags:
AS/400
RPG
I need to get which field changed in a file, instead of writing multiple (IF this field changed THEN DO...) for each field in each file, I was wondering if I could use some tool in RPG for doing that?


Software/Hardware used:
V6R1M0
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

I
want to create a dynamic insert statement to insert the audit record,
and have SQL be able to retrieve the contents of the field name in
the trigger buffer, and not have to hardcode every column name.

For
example, if I detect a change in a city field (name is ACCITY), I
want to load the contents of the “before” buffer’s ACCITY
field into the BEFOREDATA field in the inserted audit record, and
also load the contents of the “after” buffer’s ACCITY field
into the AFTERDATA field in the inserted audit record.

Do
you have any ideas on how to do that? Would a SQL (V6R1M0) descriptor help me
with this? I’m hoping there is an easy way to do this rather than
hardcoding a bunch of IF/THEN statements for each field in a file
that needs to have changes tracked.


Have you considered turning on the journaling feature? This sounds like it would suit your needs.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • BigKat
    you could set SQL triggers on each field if you're OS is level is high enough (unfortunately I don't remember at which level that is)
    9,460 pointsBadges:
    report
  • bvining
    An approach I have successfully used in the past is to use the List Fields (QUSLFLD)  API to find the fields defined within the record buffer of a file operation. This information includes field name, start position, length, data type, etc. The program can then compare the before and after image buffer locations for each field of the file to determine if there is a difference using a substring from X for Y bytes approach. No difference, move on to the next field definition. Difference found, log what you want and then move on to the next field definition.

    One thing I would suggest however is to have the trigger simply record the before and after images to a transaction file. Then have a second program process the transactions as previously described in batch. You probably don't want to do all the comparisons in the trigger itself as that would be inline to the main application processing.

    Since the API output can go to a *USRSPC you can just run it once, store the results in a permanent library, and re-use the space until the file definition changes -- at which time you just rerun the API.

    If you really need to track more than one file (I actually had to do some 600 files recently to detect and log individual field changes) then you may want to do a variant of what's described above in order to avoid having 600 *USRSPCs laying around...

    Bruce Vining
    7,070 pointsBadges:
    report
  • BigKat
    @bvining: ohhh, I like that idea 
    9,460 pointsBadges:
    report
  • bvining
    Don't be too surprised if you see an implementation of it in my next API Corner column :)
    7,070 pointsBadges:
    report
  • ToddN2000
    bvining: Very nice idea.. Will have to play around with it when I get a chance. We do not have journaling on and this seems like it may work for us on a few problematic files where users deny changing data.
    136,490 pointsBadges:
    report
  • azohawk

    bvining: where can I find that column (API Corner)?

    4,075 pointsBadges:
    report
  • ToddN2000
    You can find his blogs and articles at

    136,490 pointsBadges:
    report
  • bvining
    Unfortunately that site (brucevining.com) is way out of date due to time constraints.

    But to see my past articles (API Corner included) try here. The API Corner is a monthly article in the MC Press RPG Developer publication which you can subscribe to (I believe at no charge).

    The article using QUSLFLD should (at least that's my intention) be in the March issue (as February is already out).

    Bruce Vining

    7,070 pointsBadges:
    report
  • azohawk
    and apparenlty the old site has been hacked
    4,075 pointsBadges:
    report
  • ToddN2000
    Thanks for the update bvining. I had gotten and bookmarked the old link from your profile.
    136,490 pointsBadges:
    report
  • Luissimon
    Thanks, your answer help a lot
    50 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: