Check value of fields based on file

[rephia11]

I have 2 files. File1 contains two fileds. File2 has a lot of fields.
file1:
CheckF. Value
F2field6 Y
F2field20 N

CheckF contains field names of file2. Im gonna read through file1 and check the value of the field in file2 base on the listed fields in CheckF.

any idea how to achieve this?

[JonBoy]

RPG is a compiled language and so, like any other compiled language, needs to resolve field names at compile time. In your scenario you can't do that ... well ... more in a moment.

The simple option is embedded SQL. Build a statement based on the field name(s) from the file, prepare and execute the statement. Done.

If for some reason you can't do that then there are approaches that can be taken but they are messy. Three major options spring to mind.

1) If this is a task that just applies to this one set of files, i.e. the names of all possible fields are known, then the basic techniques I described in these articles will do the job. https://authory.com/JonParisAndSusan...r-Calculations and https://authory.com/JonParisAndSusan...e-Calculations are the two you need.

2) If a more generic approach is needed (i.e. any combination of files) then you need to use the systems's table views to extract data about the fields in the file being processed, read the record from the file as a "lump" (i.e. a program described file) and substring out the data based on the definition you extracted, etc. etc.

3) Another generic option is to write a program that writes a program to process the named fields. Compile that program and run it.

Personally I'd go for SQL - by far the simplest.

[rephia11]

Thanks JonBoy. SQL is actually the first thing that I though of when I got this problem. Unfortunately it was not allowed. So I worked on a different solution/approach.

For file1, I added two fields. POSITION and LENGTH

Then in the program I used a DS for file2
D DSfile2 E DS EXTNAME(file2) INZ


Using the POSITION and LENGTH in file1, I %SUBST DSfile2 to get the value of the field.

So the flow of the program is:
Chain on file2 to update the values on DSfile2
DOW read on file1 to get the fields that needs checking, the position and length
EVAL testfield = %subst(DSfile2:POSTION:LENGTH)
If testfield= Y

And then do the needed validation and so forth. It is working. I was just wondering if there is a cleaner and better way of doing it.

[JonBoy]

It makes no sense to not allow it ... but that's management for you.

Looks like you have done a hard coded version of my option 2. Personally I wouldn't go that route unless this was a one-time fix up program. If it is in ongoing use I'd be concerned about changes to the file and forgetting to change the hard-coded lengths and offsets.

Are you only ever testing the content of a single character field? If so there is not point in the EVAL - Just code the IF against the %Substr.

I'm guessing that the management won't let you do other sensible things like code in free-form. Sigh.

P.S. If you really are only testing for a single character then you'd have got better answers if you'd said so. The solutions I proposed assumed that the fields could be of any length and data type.

[Scott Klement]

I could suggest a method using pointers and system APIs.

However:

1. It would require around 10x as much code (maybe more) than the SQL method
2. It would involve pointers and memory manipulation that RPG programmers often don't understand, and results in hidden problems
3. It would be MUCH harder for the next guy to understand, change or maintain vs. SQL

You need to understand WHY you aren't allowed to use SQL. If its because other programmers might not understand it, using a much more complex method like pointers and system APIs will be every bit as bad.

[Scott Klement]

Also, please remember that different techniques (RPG, SQL, pointers, APIs) are all just tools. Like any tool, some are better for different types of jobs than others.

Imagine hiring a carpenter and telling him "This wood must be affixed using nails. However, management forbids the use of a hammer, you will have to pound in the nails using a saw."

[JonBoy]

Or your head - which is sadly the approach all too many ignorant shops take.

[rephia11]

SQL access to the file is monitored and too much will raise an alarm to the security company monitoring our system so..... SQL is a no no. even though we already discussed adding some rules for that file access monitoring so that alarm will not go off but they dont want it because it will set a precedence blah blah blah blah. management.... anyway im rambling here.

Yeah you're right that the that the approach I did is almost similar to your no2. The fields that needs to be checked are 1char (Y/N) conditions but I still added the LENGTH field just in case the management change their mind midway the project and decided to check some of the other fields.

Thanks. Thats a bit of an oversight on my part regarding the IF. yeah putting the subst directly will be better.

