Guru: Handling Constraint Violations In RPG

September 5, 2018 Paul Tuohy

Author’s Note: The contents of this article were originally published as two separate articles – Handling Constraint Violations in RPG and Handling Constraints Revisited. The content of the article has been updated for free form RPG and some of the coding enhancements that have been introduced, into RPG, since 2009.

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.

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

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 SQL. Let’s start with a DEPARTMENT and EMPLOYEE table defined as follows:

CREATE OR REPLACE TABLE DEPARTMENT ( 
	DEPARTMENT_CODE FOR DEPTNO CHAR(3) NOT NULL DEFAULT , 
	DEPARTMENT_NAME FOR DEPTNAME VARCHAR(36) NOT NULL DEFAULT )     
	RCDFMT DEPARTR ; 

CREATE OR REPLACE TABLE EMPLOYEE ( 
	EMPLOYEE_ID FOR EMPID CHAR(6) NOT NULL DEFAULT , 
	FIRSTNAME VARCHAR(25) NOT NULL DEFAULT , 
	LASTNAME VARCHAR(25) NOT NULL DEFAULT , 
	DEPARTMENT_CODE FOR DEPTNO CHAR(3) NOT NULL DEFAULT , 
	HIRE_DATE DATE NOT NULL DEFAULT, 
	BIRTH_DATE DATE NOT NULL DEFAULT, 
	SALARY DECIMAL(9, 2) NOT NULL DEFAULT  )   	  
	RCDFMT EMPLOYEER   ;   

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. Basically, a primary key constraint is first normal form for a table. Defining key constraints is a requirement for foreign key constraints. You can define primary key constraints for the DEPARTMENT and EMPLOYEE tables as follows:

ALTER TABLE DEPARTMENT 
	ADD CONSTRAINT PK_DEPARTMENT 
	PRIMARY KEY (DEPARTMENT_CODE) ; 

ALTER TABLE EMPLOYEE 
	ADD CONSTRAINT PK_EMPLOYEE 
	PRIMARY KEY (EMPLOYEE_ID) ;  

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

ALTER TABLE EMPLOYEE 
	ADD CONSTRAINT FK_EMPLOYEE_TO_DEPARTMENT_ERR0001 
	FOREIGN KEY( DEPARTMENT_CODE ) 
	REFERENCES DEPARTMENT ( DEPARTMENT_CODE ) 
	ON DELETE RESTRICT 
	ON UPDATE RESTRICT ;

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

