Subject Re: [IBO] Handling a FK violation in OnError
Author Antti Kurenniemi
Thank you, Helen. I have two additional questions: how exactly do I cancel
the delete in the OnError, to prevent the exception from being displayed?
And, is there a way to "ask" the database via IBO (or any other way) if a
record can be deleted? The way I do it now is first ask the user if they
really want to delete the record, then attempt to delete it and then throw
an error if the delete fails, which is sort of clumsy.

Antti Kurenniemi

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <>
Sent: Thursday, September 21, 2006 2:27 AM
Subject: Re: [IBO] Handling a FK violation in OnError

> 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
>>a record? The OnError event of a TIB_Query seems like it, but maybe I'm
>>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
>>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