I am a big fan of Referential Integrity -- both Referential Constraints and Check Constraints. They remove a lot of code from my programs and provide the added benefit of ensuring that the constraint rules will be applied to any other applications that "need" to access my database.
But there is one annoying detail with using Referential Integrity in RPG programs. A constraint violation is notified as a file I/O error but it does not identify which constraint caused the problem.
So I would like to discuss a couple of methods for having your RPG program provide a meaningful message to the user when there is a constraint violation.
The first step is to trap the I/O error. Figure 1 shows my preferred method of handling I/O errors using a subprocedure. SndFileError returns an indicator which will be on if the error was not "handled" or off if it was. The status code of the file in error is passed as a parameter.
The SndFileError subprocedure, shown in Figure 2, sends an application message for any of the "recognized" status codes. If it is not one of the "recognized" status codes (the Other operation), a standard message is sent and the procedure returns a *On condition. SndErrMsg is a subprocedure that uses the QMHSNDPM API to send a message to a program message queue; this is a fairly standard routine, so I have not included it – just drop me a note if you want a
The noted difference is what the routine does with a referential constraint error (status of 1022 or 1222); instead of sending an error message a call is made to the subprocedure SndConstraintMsg.
Before looking at the SndConstraintMsg subprocedure, you need to give a little thought to the way you name your referential constraints and how you associate meaningful messages with the constraints. You have three choices:
- Have logic in your program that associates the constraint name with a message you want to send. This is the most inflexible way of doing it and should only be considered for testing purposes (if at all).
- Send the name of the constraint as the message text. Since you are not confined by ten character naming conventions, it is valid to have a constraint named "Customer has outstanding invoices. May not be deleted."
- If you are used to using your own message files for error messages in your application, why not have a naming convention that has the error message id as the first seven characters of the constraint name? This is my preferred method and it is the one I will describe; but the method gives you the means of handling the previous two options if required.
Figure 3 shows the definition of a Referential Constraint named ALL0021_Product_To_Category. The constraint ensures that a category code cannot be changed or deleted on the Category file if it has dependants on the Product file; also, a category code may not be defined on a Product record if the category does not exist on the Category file.
[TABLE]Figure 3: Defining a Referential Constraint in iSeries Navigator.
Figure 4 shows the definition of a Check Constraint named ALL0011_Landed_Cost_Too_High. The constraint ensures that the Landed Cost (i.e. the buying price) can never exceed the Selling Price.
[TABLE]Figure 4: Defining a Check Constraint in iSeries Navigator.
These two constraints are a simple example of the power of Referential Integrity. The constraints are enforced by the database manager, which means you cannot even use DFU to break the rules. And imagine what you don't have to code in your programs!
Now that you know what the constraints look like, let's get back to the SndConstraintMsg subprocedure.
Finding thenName of the constraint
Although the file I/O error may not identify the constraint, the required information is available. There is an earlier message in the program message queue of the program that received the I/O error that will identify the constraint. When you look at the messages second level message text you see the name of the constraint.
So SndConstraintMsg must read back up through the program message queue, retrieve the required constraint message and extract the name of the constraint.
The QMHRCVPM API is used to read through the program message queue. Figure 5 shows the prototype for calling QMHRCVPM. Note the following parameters:
Figure 5: Prototype for Calling QMHRCVPM.
Figure 6 shows the data definition portion of the SndConstraintMsg subprocedure. Data returned from the QMHRCVPM API is placed in the MsgBack data structure. The routine is primarily interested in the MsgId and MsgData fields. MsgId is used to identify the constraint error message you are looking for and MsgData will contain the constraint name starting in position 177.
P SndConstraintMsg...
P
B
Export
D SndConstraintMsg...
D
PI
// DS returned by QMHRCVPM for format RCVM0100
D MsgBack
DS
Qualified Inz
D ByteReturned
10I 0
D ByteAvail
10I 0
D MsgSeverity
10I 0
D MsgId
7
D MsgType
2
D MsgKey
4
D 7
D CCSIDInd
10I 0
D CCSIDReplace
10I 0
D LengthReturn
10I 0
D LengthAvail
10I 0
D MsgData
1024
D SetMsgKey S
4
D PrevMsgKey
S Like(SetMsgKey)
D Constraint S
50
D MsgId
S
7
Figure 6: D Specs for SndConstraintMsg.
Figure 7 shows the rest of the SndConstraintMsg subprocedure. The routine works as follows:
/Free
SetMsgKey = *ALLx'00';
Dow SetMsgKey
<> PrevMsgKey;
PrevMsgKey = SetMsgKey;
ReceiveMsg(
MsgBack : %size(MsgBack) : 'RCVM0100
:
'*' : 2 : '*PRV' : SetMsgKey : 0
:
'*SAME' : APIError);
If (MsgBack.MsgId
= 'CPF502D' Or
MsgBack.MsgId
= 'CPF502E' Or
MsgBack.MsgId = 'CPF502F' Or
MsgBack.MsgId
= 'CPF503A' Or
MsgBack.MsgId
= 'CPF503B');
Constraint
= %Subst(MsgBack.MsgData:177);
Monitor;
MsgId
= %SubSt(Constriant:1:7);
SndErrMsg(MsgId);
Return;
On-Error;
SndErrMsg('ALL9004');
Return;
EndMon;
EndIf;
SetMsgKey
= MsgBack.MsgKey;
EndDo;
SndErrMsg('ALL9004');
Return;
/End-Free
P SndConstraintMsg...
P
E
Figure 7: The SndConstraintMsg subprocedure.
In the end . . .
Referential Integrity is a powerful tool for us to use in our applications and provide a means of ensuring data integrity outside of our application.
Handling constraints in our programs may seem a little cumbersome at first but all of the handling can be placed in a couple of subprocedures and hidden from sight.
---------------------------
About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company.
He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted
industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"
