Writing MySQL-Based Programs - MySQL - page 92

Perl
Perl DBI represents NULL values using undef . To detect such values, use the de
fined() function; 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 Use of uninitialized value warnings.
To prevent these warnings, test column values that might be undef with defined()
before using them. The following code selects a few columns from the profile table
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, testing multiple column values is ponderous and becomes worse the
more columns there are. To avoid this, test and set undefined values using a loop or map
prior to printing them. The following example uses map :
my $sth = $dbh -> prepare ( "SELECT name, birth, foods FROM profile" );
$sth -> execute ();
while ( my $ref = $sth -> fetchrow_hashref ())
{
map { $ref -> { $_ } = "NULL" unless defined ( $ref -> { $_ }); } keys ( % { $ref });
printf "name: %s, birth: %s, foods: %s\n" ,
$ref -> { name }, $ref -> { birth }, $ref -> { foods };
}
With this technique, the amount of code to perform the tests is constant, not propor‐
tional to the number of columns to be tested. Also, there is no reference to specific
column names, so it can more easily be used in other programs or as the basis for a
utility routine.
If you fetch rows into an array rather than into a hash, use map like this to convert 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 ];
}