O'Reilly logo

Learning MySQL by Hugh E. Williams, Seyed M.M. Tahaghoghi

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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();

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required