[rephia11]

I could suggest a method using pointers and system APIs.

However:

1. It would require around 10x as much code (maybe more) than the SQL method
2. It would involve pointers and memory manipulation that RPG programmers often don't understand, and results in hidden problems
3. It would be MUCH harder for the next guy to understand, change or maintain vs. SQL

You need to understand WHY you aren't allowed to use SQL. If its because other programmers might not understand it, using a much more complex method like pointers and system APIs will be every bit as bad.

As much as I would like to pick your brains and ask how to do that and try that approach, I dont have much time as the project is "urgent" and needs to be done by Wednesday and code review by Thursday and UAT by Friday.

But if you are willing to give me some "pointers" on how to approach this using pointers (see what i did there?), then I will be extremely thankful and will try it on my spare time outside of this project.

[Scott Klement]

But if you are willing to give me some "pointers" on how to approach this using pointers (see what i did there?), then I will be extremely thankful and will try it on my spare time outside of this project.

Ha. Pointers:

• There are APIs such as QUSLFLD or QDBRTVFD that can tell you all sorts of information about a file, including the names of the fields, positions within the input/output buffers, the key fields, etc.
• You could use the info from the above APIs to build an array in your program of fields that could be searched by field name to get the record buffer positions, data type, etc.
• There are routines in ILE C such as _Ropen(), _Rreadk, _Rreadn, et al that can be used to retrieve a record into a memory buffer, optionally using the equvalent of stuff like CHAIN, SETLL, READ, READE, READP, etc. This can be done even when the name of the file or size of the record aren't known at compile time.
• Using the field name, you can now look up its position using the array, and then with pointer math, get the position of that field within your record buffer.
• There are APIs such as _LBCPYNV and _CVTEFN that can be used to convert different numeric types, et al, using their pointer, data type, field position, length, decimal positions, etc.
• Or APIs like iconv() that can be used to convert character fields between CCSIDs, et al.

So you could package the whole thing up into a service program that enabled you to call it with variables for file name, field name, etc, and you could do I/O that way.

SQL access to the file is monitored and too much will raise an alarm to the security company monitoring our system so..... SQL is a no no. even though we already discussed adding some rules for that file access monitoring so that alarm will not go off but they dont want it because it will set a precedence blah blah blah blah. management.... anyway im rambling here.

Hmm... how do you monitor SQL access, separately from other access methods? I know a trigger could be used -- but how would it know you were using SQL? Not sure how this is possible.

If you're saying that this is monitored by monitoring a particular network server, such as the ODBC/JDBC network server... an RPG program doing SQL wouldn't use that.

[Peder Udesen]

My first thought was that you could have copied the file you wanted to test to QTEMP and then perform SQL statements on that.

Then I remembered reading about names-values information in Open Access and this might be the solution for you.
Take a look at this:
https://www.ibm.com/support/knowledg...nAccXmpWriteNv

Here you can find the fields name, type and value in the record.

[Barbara Morris]

Cool idea to use the names-values information from Open Access. But I don't think it would work. The names-values information doesn't have the exact data type for all the fields. Packed, zoned, and (binary) bindec are all just "decimal". https://www.ibm.com/support/knowledg...rnDatatype.htm

[Peder Udesen]

But the value for a decimal type is a string in the form returned by the %CHAR built-in function of RPG, for example "12.34" or "-5,67"
as stated in the manual. So it doesn't matter what format zoned, packed etc it is in.

So it should be relatively simple to compare two strings to see if they are equal or not equal.


And by the way according to the documentation the value subfield is a pointer to the value of the field - just want to be precise

[Peder Udesen]

@Barbara:
Many times the question is raised in the foras that the programmer have a field containing the
name of a field and another field with the value to be moved to the field in the first field.
Till now the answer has always been that it could only be done using SQL or another
language that is not compiled.

But using the names-values information from Open Access might even open the possibility
to implement this in RPG. Quite an interesting option.
You can find the corresponding field in the names-values information and alter the value.

[Barbara Morris]

Peter, about Open Access, I wasn't thinking straight yesterday. I think you are right! You could define the file as an open access output file, and write to the file with your data structure.