3.14 Writing Comparisons Involving NULL in Programs
3.14.1 Problem
You're writing a program
that issues a query, but it fails for NULL values.
3.14.2 Solution
Try writing the comparison selectively for NULL
and non-NULL values.
3.14.3 Discussion
The need to use different comparison operators for
NULL values than for non-NULL
values leads to a subtle danger when constructing query strings
within programs. If you have a value stored in a variable that might
represent a NULL value, you must account for that
if you use the value in comparisons. For example, in Perl,
undef represents a NULL value,
so to construct a statement that finds records in the
taxpayer table matching some arbitrary value in an
$id variable, you cannot do this:
$sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id = ?");
$sth->execute ($id);
The statement fails when $id is
undef, because the resulting query becomes:
SELECT * FROM taxpayer WHERE id = NULL
That statement returns no records�a comparison of
= NULL always fails. To take
into account the possibility that $id may be
undef, construct the query using the appropriate
comparison operator like this:
$operator = (defined ($id) ? "=" : "IS");
$sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id $operator ?");
$sth->execute ($id);
This results in queries as follows for $id values
of undef (NULL) or 43 (not
NULL):
SELECT * FROM taxpayer WHERE id IS NULL
SELECT * FROM taxpayer WHERE id = 43
For inequality tests, set $operator like this
instead:
$operator = (defined ($id) ? "!=" : "IS NOT");
| 
No comments:
Post a Comment