MySQL Prepared Statement & NULL

[Rob J Ford]

I have an app with a CachedRowSetImpl that reads from a MySQL database (using "com.mysql.jdbc.Driver")

I'm passing the RowSet a prepared statement that looks like this:

    
rowSet.setCommand("SELECT FirstName, DOB, Spouse FROM family WHERE Spouse LIKE ?;");

...and then setting the parameter with setString, setInt, etc... I'm using the LIKE operater to allow me to enter the '%' wildcard into the parameter.

The only place where its falling down is when I try to select NULL fields. Using setNULL does not work as MySQL treats NULL differently from other databases. The usual way to execute a clause on NULL is

   
"SELECT FirstName, DOB, Spouse FROM family WHERE Spouse IS NULL;"

...but using the 'LIKE' or '=' operators fails when used with NULL under MySQL.

So is there any way I can put a WHERE clause on a column with a NULL value using a PreparedStatement like I have above?

[Jeanne Boyarsky]

Rob J Ford wrote:...but using the 'LIKE' or '=' operators fails when used with NULL under MySQL.

So is there any way I can put a WHERE clause on a column with a NULL value using a PreparedStatement like I have above?

It should fail. Testing for equality (or like) is undefined according to the SQL starndard. MySql is behaving correctly. You have to use "is null" to check for null in SQL.