Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need an easier way to compare fields!!!!!!!!

Posted on 2007-08-07
7
Medium Priority
?
292 Views
Last Modified: 2012-06-21
We have developed a auditing file for or order billing system.

There are 3 main files where with a trigger on them that captures the before and after image of a record as well as some other tracking information such as User, File, Library.
Because I am captureing data from 3 different files I am storing the before and after image in 2 fields as one large string.

Now we want to create the inquiry/reporting over the audit data.

Here's my question.

I would like to be able to refer to fields as an index of some sort.  I need to compare the before and after image field by field to determine what fields changed during the transaction.  There are many fields in these files.

Is it possible, using a array, data structure  to refer to field names by some sort of index.

I am not thrilled about having to compare each field by name
Example:
If BeforeCustomer = AfterCustomer;
     Change = 'Y';
EndIf;

I would like to be able to do something like this

x = 1

DoU X = 56;                               // 56 Fields in record
     If BeforeFields(x) <> AfterFields(x);
              Change(x) = 'Y';
     EndIf;
    X +=1;
EndDo;

Any help is much appreciated


0
Comment
Question by:tcfrey
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Accepted Solution

by:
tliotta earned 2000 total points
ID: 19648263
tcfrey:

Are you looking to flag _which_ fields were changed? Your first example has [Change = 'Y';] which implies a single Change indicator. Your second example has [Change(x) = 'Y';] which implies a separate indicator for each field.

I'd guess that the second is the objective, otherwise you'd simply compare the two large structures.

Technically, there is no way to do what you ask, at least not in the way you're asking. However, you should be able to get a similar result in a number of ways. One way comes to mind immediately.

Rather than thinking in terms of "fields" in a record, think in terms of "offsets and lengths" in a buffer. Have an array of offsets and lengths. Use those in a comparison of substrings of the buffers. You might even be able to construct the offsets/lengths array dynamically if the buffers actually hold record images.

Maybe something like:

x = 1

DoU X = 56;                               // 56 Fields in record
     If %subst(BeforeBuf:offset(x):len(x)) <> %subst(AfterBuf:offset(x):len(x));
              Change(x) = 'Y';
     EndIf;
    X +=1;
EndDo;

Does that kind of match what you're looking for?

Tom
0
 
LVL 1

Author Comment

by:tcfrey
ID: 19648714
Tom,

Yes, my before and after fields hold a exact image of the record.

Actually I have begun to head in that direction.  

I was thinking of using the QUSLFLD API to retrieve a list of field names,field text, lengths and offsets for the correct file.  This could be eaisly used to drive the %Substr Functions for the compares.

Therefore, with the example you gave me.  I could use values returned from the API.
 OffSet = InBuffPos
 Len = FldLen

In the example from my orginal posting where Change(x) = 'Y'.  I could write the field name, text and before and after image of the field to a subfile or report.

What do you think?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 19649070
tcfrey:

QUSLFLD was behind my quick suggestion. The API list of fields provides everything needed for populating an array just as you mention. The command DSPFFD provides a similar capability but to an *OUTFILE. (The system catalog might be useful too.)

Given a choice of an API list or a file of field level info (or even writing code to populate your own *outfile from the API list), you should have a fairly easy time of developing almost any kind of process you can imagine.

Encapsulate parts in procedures. Generalize for multiple uses. Shouldn't be too far before you have parts that plug together to handle whatever.

One potential tricky part is "before and after image of the field to a subfile or report". Keep in mind that a %subst() is a character variable. It has no direct knowledge of items such as number of decimal positions. If you intend to write changed values to reports, you'll want to consider use of perhaps the edit APIs. You can pass in a pointer (by value) plus type, length, etc., and edit code or edit word. The result is an edited string.

You don't really even need to know much about the "field" itself except perhaps if it's numeric. Pass the attributes to the edit API and let it figure it out.

Tom
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 14

Expert Comment

by:daveslater
ID: 19652363
Hi
how are you wanting the audit to work?

will it be a report that is run in batch or is it a trigger program that will populate an existing file.

I am just thinking about the performance implications if the file is a high volume transaction file.

Dave
0
 
LVL 1

Author Comment

by:tcfrey
ID: 19657883
Dave:

I already have set up collecting the data I wish to capture.  I use a trigger to capture the transactions.

I am only storeing the before and after image when the trigger is fired.  Also some tracing data like, calling program, file, User, date and time.

I realize that there are possible issues with performance doing something like this but, in this case, i am willing to trade a little performance for the audit trail.

Our order billing system is rather complicated and there are many users in different departments that have a hand in the life cycle of a customer order.  When there is a problem with an order there is way too much finger pointing between departments as to who did what when.  (ever heard of that before?)

Trying to "Field" the data will not occr in the program that captures the data via the trigger.  Only in the inquiry program / reporting tools to view orders in question.

A larger issue will is the volume of transactions being collected.  File size will have to be monitored closely.

Thanks
Thomas
0
 
LVL 1

Author Comment

by:tcfrey
ID: 19657895
Dave,

I'm awarding you the points.  Since you at least confirmed my thought process.

Thanks again

Thomas
0
 
LVL 14

Expert Comment

by:daveslater
ID: 19661589
Hi
just a side issue, normally when I need to do a repatative comparison like the example; I tend to cut and past the fields into EXCEL and write a nasty VB program to generate the code, FTP this into a /Copy member then include that in the compile.

usually takes 5-10 minutes and you eliminate the typo's

Dave

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
"Day by day nothing changes but when u look back, everything is different". That quote precisely describes today’s digital era. For example, you may not have noticed the change, but Voice Search is now all around us.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question