The DBI docs say:
However, care must be taken in the particular case of trying to use
null values to qualify a C<SELECT> statement. Consider:
SELECT description FROM products WHERE product_code = ?
Binding an C<undef> (NULL) to the placeholder will I<not> select rows
which have a NULL C<product_code>! Refer to the SQL manual for your database
engine or any SQL book for the reasons for this. To explicitly select
NULLs you have to say "C<WHERE product_code IS NULL>" and to make that
general you have to say:
... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))
and bind the same value to both placeholders.
Tim.
On Tue, Oct 23, 2001 at 09:39:55AM -0600, Sterin, Ilya wrote:
> Ok, this might be a silly one. Though I have no problem solving this issue
> by dynamically building the query, there should be a better solution due to
> repreparing the statement...
>
> Within the where clause of the query that is prepared...'
>
> where foo = ?
>
> When binding an undef value when executing, this executes "where foo =
> NULL". In Oracle there is a difference between foo = NULL and foo is NULL.
> This also kind of disallows you to prepare the statement, since you must
> check for the undef binding value and reprepare with "is NULL".
>
> Maybe someone knows a better way of doing this, but I would envision a
> facility that does this for you when binding NULLs to where clause
> placeholders.
>
> Ilya
