db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: setNull in select doesn't work
Date Mon, 24 Jul 2006 17:37:52 GMT
The issue is that some databases support the comparison of

x = NULL

This is not a feature AFAIK that is an ANSI requirement.

You need to use IS NULL in your query.



you can use setNull or pass a null to setXXX if you choose, but this 
does not correct your SQL grammer, you need to provide the correct SQL 
grammer for the given backend end that you are using for handling NULLs.

Marieke Vandamme wrote:
>
> Hello,
>
> When using other Database Drivers (like postgres) this is supported.
> When suggesting Derby doesn't, what are the alternatives for my problem?
> I really need to use it in the form of PreparedStatement,
> like "select * from table where value = ?",
> because we are using select statement with more than 6 parameter-values.
>
> In what ways is the setNull function usefull ?
> When trying to solve my problem, in many examples I found :
> if (value == null)
>         ps.setNull(1, Types.VARCHAR);
> else
>         ps.setString(1, value);
>
> Thanks !
>
>
> -------
>
> Marieke Vandamme <marieke.vandamme@tvh.be> writes:
>
> > 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.
>
> It doesn't return any rows because "value = null" does not evaluate to
> true even when value in fact is null, in which case it evaluates to
> unknown. "value is null" should work.
>
> -- 
> Knut Anders
> **** DISCLAIMER ****
> http://www.tvh.be/newen/pages/emaildisclaimer.html 
> <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."
>

Mime
View raw message