Handling Constraints Revisited
March 31, 2015 Paul Tuohy
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.
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!
Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.