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
