Re: setNull in select doesn't work

[Craig L Russell]

Hi Knut,

On Jul 25, 2006, at 12:11 AM, Knut Anders Hatlen wrote:

Craig L Russell <[EMAIL PROTECTED]> writes:

Hi Marieke,

You have hit one of the big usability issues with SQL. While many
vendors have implemented "WHERE value = ?" such that it behaves
exactly like "WHERE value IS NULL" in case the parameter passed is
null, it isn't required by the governing standard, and is not
therefore a bug in an implementation but a feature.

To be portable, you need to have different SQL statements in the case
where the parameter is null versus not null. And if you have "n"
parameters, you might have to have "2 ^ n" different SQL statements,
depending on whether each of the parameters is null or not.

Sadly, this is the state of the art in today's database world.

Hi Craig,

Let's not get too negative. I don't think you need more than one
statement.

This is the simplest one, but it requires two calls to setNull/setXXX
per parameter:

select * from mytable where value = ? or (? is null and value is null)

Yes, very creative, but unfortunately, this solution doesn't work for all databases either. :-(

IIRC, Sybase throws an exception parsing "? IS NULL".

Alternatively, this query requires only one setNull/setXXX per
parameter and handles null:

  select * from (values (cast (? as varchar(128)))) params(value),
                mytable
           where mytable.value = params.value or
                 (params.value is null and mytable.value is null)

Not exactly beautiful, but it works! :)

I rest my case.

Craig

I believe the thesis of my reply is "You have hit one of the big usability issues with SQL".

--
Knut Anders

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature