Tip

Name that constraint

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.

    Write(E) ProductR;
    If %Error;
        If SndFileError(%Status(Product1));
            Exsr *PSSR;
        EndIF;
        Return *On;
    EndIF;

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

   /Free

        Select;

            // Duplicate
        When Status = 01021;
            SndErrMsg('ALL9003');

            // Referential Constraint
        When Status = 01022  Or
            Status = 01222;
            SndConstraintMsg();

            // Trigger
        When Status = 01023  Or
            Status = 01024;
            SndErrMsg('ALL9005');

            // Other
        Other;
            SndErrMsg('ALL9006');
            Return *On;
        EndSL;

        Return *Off;

   /End-Free
  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:

  1. 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).
  2. 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."
  3. 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.
  4. 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 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:

    • 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.

      /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?"


    This was first published in September 2004

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

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

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.