PreparedStatement WHERE field IS NULL

[Michael Zellmann]

I am having trouble using a PreparedStatement to search for a record.
As an example, I have a table with fields name and namespace.
Usually the namespace is NULL, but lately we have some records with a specific namespace.
In the past I have used a PS with SELECT id FROM tagnames WHERE name = ?
That works fine.
Now I need to include the namespace - SELECT id FROM tagnames WHERE name = ? AND namespace = ?
If I have a name of Credit and namespace of abc, it works fine
However, there might also be a record with name of Credit, but namespace NULL
If I try ps.setNull(2, java.sql.Types.VARCHAR) the query doesn't get the result
I find I need to use a different query - SELECT id FROM tagnames WHERE name = ? AND namespace IS NULL
So - I need to test if the namespace is NULL and then use the alternate ps
What I would like is to have one ps that would handle both a NULL namespace and a specific namespace.

Is there any way to do this?

[Apurv Adarsh]

Can you try using selective OR in this querry?
the querry can be rewrriten like : SELECT id FROM tagnames WHERE name = ? AND (1 = ? OR namespace = ?)
and then you can have

if(null == namespace){
ps.setIntl(2,1);
ps.setNull(3, java.sql.Types.VARCHAR);
}else{
ps.setIntl(2,2);
ps.setStringl(3,"abc");
}
I havent tested this code but think it should run fine.

[Jeanne Boyarsky]

Michael Zellmann wrote:What I would like is to have one ps that would handle both a NULL namespace and a specific namespace.

Why do you want a single prepared statement?

I would use different SQL in the two prepared statements. They are different queries and could potentially be optimized differently. Adding complexity to the query and making life difficult for maintainers/optimizers doesn't seem worth writing a tiny bit less code.r