IS NULL Test in PrepareStatement

[Alec Lee]

Can I use NULL for =? test in prepare statement? i.e.

pstmt=con.prepareStatement("SELECT * from tmp WHERE pk=?");

Can I use :

pstmt.setNull(1,Types.NUMERIC);

to make the SQL test for NULL instead of supplying another SQL using
WHERE pk IS NULL?

[Shailesh Chandra]

No you can not !!

A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero.

To test for nulls, use only the comparison operators IS NULL and IS NOT NULL. Null represents a lack of data, a null cannot be equal or unequal to any value or to another null.

Shailesh

[Alec Lee]

That is means I need to create an extra PreparedStatement object just for the NULL test?

pstmt1=con.prepareStatement("update tmp set a=1 where key=?");
pstmt1.setInt(1, 10);
...
pstmt2=con.prepareStatement("update tmp set a=1 where key IS NULL" );
pstmt2.execute();

I am just wondering is there a more generic SQL which also includes the NULL case.

[Shailesh Chandra]

Originally posted by Alec Y.L. Lee:

That is means I need to create an extra PreparedStatement object just for the NULL test?

I am just wondering is there a more generic SQL which also includes the NULL case.

It depends on you how you want to do this.
you can do same in single query

pstmt=con.prepareStatement("update tmp set a=1 where( key=? OR key IS NULL)");
pstmt.setInt(1, 10);
 

Shailesh

[Jeanne Boyarsky]

Shailesh,
That wouldn't always work though. If you wanted to only do the update when a field is "7", that query would match on nulls as well.

Alec,
I think it's better to go with the 2 query option. It's clearer, simpler and more efficient.

[Shailesh Chandra]

Originally posted by Jeanne Boyarsky:
That wouldn't always work though. If you wanted to only do the update when a field is "7", that query would match on nulls as well.

Jeanne,

I agree that query will update data if either condition is matched,but same is the requirement.

I think it's better to go with the 2 query option. It's clearer, simpler and more efficient

even if we follow this approach how far result would differ from above approach ???

let say I want to only do the update when a field is "7",

first query will do the task afterwards second query will update all rows having null values.

this approach will even have one more round trip to database

thanks
[ April 03, 2005: Message edited by: Shailesh Chandra ]

[Jeanne Boyarsky]

Shailesh,
It depends on what the requirement is. I interpretted it that either a given value will be provided or null should be searched. Not both at the same time.

If it is the case that null should be searched no matter what, I agree with your approach.

[Shailesh Chandra]

Actually both of us were on same track with different version of statement

Shailesh