At 09:09 AM 09-11-01 +0000, you wrote:

>I know this has been asked before, but I didn't find any suitable
>reply.
>
>What I would like to do is to have a standard database error handler
>that catches all the things that can go wrong during inserts,
>updates, deletes, etc and displays a custom error message.
>
>I tried to assign a method to OnError (either of the query or the
>database) which seems the right place to me. I can check the type
>of error, display a message and continue.
>
>What doesn't work though is the 'continue' part. After displaying the
>message, the user should be able to continue working normally.

OK, "working normally" in this context means "fixing the error and trying again" since, by the time you get a database error back, the user has already tried to post the error across the wire. Each carry-on solution will be different, depending on the type of error returned by the database. For example, if you get a key violation error and have already tried to commit, it will never commit, so you need to roll back the transaction, have the user (or your app) fix the problem and repost. In the case of concurrency conflicts, you might want to implement some kind of retry strategy or you might want to treat it as an error - depends on what your requirements are.

You might want to define some specific exceptions arising from the anticipated database and write a series of functions and/or procedures to handle them all. Then, your OnErrorHandler simply decides what error it has and calls the appropriate method. In general, it's a lot less pervasive to fix errors before a commit is attempted so there will be conditions where you will want to keep Post and Commit separated (set AutoCommit to false); for one-off statements, it may be more economical to attempt Commit and perform Rollback on exception...

Or you might prefer to do some of your validation on the client side - typically, pre-empting nulls in non-nullable columns, numeric overflows and uniqueness in keys. In reality, you will implement a mixture of both - whatever works best, both for performance and user-friendliness, for each validation you need to do.

IMO, the more of this stuff you can globalise in the ib-Connection (via the TIBODatabase in your case) the better - as a lazy person, I like the "set-and-forget" approach!

> I
>tried "raiseexception:=false", but found that if an insert statement
>fails (e.g. key violation), IB still raises an error telling me that
>the insert didn't take place. The same is true for an update and
>delete.

That's right: if there is a validation error in your data, it will never commit, no matter how much you want it to. <g> Boy, don't you just HATE that?

>Note, that I cannot use try&catch to catch the exception, as I don't
>do the insert or update in code myself... IB does it, e.g. when I
>edit a grid.

See the comments above about pre-empting errors. IBO offers lots of ways to get in there and do that. Do note that IBO is more "data-driven" than native Delphi, so you have a lot more data events on your IBO datasets that you have with TQuery. Do your trying and catching on the data events, not the grid events.

cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________