Learning MySQL

Handling NULL Values

MySQL operations return undef for fields that have a NULL value; however, Perl handles these values as empty strings, so if we ask it to print the results, we’ll simply get blanks for NULL values. Example 17-7 checks whether fields are NULL and handles them, perhaps by setting them to the string "NULL".

Example 17-7. Perl script to handle NULL values

#!/usr/bin/perl -w
use DBI;
use strict;

my $DB_Database="AnimalDB";
my $DB_Username="root";
my $DB_Password="the_mysql_root_password";
my $dbh=DBI->connect(	"DBI:mysql:host=localhost;database=$DB_Database",
"$DB_Username", "$DB_Password", {PrintError=>0, RaiseError=>0})
    or
    die("Failed connecting to the database ".
     "(error number $DBI::err): $DBI::errstr\n");

my $Query="SELECT Count from Animals";
my $sth=$dbh->prepare($Query);
$sth->execute();
while(my $ref=$sth->fetchrow_hashref("NAME_uc"))
{
    my $Count=$ref->{COUNT};
    if(!defined($Count))
    {
        $Count="NULL";
    }
    print "Count=$Count\n";
}
$sth->finish();
$dbh->disconnect();