ALTER TABLE EMPLOYEE 
	ADD CONSTRAINT C_HIRE_AGE_ERR0002 
	CHECK( YEAR(HIRE_DATE-BIRTH_DATE) >= 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.

Handling Constraint Violations

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.

    write(E) employeeR;
    if %error;
       if u_send_fileError(%status(employee));
          exsr *PSSR;
       endIF;
       return *On;
    endIf;  

Checking The File Error

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().

dcl-C STAT_DUPLICATE    01021;
dcl-C STAT_CONSTRAINT_1 01022;
dcl-C STAT_CONSTRAINT_2 01222;
dcl-C STAT_TRIGGER_1    01023;
dcl-C STAT_TRIGGER_2    01024;


dcl-C ERR_NOTFOUND   'ALL9001';
dcl-C ERR_CHANGED    'ALL9002';
dcl-C ERR_DUPLICATE  'ALL9003';
dcl-C ERR_CONSTRAINT 'ALL9004';
dcl-C ERR_TRIGGER    'ALL9005';
dcl-C ERR_UNKNOWN    'ALL9006';
dcl-C ERR_NOT_NUMBER 'ALL9007';
dcl-C ERR_NOT_DATE   'ALL9008'; 
dcl-Proc u_send_FileError export;

  dcl-Pi *n ind;
    status int(5) const;
  end-Pi;

      // Duplicate
  if (status = STAT_DUPLICATE);
    u_add_Message(ERR_DUPLICATE);

      // Referential constraint

  elseIf (status = STAT_constRAINT_1  or
          status = STAT_constRAINT_2);
    send_constraintMsg();

      // Trigger
  elseIf (status = STAT_TRIGGER_1  or
          status = STAT_TRIGGER_2);
    u_add_Message(ERR_TRIGGER);

      // Other
  else;
    u_add_Message(ERR_UNKNOWN);
    return *On;
  endIf;

  return *Off;

end-Proc;  

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 (not really a very practical option).
2. Send the name of the constraint as the message text. Since we are not confined by ten-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 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.

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. SendConstraintMsg() 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. Note the following parameters in the prototype for calling QMHRCVPM:

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

dcl-Pr receive_Msg extPgm('QMHRCVPM');
  msgInfo      char(3000) options(*VarSize);
  msgInfoLen   int(10)    const;
  formatName   char(8)    const;
  callStack    char(10)   const;
  callStackCtr int(10)    const;
  msgType      char(10)   const;
  msgKey       char(4)    const;
  waitTime     int(10)    const;
  msgAction    char(10)   const;
  errorForAPI  like(APIError);
end-Pr; 

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.

dcl-Proc send_constraintMsg;

  dcl-Pi *n end-Pi;

   // DS returned by QMHRCVPM for format RCVM0100
  dcl-Ds msgBack qualified inz;
    byteReturned int(10);
    byteAvail    int(10);
    msgSeverity  int(10);
    msgId        char(7);
    msgType      char(2);
    msgKey       char(4);
    *n           char(7);
    CCSIDInd     int(10);
    CCSIDReplace int(10);
    lengthreturn int(10);
    lengthAvail  int(10);
    msgData      char(1024);
  end-Ds;

  dcl-S setMsgKey  char(4);
  dcl-S prevMsgKey like(setMsgKey);

  dcl-S constraint char(50);
  dcl-S msgId      char(7);  

Here is the rest of the sendConstraintMsg() subprocedure.

  setMsgKey = *ALLx'00';

  doW setMsgKey <> prevMsgKey;

     prevMsgKey = setMsgKey;

     receive_Msg( 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);
            u_add_Message(msgId);
            return;
         on-Error;
            u_add_Message(ERR_CONSTRAINT);
            return;
         endMon;
     endIf;

     setMsgKey = msgBack.msgKey;
  endDo;

  u_add_Message(ERR_CONSTRAINT);
  return;

end-Proc; 

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.

And that is there all there is to getting a meaningful constraint name for native I/O. But this technique will not work for embedded SQL

The Embedded SQL Problem

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.

And therein lies the problem.

Even though we code our SQL directly in our programs, it is not our programs that run the SQL statements. Assuming we coded the following in an RPG program:

exec SQL
   insert into employee (
       employee_ID, firstname, lastname, department_code,
       hire_date, birth_date)
     values(
       :employee_ID, :firstname, :lastname, :department_code,
       :hire_date, :birth_date);  

The SQL pre-compiler translates it to the following:

//*exec SQL                                                     
//*   insert into employee (                                    
//*       employee_ID, firstname, lastname, department_code,    
//*       hire_date, birth_date)                                
//*     values(                                                 
//*       :employee_ID, :firstname, :lastname, :department_code,
//*       :hire_date, :birth_date);                                                   
                                          
          SQL_00005 = EMPLOYEE_ID;     
          SQL_00006 = FIRSTNAME;       
          SQL_00007 = LASTNAME;        
          SQL_00008 = DEPARTMENT_CODE; 
          SQL_00009 = HIRE_DATE;       
          SQL_00010 = BIRTH_DATE;      
          SQLER6 = -4;                 
          SQLROUTE_CALL(               
               SQLCA                   
             : SQL_00000               
          );                            

So, the insert is performed by the program QSYS/QSQROUTE or some program or procedure called by QSYS/QSQROUTE. (In the code above, SQLROUTE is a named constant for QSYS/QSQROUTE.)

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.

The Solution

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

There is one small caveat. SQLERRMC is defined as a 70-character field but the name of the constraint is a varying length field. In other words, the first two characters of SQLERRMC contain the length of the constraint name. So you may want to consider defining the following data structure:

dcl-ds getName len(70);           
  constraint varchar(68);
end-ds;  

And then, by copying SQLERRMC to getName, you will have the name of the constraint.

In the example above, if I had coded:

   exec SQL
   insert into employee (
       employee_ID, firstname, lastname, department_code,
       hire_date, birth_date)
     values(
       :employee_ID, :firstname, :lastname, :department_code,
       :hire_date, :birth_date);

   if (SQLSTATE = '23513');
     getName = SQLERRMC;
   endIf;  

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.

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.

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.

RELATED STORIES

Handling Constraint Violations in RPG

Handling Constraints Revisited

Getting the Message, Part 1