Handling Constraints Revisited

I recently had an email from Doug, who was making use of some code I had published way back in February 2010 in my article Handling Constraint Violations in RPG. The article discussed how to trap a constraint violation caused by a WRITE/UPDATE/DELETE operation and, more importantly, how to determine the name of the constraint that caused the violation.

Doug had come up with an instance where my code was not working! After going through the usual stages of denial, anger, and some expensive therapy, I read the rest of the email. Doug’s code worked fine with an RPG operation code, but if he used an embedded SQL statement, he could not determine the name of the constraint.

So what was going on?

How It Worked

The basic premise of the original article was 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:

     charIs = '6';
     exec SQL
        insert into violate
           values(:charIs);

The SQL pre-compiler translates it to the following:

     charIs = '6';
  //*   exec SQL
  //*      insert into violate
  //*         values(:charIs);
 /END-FREE
 C                   EVAL      SQL_00005    = CHARIS
 C                   Z-ADD     -4            SQLER6
 C                   CALL      SQLROUTE
 C                   PARM                    SQLCA
 C                   PARM                    SQL_00000
  /FREE

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 then 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:

d getName         ds            70
d  constraint                   68a   varying

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

In the example above, if I had coded:

     charIs = '6';
     exec SQL
        insert into violate
           values(:charIs);
     if (SQLSTATE = '23513');
        getName = SQLERRMC;
     endIf;

Then the field constraint would have a value of BAD_STATUS_CODE_ERR0092.

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.

There is one tidbit from the original article that I would like to repeat. When I name a constraint, the last seven characters of the name are a message ID. For instance, BAD_STATUS_CODE_ERR0092. This provides a very simple means of associating a meaningful error message with a constraint.

Thanks Doug for bringing this to my attention. And my therapist thanks you, too!

RELATED STORIES

Guru: Handling Constraint Violations In RPG

Handling Constraint Violations in RPG