At 09:51 AM 09-01-01 +0100, you wrote:

>Hi all,
>
>my question today is how to specify NULL in TIB_Query.Params? I tried
>calling Params[xx].Clear, Params.ClearBuffers(rsNone), assigning Null or
>Unassigned to Params[xx].Value but none of these works. Please consider the
>following test case:
>
>CREATE TABLE TEST (
> A INTEGER,
> B INTEGER
>);
>
>the test table contains the following data:
>
> A B
>============ ============
>
> <null> 1
> <null> 2
> 1 1
> 1 2
> 2 1
> 2 2
>
>I would like to use TIB_Query with a parameter. SQL.Text property contains
>statement 'SELECT * FROM TEST WHERE (A = :A)'.

This isn't valid SQL if :A is null, as has been pointed out.
But I'm sure Jason deals with 'is null' in the SQLWhere parser.

Here's how to do it with the parameters:

> with IB_Query1 do
> begin
> Close;
> if not Prepared then
> Prepare;
> Params[0].Clear;
> // Params.ClearBuffers(rsNone); <-- don't do this, we need the
> parameters and IBO looks after updating the values

Don't use the Params[] array, you lose some essential stuff if you do and
in some queries it can get scrambled. Always use ParamByName.

> // Params[0].Value := Null; WRONG

ParamByName('A').Clear;

> // Params[0].Value := Unassigned; NOPE.


> Open;
> // here I always get Eof

You get EOF if the dataset is empty. You get BOF as well.

Try this out and come back with a RESULT. :))

btw, have you looked at the BLANKISNULL column attribute...I don't know
whether it helps in your current situation but it's worth knowing about,
e.g. to make sure an empty string gets written as NULL and not ''.

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