Re: setNull in select doesn't work

[Craig L Russell]

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.  Craig 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-10.2.0.4-423199. 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:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature