At 03:38 AM 24/02/2007, you wrote:

>If I may bother you once more; if it was a issue of logical
>corruption what steps would you suggest to handle this type of issue
>gracefully in firebird. It seams a difficult thing to detect as no
>error message was thrown.

Indeed, it is difficult to detect. You must absolutely ensure that
your database is designed to throw an exception whenever it
encounters the unexpected. But for "handling this type of issue
gracefully" - I have no answer. You have to design your structures
so that these accidents cannot happen - don't park your unlocked car
and leave the keys in the ignition.

At best, if you become aware that you have a possible "validation
leak", plan to find the cause and fix the the problem and, in the
meantime, write your queries in such a way that you will pick up the
expected results, e.g. by casting varchars as char and/or testing for
null, or using STARTING WITH, or whatever it takes.

-- Design relationships so that equivalence comparisions between keys
will always return the expected result - for example, don't use CHAR
or floating point types for such fields. Take the same care with
non-key fields if your requirements are likely to call for
equivalence tests involving them. Ideally, surrogate all
"meaningful" keys so that relationships are independent of the data.

-- When you make changes to structures you must be aware of the
consequences and first ensure that all of your existing data is
consistent with the new rules *before* you do the change.

-- If you disable indexes or triggers for some reason, understand the
consequences of accepting data into the structures under those conditions.

-- The same applies if you have imported the database from some
existing system: you have to find out how the old system validated
the data. In the worst case, all data would have been validated in
application code.

-- Ideally (in my view) you should have all data constraints covered
in the database itself (with data attributes, check constraints and
triggers). But certainly don't write applications that blindly poke
unvalidated data into unprotected data containers; and don't write
systems that depend on applications to validate incoming data.

-- If you are importing data, you'll need to be particularly careful
to constrain the containers into which you are pulling data.

Sorry, no magic bullet today...

./heLen