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.
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;
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:
- 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).
- 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.”
- 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 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.
/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.
