Volume 10, Number 6 -- February 17, 2010

Handling Constraint Violations in RPG

Published: February 17, 2010

by Paul Tuohy

Constraints have been around for a long time but have not quite made it into every programmer's tool kit. This is partly explained by the fact that implementing constraints in an existing application can be tricky--but it doesn't explain why constraints are not used extensively in new applications.

On a side note, the lack of constraints on a database is one of the reasons I have heard why data should be moved/copied from the i to other database servers, such as SQL server and Oracle, for such things as data warehousing. To a "pure database person," a lack of constraints means it is not a database.

Hopefully, this article can get rid of one of the hurdles to including constraints in your applications: how to handle a constraint violation in an RPG program.

But first, a quick refresher.

What are Constraints?

Constraints are a function of referential integrity, where the database manager ensures the logical consistency of data values between tables (files) and the validity of data relationships, based on rules that you establish.

Impressive as that sounds, it is something you are already doing, except that you are doing it in your application programs. You cannot delete the customer if there are dependant invoices on the invoice file. You do not employ people under the age of 16. Such rules are implemented through logic in your RPG programs.

But what happens as your applications expand and data becomes accessible outside of the traditional RPG application? It becomes imperative that these rules be consistent across all interfaces. What better way to implement them than through the database manager?

Although there are a host of commands for handling constraints--ADDPFCST, CHGPFCST, RMVPFCST, WRKPFCST, EDTCPCST, and DSPCPCST)--by far the easiest way of handling constraints is through the Database function in Navigator. The definition of a table offers three tabs: Key Constraints, Foreign Key Constraints, and Check Constraints, as shown in Figure 1.

Figure 1: Defining a table in Navigator.

Key constraints allow you to define one primary key and multiple unique keys for a table; the end result is an access path but there is no corresponding logical file. A table requires at least one key constraint to be defined in order to be accessible as a parent in a Foreign Key constraint. Figure 2 shows the definition of a Primary Key Constraint.

Figure 2: Defining a Primary Key Constraint.

Foreign Key Constraints let you define a constraint between two tables--a dependant and a parent. The parent table must have a primary constraint defined for it.

Figure 3 shows the definition of a Foreign Key Constraint that identifies a dependency between the DEPARTMENT table and the EMPLOYEE_DETAILS table. The dependency means that a Department code may not be deleted or renamed if there are dependencies on the Employee Details table.

Figure 3: Defining a Foreign Key Constraint.

A Check Constraint allows you to define validation for one or more columns in a table. Basically, a Check Constraint is an SQL Where Clause that is used as a rule for data being placed in a row. Figure 4 shows the definition of a Check Constraint that ensures that the number of years difference between the Date of Birth and Date Joined is greater than or equal to 16. (That is, we do not employ people under the age of 16.)

Figure 4: Defining a Check Constraint.

You may have noticed that all of the constraints have long names. Constraints are not objects and, therefore, are not restricted by 10 character names.

Trapping the I/O Error

So how does your RPG program check for a constraint violation? The same way it checks for any database violation--by trapping an I/O error on the file operation. Of course the problem with this technique is that it simply tells us that there was a constraint violation--it doesn't tell us which of the myriad constraints caused the problem.

Code 1 shows a snippet of code that writes a record to the Employee Details table. If an I/O error is detected a call is made to the sendFileError() subprocedure, passing the status code of the Employee Details table. sendFileError()returns *off if it "handled" the error or *on if it didn't.

    write(E) emp_DetlR;
    if %error;
       if sendFileError(%status(emp_Detl));
          exsr *PSSR;
       return *On;

Code 1: Trapping an I/O Error.

Checking the File Error

The sendFileError() subprocedure, shown in Code 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. The addMessage() subprocedure is described inGetting the Message, Part 1.

The noted difference is what the routine does with a constraint error (status of 1022 or 1222). Instead of sending an error message, the routine calls the subprocedure SendConstraintMsg().

     D STAT_DUPLICATE  C                   01021
     D                 C                   01022
     D                 C                   01222
     D STAT_TRIGGER_1  C                   01023
     D STAT_TRIGGER_2  C                   01024

     D ERR_NOTFOUND    C                   'ALL9001'
     D ERR_CHANGED     C                   'ALL9002'
     D ERR_DUPLICATE   C                   'ALL9003'
     D ERR_CONSTRAINT  C                   'ALL9004'
     D ERR_TRIGGER     C                   'ALL9005'
     D ERR_UNKNOWN     C                   'ALL9006'
     D ERR_NOT_NUMBER  C                   'ALL9007'
     D ERR_NOT_DATE    C                   'ALL9008'

     P sendFileError   B                   Export

     D                 PI              n
     D  status                        5i 0 Const



           // Duplicate
       when status = STAT_DUPLICATE;

           // Referential Constraint

       when status = STAT_CONSTRAINT_1  or
           status = STAT_CONSTRAINT_2;

           // Trigger
       when status = STAT_TRIGGER_1  or
           status = STAT_TRIGGER_2;

           // Other
           return *On;

       return *Off;

     P                 E

Code 2: The sendFileError() subprocedure.

What's In a Name?

Before looking at the SendConstraintMsg() subprocedure, let's give a little thought to the way we name constraints and how we might associate meaningful messages with the constraints. There are three choices:

  1. Have logic in the program that associates the constraint name with a message we want to send. (This is not really a very practical option).
  2. Send the name of the constraint as the message text. Since we are not confined by 10-character naming conventions, it is valid to have a constraint named "This person is way too young to be employed by us."
  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 part of the constraint name? This is my preferred method and it is the one I will describe.

Regardless of how you decide to associate a constraint with a message, the SendConstraintMsg() shows the technique for retrieving the constraint name.

