--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 08:33 PM 1/03/2003 +0000, you wrote:
>
> >The problem is that if you can't avoid NULL in your fields/tables,
> >there is the situation where you have to test a field against some
> >user provided values that can include null.
> >Unfortunatly, if you try in code something like:
> >where (:myid is null) or (customer_id = :myid)
> >you have an exception raisen.
>
> The integrity of the interface (IBO) depends on the designer having

done

> the right things, both in the client app and in the database. It's a
> stretch to expect IBO to provide events as run-time Band-Aids for bad
> design, which is what you are asking for.

Asking for? I was explaining a situation where OnPrepareSQL (already
existing event) is useful.
If you try the above SQL, you get:
"data type unknown"
so you can't use the same query for a user-provided param value that
can be null (state) or a value.
For these situations, you can build the where part of the SQL in the
OnPrepareSQL, as you already know ;)

> The WHERE clause in your SQL
> above isn't valid on the server (null is null is not a valid predicate,
> since it cannot return True).
>

The WHERE above IS valid on the server.
If you use a SP with a variable instead of a parameter, like:
w_myid integer;
...
w_myid = null;

select name from customer
where (:w_myid is null) or (customer_id = :w_myid)

it works
As discussed, also by Jason, in the FB developer list, actually FB
(and Interbase) gds32.dll fails in passing/understanding important
information about the param types, AFAIR

In situation where a foreign key can or can not point to a row in the
foreign table, checking for null is very common, and since the "="
does not work well for nulls, there is often the need to use "is null"
and the "=" to handle the two possible values/state of a user choice,
if you have to use the same query for a generic solution.
So IBO helps with OnPrepareSQL, but it would be better client FB dll
correctly understand param types.
regards
Marco Menardi