Recipe 2.7. Identifying NULL Values in Result Sets


Problem

A query result includes NULL values, but you're not sure how to tell where they are.

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

Section 2.5 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:

LanguageNULL-detection value or method
Perl DBI undef value
Ruby DBI nil value
PHP PEAR DBA 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

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\n",         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\n",         $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\n",         $val[0], $val[1], $val[2]; } 

Ruby

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\n", 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

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]\n"); } $result->free (); 

Python

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).

Java

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 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net