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 Mon, 24 Jul 2006 20:03:56 GMT
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.


On Jul 24, 2006, at 1:27 AM, Marieke Vandamme wrote:

> Hello,
> We are trying to perform a select query to the Derby database with  
> PreparedStatement
> PreparedStatement ps = c.prepareStatement("select * from table  
> where value = ?");
> //several methods performed on statement (separately ofcourse)
> 1. ps.setNull(1,Types.VARCHAR);
> 2. ps.setObject(1, null, Types.VARCHAR);
> 3. ps.setString(1, null) ;
> The table contains null values, but the resultset is empty for the  
> 3 cases.
> When executing statement with query "select * from table where  
> value is null", we get the wanted results,
> but we need to do it with setting parameters in preparedstatement.
> We are using db-derby-snapshot-
> Is this a bug? Do we need to set any special parameters/settings?
> Thanks!
> **** DISCLAIMER ****
> http://www.tvh.be/newen/pages/emaildisclaimer.html
> "This message is delivered to all addressees subject to the  
> conditions set forth in the attached disclaimer, which is an  
> integral part of this message."

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!

View raw message