Comments (3)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 Kovica commented Permalink

About parameter markers. I'm using them in JDBC application.
For example I have an SQL statement like this
SELECT CODE FROM TABLE1 WHERE NAME = ?
TABLE1 has values
CODE NAME
1 SUSAN
2 NULL

 
when NAME is a value then I get a row out of this statement, but when NAME is NULL I don't get a row out, since the SQL statement should like like this:
SELECT CODE FROM TABLE1 WHERE NAME IS NULL
 
Is this the correct behaviour? I thought that the JDBC driver or the database itself will be a bit more clever. :)

2 SergeRielau commented Permalink

Kovi,

 
That's working as required by the SQL Standard.
Regular comparison operators must never equal anything to NULL including NULL itself.
I think DB2 9.7 can detect the following pattern and optimize for it though:
WHERE c1 = ? OR (c1 IS NULL AND ? IS NULL)
Unfortunately that requires you to bind in the same value twice.
Lats week I discussed NULL on this blog
 
Cheers
Serge

3 Kovica commented Permalink

HI!

 
Thanks for the answer.
It made things clearer. :)
 
Best regards,
Kovi