[FIXED] Prepared statement with set null in query doesn't return any record

Issue

I use prepared statements to read/write data in my DB (SQLite). In my table INVENTORY, there are records which have null value in the column paleta (the column is defined as VARCHAR in the table). I want to select these records and I tried:

sq = "SELECT * FROM INVENTORY WHERE paleta = ? AND product = ? AND lot = ?";
//...
stm = c.prepareStatement(sq);
stm.setNull(1, java.sql.Types.VARCHAR);
stm.setString(2, "theIdOftheProduct");
stm.setString(3, "theLotOftheProduct");

ResultSet rs = stm.executeQuery();

The above query doesn't return anything.. I removed the paleta = ? and I get the records I want.. How can I define the query like SELECT * FROM INVENTORY WHERE paleta is null etc.. using the query parameters?

Solution

What you are trying to do is equivalent to writing SELECT * FROM INVENTORY WHERE paleta = NULL ..., which doesn't work.

Since you are essentially searching for rows having a constant value in the paleta column (which happens to be NULL), you can eliminate the first query parameter and explicitly check for null:

sq = "SELECT * FROM INVENTORY WHERE paleta IS NULL AND product = ? AND lot = ?";

stm = c.prepareStatement(sq);
stm.setString(1, "theIdOftheProduct");
stm.setString(2, "theLotOftheProduct");

Answered By - Eran
Answer Checked By - Marilyn (JavaFixing Volunteer)