Somewhere in a system I'm developing I have a query like this:

SELECT C FROM T WHERE (X = :A) AND (Y = :B)

where both X and Y a declared as VARCHAR(10), and :A and :B are instanciated
with values extracted from String variables in the software. X is never
NULL,
but Y could be. I'm (still) using Firebird 1.0.3.

My first problem was that this query never returned Cs of any register where
Y was NULL. So, I realized that NULL is different of '' (the empty string),
that was the value assigned to :B. Right?

Then I rewrote the query as:

SELECT C FROM T WHERE (X = :A) AND (((Y IS NULL) AND (:B = '')) OR (Y = :B))

What a kludge! But if it works... he, of course it didn't. Whenever :B was
not empty the query failed with an error: "arithmetic exception, numeric
overflow, or string truncation." I don't really know why, but I guess that
when compiling the query the server assigns to '' a type incompatible with
the value of :B (something like VARCHAR(0) for example) and complains that
to be able to compare it with :B it would need to truncate :B, so
invalidating the comparison. In any case, it fires an exception.

I noticed that rewriting the query as:

SELECT C FROM T WHERE (X = :A) AND (((Y IS NULL) AND (:B = CAST('' AS
VARCHAR(10))) OR (Y = :B))

it did work in every case, but I didn't like this solution. Perhaps in the
future someone will change the size of column Y, and queries like this will
start to fail sporadically.

So I rewrote the query, once again, as:

SELECT C FROM T WHERE (X = :A) AND (((Y IS NULL) AND (:LB = 0)) OR (Y = :B))

and assigned to :LB the length of the value assigned to :B (there's no
LENGTH function for varchars, ins't it?). This one finally worked as
expected and didn't create extraneous dependencies.

I found two another possible solutions (not tested):

1) Create a domain DY as VARCHAR(10), and declare the column Y to be of that
domain and cast the empty string in the query to the domain instead of to
VARCHAR(10) explicitly. I'm not using domains anywhere in the database so I
didn't like this special case.

2) Upgrade to Firebird 1.5 and rewrite the query as:

SELECT C FROM T WHERE (X = :A) AND (NULLIF(Y, '') = :B)

but requires to upgrade to FB 1.5 in the middle of the project so I
discarded this option by now(there's a NULLIF function in FB 1.5, ins't
it?).

The objective of this little story is to ask: Is there any easier/better way
to solve this issue?

Thanks in advance.

EB