At 04:49 AM 21/09/2006, you wrote:

>Hi all,
>
>
>what's the proper way of catching a FK violation when a user tries to delete
>a record? The OnError event of a TIB_Query seems like it, but maybe I'm not
>using it properly because even if I set the RaiseException parameter to
>False, there is still a "Record was not located for delete" error - or do I
>have to do something else, like cancel the delete?
>
>Oh, and this is using IBO 4.6

OnError *catches* the exception at that level. You can handle it
completely right there and set RaiseException false; or you can
allow it to pass "up the chain" to another exception handler higher
up. Ultimately, if it doesn't find a handler en route up the chain,
it will go to the session's default "catch-all" handler and you will
see the default response to unhandled errors.

Taking your FK violation on a delete: in this case, you certainly
have to cancel the operation, since there is nothing you can ask the
user to do at that point to correct the situation. The server is not
going to allow the deletion because there are dependent children and
there is no ON DELETE CASCADE or ON DELETE SET NULL rule defined for
that FK relationship. So your code has to handle *this* condition by
calling Cancel. That will cancel the delete request and return the
dataset to dssBrowse state. Your handler could send the user a
meaningful message like "Cannot delete Group because it contains
active members".

Another time, you might get an FK violation on an update or an
insert. If the error occurred because the user made an invalid data
entry (perhaps he forgot to select a value for the FK in an insert,
or he manually changed something in an edit that causes the
violation), you probably don't want to cancel the operation, but
simply ask the user to fix it. In that case, you can handle this
exception by calling SysUtils.Abort and sending a useful message like
"Value in Group is missing or invalid" or "Can't change parent Group
because it contains members". In this case, the dataset state
remains in dssEdit (or dssInsert, as the case may be) and only the
database request is cancelled.

Now, handling the whole thing at the lowest level every time is going
to take a lot of coding and be complex to maintain. So you might
want to pass the exception up the chain to an outer-level handler
that conditionally handles all of the exceptions that you want to
handle yourself.

-- One way to do this is to write one big OnError handler for the
connection or the session.

-- Another way is to write a separate unit that contains all the
error handling you want to do yourself, that you can call from any
exception level you wish.

-- Or you can do it both ways, deciding to do it one way for some
kinds of exceptions and the other for certain other kinds.

-- And don't forget you can subclass exceptions. So, using our
current example, because you want to define different behaviours for
a foreign key violation depending on what kind of operation was being
requested, you might want to raise one class of exception if an FK
violation occurs on a delete request and another for when it occurs
on an insert or update.

As a tip for knowing where you're going, IB_Header.pas has all the
symbols for the error codes, e.g. testing whether the exception's
ERRCODE field contains isc_foreign_key is a lot more useful for the
code maintainer than testing whether it contains 335544466 !

Helen