db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacob Barrett" <Jaco...@Attachmate.com>
Subject Prepared Statement and NULL in the WHERE Clause
Date Thu, 16 Dec 2004 18:43:35 GMT
I am seeing something that doesn't seem right.

Consider this table, records, and prepared statement below:
TABLE mytable
	VARCHAR : a
	VARCHAR : b

RECORDS:
foo, bar
foo, <NULL>
foo, car

SELECT * FROM mytable WHERE a = ? AND b = ?

If I want to find all records where a is "foo" and b is NULL shouldn't I
be able to:
setString(1, "foo")
setString(2, null)

Doing so produces no records.

Changing the prepared statement to:
SELECT * FROM mytable WHERE ((? IS NULL AND a IS NULL) OR a = ?) AND ((?
IS NULL AND b IS NULL) OR b = ?)
setString(1, "foo");
setString(2, "foo");
setString(3, null)
setString(4, null)

This actually returns all the desired records. (foo, <NULL>)

Do I really have to write all my prepared statements in this fashion to
catch the NULL parameters?  The setNull or equivalent methods on a WHERE
parameter should be the same as IS NULL, but it isn't.  Am I wrong?

Thanks,
Jake



Mime
View raw message