Take another look at the constraints shown in Figures 3 and 4. Note how the constraint names have a message ID as part of the name (ERR0001 and ERR0002). When a program receives a constraint violation, we retrieve the constraint name, extract the message ID, and use that message to provide a "meaningful" error to the user.

Name that Constraint

Although the file I/O error may not identify the constraint, the required information is available. We need to check the program message queue (of the program that received the I/O error) for the constraint violation message (CPF502D, CPF502E, CPF502F, CPF503A, CPF503B). The second level message text of the message contains the constraint name.

So, SendConstraintMsg() must read back up through the program message queue, retrieve the required constraint message, and extract the name of the constraint.

I use the QMHRCVPM API to read through the program message queue. Code 3 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 Code 4).
  • 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 (sendConstraintMsg() is the current entry in the call stack-- callStack = '*'; SendFileError() is one up in the call stack; the procedure 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                    3000a   Options(*VarSize)
D  msgInfoLen                   10i 0 Const
D  formatName                    8a   Const
D  callStack                    10a   Const
D  callStackCtr                 10i 0 Const
D  msgType                      10a   Const
D  msgKey                        4a   Const
D  waitTime                     10i 0 Const
D  msgAction                    10a   Const
D  errorForAPI                        Like(APIError)

Code 3: Prototype for Calling QMHRCVPM.

Code 4 shows the data definition portion of the SendConstraintMsg() 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 sendConstraintMsg...
P                 B

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                         7a
D  msgType                       2a
D  msgKey                        4a
D                                7a
D  CCSIDInd                     10i 0
D  CCSIDReplace                 10i 0
D  lengthreturn                 10i 0
D  lengthAvail                  10i 0
D  msgData                    1024a

D setMsgKey       S              4a
D prevMsgKey      S                   like(setMsgKey)
D constraint      S             50a
D msgId           S              7a

Code 4: D Specs for sendConstraintMsg().

Code 5 shows the rest of the sendConstraintMsg() 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 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 last 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 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(constraint:%Scan(' ':constraint)-7);

     setMsgKey = msgBack.msgKey;


P                 E

Code 5: The SendConstraintMsg() subprocedure.

In The End. . .

Constraints are a powerful tool that we should use in our applications to provide a means of ensuring data integrity both inside and outside of our application.

And think of all the code that can be removed from programs and be placed on the database.

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. The only tricky bit is the process of trapping the I/O error on the file operation.

Paul Tuohy has worked in the development of IBM Midrange applications since the 1970s. He has been IT manager for Kodak Ireland Ltd. and Technical Director of Precision Software Ltd., and is currently CEO of ComCon, a midrange consultancy company based in Dublin, Ireland. He has been teaching and lecturing since the mid-1980s. Paul is author of "Re-engineering RPG Legacy Applications," "The Programmers Guide to iSeries Navigator," and the self-teach course "iSeries Navigator for Programmers." He writes regular articles for many publications and is one of the quoted industry experts in the IBM Redbook "Who knew you could do that with RPG IV?" As well as speaking at RPG & DB2 Summit, Paul is an award-winning speaker who participates regularly at the U.S. COMMON tradeshow and other conferences throughout the world. Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.


Getting the Message, Part 1

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Sponsored By

SEQUEL ViewPoint®--Data Access & Analysis
for Power Systems™ Servers

                                               · Easy to use by IT and end users
                                               · Automated data access and display
                                               · Complete BI package: reports, tables,key
                                                  performance indicators, and dashboards
                                               · IBM i-centric for real-time data analysis
                                               · Expert support and training
                                               · Secure data access
                                               · Green screen, Web, browser

SEQUEL--Fast, efficient & cost-effective data analysis

Click here for a FREE Information Kit!

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

MaxAva:  Get a complimentary continuity assessment for *noMAX - Premium HA & DR
ProData Computer Services:  Save the day with RDR and ProData utilities!
COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida


IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
The iSeries Express Web Implementer's Guide: List Price, $49.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
Can the AS/400 Survive IBM?: List Price, $49.00
Chip Wars: List Price, $29.95

The Four Hundred
IBM Fired Up About Power7-Based Smarter Systems

A Little Insight Into the Rest of the Power7 Lineup

Power7: Yields Are Good, Midrange Systems A Go

Mad Dog 21/21: The Lotus Reposition

Pay Increase? You're Getting a Pay Increase?

Four Hundred Stuff
i/OS Shops to Wait Another Quarter for Power7 Compilers

Technology Mashup Yields an iPhone App for BPCS Data

SharePoint Gets Its Own iBOLT for ERP Integration

Datawatch Adds Goodies to Data Warehousing Software

ACOM Streamlines Access to Content in EZCM and SharePoint

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
February 13, 2010: Volume 12, Number 07

February 6, 2010: Volume 12, Number 06

January 30, 2010: Volume 12, Number 05

January 23, 2010: Volume 12, Number 04

January 16, 2010: Volume 12, Number 03

January 9, 2010: Volume 12, Number 02

TPM at The Register
Teradata ekes out sales and profit growth

SGI spins up Cyclone HPC cloud

Unisys parts with server chief

Red Hat projects to seed cloudy IT

Big Blue says Power7 will make world smarter

Hitachi inks Xsigo pact for server I/O virt

IBM chills sealed data center with outside air

Riverbed WAN optimizers get SSDs, 10 GE

Intel's 'Tukwila' Itaniums - hot n' pricey

AMD talks energy with 'Llano' cores

Power7 v Power6 - it's all about the cache

Power7 - Big Blue eye on UNIX


SEQUEL Software
WorksRight Software
System i Developer

Printer Friendly Version

Handling Constraint Violations in RPG

CPYTOIMPF Doesn't Create Column Headers, But You Can

Admin Alert: Six Things Power i Administrators Need to Know

Four Hundred Guru


Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2010 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement