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. 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).

IBO presumes that the developer is on top of what he is doing. Although it
largely relies on the server for final validation of the SQL, it provides
more than ample ways for you to protect your SQL from invalid user input
and thus avoid the expense of a server-side exception. It is your job to
use them and to make sure that they work.

Using your example above, you should not try to test the nullness state of
a client-side input in your DSQL statement. You should make it not null on
the server, or at least provide a trigger to disallow null, where you want
this on a nullable column. In the latter case, or any other case where the
column is nullable on the server but you want to enforce it as not null at
the client, you apply the ColumnAttribute REQUIRED to it. You can also use
the BLANKISNULL attribute if you need to ensure that blanks and empty
strings get treated as nulls.

If you don't want to wait until the Post event to raise the local exception
('xyz is a required field') then have your code monitor the candidate
values in a timely way *before* parameter assignment begins. Since
parameter values can come from any source and any event (dataset column
buffers, variables, input controls, keystrokes, change events, etc.) this
is quite specific to your own application. A brute force event handler
would be an ambulance at the bottom of the cliff - it won't cure bad design
and, in any case, in some situations, it would occur too late to enable a
user error to be fixed up by the user .
-------------------------------------------
Your example above seems to indicate that you might be still confused about
what happens to a client-side parameter in the journey between gds32.dll
and the server.

Delphi's and IBO's Params both provide a way for the client to pass
constants to the SQL interface to DSQL, such that, each time the statement
is passed to the server, a different set of WHERE arguments goes
through. The server doesn't receive the Delphi-side parameter, it receives
an SQL statement containing the constants. This is made possible by
cooperation between the application interface (IBO) and the API (gds32.dll
or its equivalents).

We get confusion about parameters sometimes, because a statement invoking
a selectable SP can have a mixture of client-side parameters (a WHERE
clause) and server-side "parameters", i.e. values that are passed as input
arguments to the SP. e.g. Let's say you have an SQL property like

select fielda, fieldb from myproc (:param1, :param2) where fielda = :fielda

In your AssignParams code, let's say you do

ParamByName('param1').AsInteger := 99;
ParamByName('param2').AsString := 'Menardi';
ParamByName('fielda').AsInteger := 55;

The statement that the server receives is

SELECT FIELDA, FIELDB FROM MYPROC(99, 'Menardi')
WHERE FIELDA = 55;

So two of the client-side params here are actually database parameters
(proper terminology "input arguments") as well. On the server side, WHERE
clauses don't take parameters. I think the terminology overlap is a common
source of confusion among Delphi programmers (thanks to the Borland
documenters) that leads them to believe that ALL Delphi-side parameters are
database parameters. If you can separate in your mind "database
parameters" from "Delphi parameters" it will help a lot to see that using
SQL predicates to test Delphi parameters is not very safe.
------------------------------------------------
On including IS [NOT] NULL in WHERE clauses:

WHERE <column> IS NULL is fine
WHERE <expression based on column> IS NULL is fine

WHERE <constant> IS NULL can never be valid. Here's why:

WHERE NULL IS NULL and WHERE NULL IS NOT NULL never evaluate to True. They
return False because the outcome of the comparison is Unknown, i.e. NULL

All other possibilities produce logical absurdities, e.g.
WHERE <non-null constant> IS NOT NULL - syllogism
WHERE <non-null constant> IS NULL always returns false - this is a logical
False, not Unknown

Helen