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.
Figure 1: Example of trapping a File Exception/Error.
Checking the file error
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 copy.
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.
P SndFileError B Export
D SndFileError PI N
D Status 5 0 Const
When Status = 01021;
// Referential Constraint
When Status = 01022 Or
Status = 01222;
When Status = 01023 Or
Status = 01024;
P SndFileError E
Figure 2: Checking the File Error.
What's in a Name?
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.
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.
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:
- MsgInfo is the structure where the retrieved information is placed. MsgInfoLen is the length of the structure used for MsgInfo.
- FormatName is the required format you want returned. RCVM0100 is used in this example (refer to Figure 6).
- CallStack and CallStackCtr are used in conjunction. CallStack is set to '*' and CallStackCtr is set to 2. This indicates that you want to retrieve messages from the message queue two up in the call stack (SndConstraintMsg is the current entry in the call stack - CallStack = '*'; SndFileError is one up in the call stack; the program that received the I/O error is two up in the call stack -- CallStackCtr = 2).
- MsgType, MsgKey and WaitTime indicate which message to read from the queue and how long to wait. The routine reads from the end of the queue to the start.
- MsgAction is what you want to do with the message on the queue when it has been read. The routine will remove it.
- ErrorForAPI is the standard API error structure.
D ReceiveMsg PR ExtPgm('QMHRCVPM') D MsgInfo 3000 Options(*VarSize) D MsgInfoLen 10I 0 Const D FormatName 8 Const D CallStack 10 Const D CallStackCtr 10I 0 Const D MsgType 10 Const D MsgKey 4 Const D WaitTime 10I 0 Const D MsgAction 10 Const D ErrorForAPI Like(APIError)
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 B Export
// 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 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:
- A loop reads messages from the program message queue two up in the call stack.
- For each message in the queue, data in the format RCVM0100 is placed in the Msgback data structure.
- If the message is one of the Referential Constraint messages, the constraint name is taken from the Message data (starting at position 177) and the required error message id is taken from the first seven positions of the constraint name.
- A generic message is sent if there is a problem with sending the error message or if none of the referential constraint messages are in the program message queue.
- The procedure ends once a message is sent.
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);
MsgId = %SubSt(Constriant:1:7);
SetMsgKey = MsgBack.MsgKey;
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?"