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.
Handling Special Characters and NULL Values in Statements
described how to refer to
NULL
values when you send
statements to the database server. In this
section, we’ll deal instead with the question of how to recognize and
process NULL
values that are
returned from the database server. In general,
this is a matter of knowing what special value the API maps NULL
values onto, or what method to call.
These values are shown in the following table:
Language | NULL-detection value or method |
---|---|
Perl DBI | undef value |
Ruby DBI | nil value |
PHP PEAR DB | A NULL or unset 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 the profile
table has a row that contains some NULL
values, use mysql to issue the following INSERT
statement, and then issue the
SELECT
statement to verify that the
resulting row has the expected values:
mysql>INSERT INTO profile (name) VALUES('Juan'),
mysql>SELECT * FROM profile WHERE name = 'Juan';
+----+------+-------+-------+-------+------+ | id | name | birth | color | foods | cats | +----+------+-------+-------+-------+------+ | 11 | Juan | NULL | NULL | NULL | NULL | +----+------+-------+-------+-------+------+
The id
column might contain a
different number, but the other columns should appear as shown, with
values of NULL
.
Perl DBI represents NULL
values using undef
. It’s easy to
detect such values using the defined()
function, and it’s
particularly important to do so if you enable warnings with the
Perl -w
option or by including a
use
warnings
line in your script. Otherwise,
accessing undef
values causes
Perl to issue the following complaint:
Use of uninitialized value
To avoid this warning, test column values that might be
undef
with
defined()
before using them. The following code selects a few columns from the
profile
column and prints
“NULL” for any undefined values in each row. This makes
NULL
values explicit in the
output without activating any warning messages:
my $sth = $dbh->prepare ("SELECT name, birth, foods FROM profile"); $sth->execute (); while (my $ref = $sth->fetchrow_hashref ()) { printf "name: %s, birth: %s, foods: %s ", defined ($ref->{name}) ? $ref->{name} : "NULL", defined ($ref->{birth}) ? $ref->{birth} : "NULL", defined ($ref->{foods}) ? $ref->{foods} : "NULL"; }
Unfortunately, all that testing of column values is ponderous and becomes worse the more columns there are. To avoid this, you can test and set undefined values in a loop prior to printing them. Then the amount of code you have to write to perform the tests is constant, not proportional to the number of columns to be tested. The loop also makes no reference to specific column names, so it can more easily be copied and pasted to other programs or used as the basis for a utility routine:
my $sth = $dbh->prepare ("SELECT name, birth, foods FROM profile"); $sth->execute (); while (my $ref = $sth->fetchrow_hashref ()) { foreach my $key (keys (%{$ref})) { $ref->{$key} = "NULL" unless defined ($ref->{$key}); } printf "name: %s, birth: %s, foods: %s ", $ref->{name}, $ref->{birth}, $ref->{foods}; }
If you fetch rows into an array rather than into a hash, you
can use map
to convert any
undef
values:
my $sth = $dbh->prepare ("SELECT name, birth, foods FROM profile"); $sth->execute (); while (my @val = $sth->fetchrow_array ()) { @val = map { defined ($_) ? $_ : "NULL" } @val; printf "name: %s, birth: %s, foods: %s ", $val[0], $val[1], $val[2]; }
Ruby DBI represents NULL
values using nil
, which can be identified by applying
the nil?
method to a value. The
following example uses nil?
to
determine whether to print result set values as is or as the string
"NULL"
for NULL
values:
dbh.execute("SELECT name, birth, foods FROM profile") do |sth| sth.fetch do |row| for i in 0...row.length row[i] = "NULL" if row[i].nil? # is the column value NULL? end printf "id: %s, name: %s, cats: %s ", row[0], row[1], row[2] end end
A shorter alternative to the for
loop is the collect!
method, which takes each array
element in turn and replaces it with the value returned by the code
block:
row.collect! { |val| val.nil? ? "NULL" : val }
PHP represents SQL NULL
values in result sets as the PHP NULL
value. To determine whether a value
from a result set represents a NULL
value, compare it to the PHP NULL
value using the
===
triple equal
operator:
if ($val === NULL) { # $val is a NULL value }
In PHP, the triple equal operator means “exactly equal
to.” The
usual ==
equal to
comparison operator is not suitable here. If you use ==
, PHP considers the NULL
value, the empty string, and 0
all equal to each other.
An alternative to using ===
to test for NULL
values is to
use
isset()
:
if (!isset ($val)) { # $val is a NULL value }
The following code uses the ===
operator to identify NULL
values in a result set and print them
as the string "NULL"
:
$result =& $conn->query ("SELECT name, birth, foods FROM profile"); if (PEAR::isError ($result)) die ("Oops, the statement failed"); while ($row =& $result->fetchRow ()) { foreach ($row as $key => $value) { if ($row[$key] === NULL) $row[$key] = "NULL"; } print ("name: $row[0], birth: $row[1], foods: $row[2] "); } $result->free ();
Python DB-API programs represent NULL
in result sets using None
. The following example shows how to
detect NULL
values:
cursor = conn.cursor () cursor.execute ("SELECT name, birth, foods FROM profile") for row in cursor.fetchall (): row = list (row) # convert nonmutable tuple to mutable list for i in range (0, len (row)): if row[i] == None: # is the column value NULL? row[i] = "NULL" print "name: %s, birth: %s, foods: %s" % (row[0], row[1], row[2]) cursor.close ()
The inner loop checks for NULL
column values by looking for None
and converts them to the string
"NULL"
. Note how the example
converts row
to a mutable object
prior to the loop;
fetchall()
returns rows as sequence values, which are nonmutable
(read-only).
For JDBC programs, if it’s possible for a column in a result set to
contain NULL
values, it’s best to
check for them explicitly. The way to do this is to fetch the value
and then invoke wasNull()
,
which returns true
if the column is NULL
and
false
otherwise. For
example:
Object obj = rs.getObject (index); if (rs.wasNull ()) { /* the value's a NULL */ }
The preceding example uses
getObject()
,
but the principle holds for other get
XXX
()
calls as well.
Here’s an example that prints each row of a result set as a
comma-separated list of values, with "NULL"
printed for each NULL
value:
Statement s = conn.createStatement (); s.executeQuery ("SELECT name, birth, foods FROM profile"); ResultSet rs = s.getResultSet (); ResultSetMetaData md = rs.getMetaData (); int ncols = md.getColumnCount (); while (rs.next ()) // loop through rows of result set { for (int i = 0; i < ncols; i++) // loop through columns { String val = rs.getString (i+1); if (i > 0) System.out.print (", "); if (rs.wasNull ()) System.out.print ("NULL"); else System.out.print (val); } System.out.println (); } rs.close (); // close result set s.close (); // close statement
18.226.165.70