Database Reference
In-Depth Information
2.7. Identifying NULL Values in Result Sets
Problem
A query result includes
NULL
values, but you're not sure how to identify them.
Solution
Your API probably has some special value that represents
NULL
by convention. You just
have to know what it is and how to test for it.
Discussion
Recipe 2.5
describes how to refer to
NULL
values when you send statements
to
the da‐
tabase server. In this section, we'll deal instead with the question of how to recognize
and process
NULL
values returned
from
the database server. In general, this is a matter
of knowing what special value the API maps
NULL
values to, or what method to call. The
following table shows these values:
Language NULL-detection value or method
Perl DBI
undef
value
Ruby DBI
nil
value
PHP PDO
NULL
value
Python DB API
None
value
Java JDBC
wasNull()
method
The following sections show a very simple application of
NULL
value detection. The
examples retrieve a result set and print all values in it, mapping
NULL
values onto the
printable string
"NULL"
.
To make sure that the
profile
table has a row that contains some
NULL
values, use
mysql
to execute the following
INSERT
statement, then execute the
SELECT
statement to verify
that the resulting row has the expected values:
mysql>
INSERT INTO profile (name) VALUES('Amabel');
mysql>
SELECT * FROM profile WHERE name = 'Amabel';
+----+--------+-------+-------+-------+------+
| id | name | birth | color | foods | cats |
+----+--------+-------+-------+-------+------+
| 9 | Amabel | NULL | NULL | NULL | NULL |
+----+--------+-------+-------+-------+------+
The
id
column might contain a different number, but the other columns should appear
as shown, with values of
NULL
.
