db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: setNull in select doesn't work
Date Tue, 25 Jul 2006 14:57:38 GMT
Hi Knut,

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

> Craig L Russell <Craig.Russell@Sun.COM> 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 believe the thesis of my reply is "You have hit one of the big  
usability issues with SQL".

I rest my case.

Craig
>
> -- 
> Knut Anders

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


Mime
View raw message