Handling Constraint Violations in RPG
February 17, 2010 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.
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.
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.
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.)
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; endIF; return *On; endIF;
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 STAT_CONSTRAINT_1... D C 01022 D STAT_CONSTRAINT_2... D C 01222 D STAT_TRIGGER_1 C 01023 D STAT_TRIGGER_2 C 01024 D 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 /free select; // Duplicate when status = STAT_DUPLICATE; addMessage(ERR_DUPLICATE); // Referential Constraint when status = STAT_CONSTRAINT_1 or status = STAT_CONSTRAINT_2; sendConstraintMsg(); // Trigger when status = STAT_TRIGGER_1 or status = STAT_TRIGGER_2; addMessage(ERR_TRIGGER); // Other other; addMessage(ERR_UNKNOWN); return *On; endSl; return *Off; /end-Free 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:
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:
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 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:
/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(constraint:%Scan(' ':constraint)-7); addMessage(msgId); return; on-Error; addMessage(ERR_CONSTRAINT); return; endMon; endIf; setMsgKey = msgBack.msgKey; endDo; addMessage(ERR_CONSTRAINT); return; /end-Free 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.