Thanks, Michael, Tim, Doug. That's sort of what I was refering to. Sybase is able to look at foo=NULL the same as foo is NULL, and I am sure other db packages are able to do the same. Can't find anything on this issue in Oracle docs. If yes, it might be a good idea to implement this on the DBD::Oracle level, therefore hiding this ugly SQL implementation. Maybe even just allowing the set of "ansinull" type of a function per transaction/connection scope.
Or reparsing the foo=NULL to foo is NULL, should also not be a diffucult task on the DBD side, though I am not sure what role that would play on the DB side as far as placeholders, since this will most likely have to reprepare the query anyways??? Ilya -----Original Message----- From: Michael Peppler To: Tim Bunce Cc: Sterin, Ilya; '[EMAIL PROTECTED]' Sent: 10/23/01 10:51 AM Subject: Re: = NULL vs. IS NULL Tim Bunce wrote: > > 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. And just to be complete, Sybase (and possibly MS) has an "ansinull" option that can be set. If it is not set then the "x = null" *will* work correctly. Michael
