Constraints have been around for a long time but apparently 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.
Also, as a side note, the lack of constraints on a database is one of the reasons I have heard quoted as to why data should be moved/copied from the i to other database servers (SQL server, Oracle, etc.) 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.
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 set by you.
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 dependent invoices on the invoice file. You do not employ people under the age of 16. Such rules are enforced 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 are consistent across all interfaces. What better way to implement them than through the database manager?
Foreign key constraints are defined between two tables: a dependent and a parent. The parent file must have a primary constraint.
The following foreign key constraint identifies a dependency between the EMPLOYEE table and the DEPARTMENT table. The dependency means that a row may not be deleted from the DEPARTMENT table if there are dependent rows on the EMPLOYEE table. The dependence is between the DEPARTMENT_CODE columns on the two tables (as it would be with a join).
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. The following check constraint ensures that the number of years difference between the date of birth and hire date is greater than or equal to 16 (i.e., we do not employ people under the age of 16).
You may have noticed that all of the constraints have long names. Creating a constraint does not result in a corresponding system object, therefore, a ten-character name is not required.
How you handle a constraint violation will be different depending on whether you are using native I/O or embedded SQL. Let’s start with native I/O.
How does your RPG program check for a constraint violation? The same way as it checks for any database violation — by trapping an I/O error on the file operation. Of course the problem with this is that it simply tells us that there was a constraint violation; it doesn’t tell us which of the myriad of defined constraints has caused the problem.
This snippet of code writes a record to the EMPLOYEE table. If an I/O error is detected a call is made to the u_send_fileError() subprocedure, passing the status code of the Employee Details table. The sendFileError() subprocedure will return *off if it “handled” the error or *on if it didn’t.
The u_send_fileError() subprocedure, 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 u_add_Message() subprocedure is described in the article Getting 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 a call is made to the subprocedure SendConstraintMsg().
Have logic in the program that associates the constraint name with a message we want to send (not really a very practical option).
Take another look at the foreign key and check constraints defined earlier. 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.
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. SendConstraintMsg() must read back up through the program message queue, retrieve the required constraint message. and extract the name of the constraint.
formatName is the required format you want returned. RCVM0100 is used in this example.
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 for a message to arrive on the queue. The routine reads from the end of the queue to the start.
This is 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.
Here is the rest of the sendConstraintMsg() subprocedure.
The basic premise of the method outlined above is that, when an RPG program received a constraint violation, it would call a procedure that would search back through the program message queue of the caller and find the relevant message that contained the name of the constraint that had caused the violation.
At this point, calling a subprocedure to look back through the program message queue of our program is pointless since it is the program message queue of QSYS/QSQROUTE (or some other program or procedure) that contains the appropriate message.
Determining the name of the constraint that caused a constraint violation for an embedded SQL statement is a lot easier than what we had to go through in RPG. SQL simply tells us the name of the constraint.
In the SQL Communications Area (the data structure SQLCA, placed in our program by the SQL pre-compiler), the field SQLERRMC will contain the name of the constraint (if there is a constraint violation).
And then, by copying SQLERRMC to getName, you will have the name of the constraint.
Then, on receiving a constraint violation, the field constraint might have a value of FK_EMPLOYEE_TO_DEPARTMENT_ERR0001 or C_HIRE_AGE_ERR0002.
Remember to get the name directly after the SQL statement that caused the error since the contents of the SQL communication areas are reset every time an SQL statement is executed.
Not only that but 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.
Hi Ted, I remember this article and had hoped to one day implement as it solves one of the pains of programming – the same business rules (edits) in multiple programs. Is the code in this latest version of the article downloadable? Thanks.