Re: Some database discussions...

Buck,

Is this something you actually do, or would like to do in all your DB
maintenance applications? Or is it something that has more loop-holes than
the tax code?

The message that you may find, somewhere in one of your call stack message
queues, may contain something like:

Message ID . . . . . . : CPF502D Severity . . . . . . . : 30

Message type . . . . . : Sender copy

Date sent . . . . . . : 05/17/17 Time sent . . . . . . :
15:54:51


Message . . . . : Referential constraint violation on member CUSMSTF.

Cause . . . . . : The operation being performed on member CUSMSTF file

CUSMSTF in library AMWDB failed. Constraint CUSMSTF_R0 prevents record

number 1 from being inserted or updated in member CUSMSTF of dependent
file
CUSMSTF in library AMWDB because a matching key value was not found in

member REPMSTF of parent file REPMSTF in library AMWDB. If the record
number
is zero, then the error occurred on an insert operation. The constraint
rule
is 2. The constraint rules are:

1 -- *RESTRICT

2 -- *NOACTION

Recovery . . . : Either specify a different file, change the file, or


More...
Reply . . . : C


If you have programs in your call stack that send diagnostic or other types
of messages to call-stack message queues, how do "applications", know what
they should be looking for?

If they are able to find what they're looking for, are you suggesting that
they parse it to locate what they are really looking for?






On Wed, May 17, 2017 at 2:24 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 5/17/2017 10:26 AM, Nathan Andelin wrote:

I may be missing something. For shops that implement DB defined RI
constraints, how do they handle user interface requirements (i.e.

friendly,

meaningful error messages, cursor positioning on relevant columns, and
highlighting)?

alter table Customer
add constraint "00005 Address foreign"
foreign key (addressID) references Address (id)
on delete restrict
on update restrict
;

By giving each constraint a pseudo-ID in the first n characters of
MSGDTA, it's possible to CHAIN to a friendly message file and help the
user out. INSERT, UPDATE, and DELETE all throw different SQLSTATE
errors, with corresponding SQL05nn MSGIDs. That's potentially part of
the logic to figure out where to position the cursor, but the RPG
program generally 'knows' it's trying to delete a row, etc.

Where it gets interesting is when a trigger tries to do that work, and
the trigger runs into RI problems. It happens. I kind of like standard
IBM i message handling for this reason. With the plumbing already in
place, I automagically collect unexpected errors from programs I never
knew were running. (And therefore don't have an 'error message array').

--
--buck