Using Perl with MySQL - Learning MySQL - page 554

which is handled correctly by most spreadsheet programs.
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();
Resources
See the MySQL AB page ( http://dev.mysql.com/usingmysql/perl ) and the DBI module
documentation ( http://search.cpan.org/~timb/DBI/DBI.pm ).
Exercises
1. What does the Perl DBI module do?
2. When would you prefer to use fetchrow_array( ) over fetchrow_hashref( ) ?