Perl DBI NULL and undef

chefchanyu has asked for the wisdom of the Perl Monks concerning the following question:

Hi: How can I separate the undef data and NULL data? the following code work ok for undef/null data but Ｉwon't know which one is undef and which is null they all print out " ".

use strict;
use DBI;
use Data::Dumper;

my $dbh = 
    DBI->connect("dBi:mysql:cacti:localhost",
    "cacti", 
    "cacti") 
    or die("Cannot connect: $DBI::errstr\n");


my $sth = $dbh->prepare("select * from data_template_data");

$sth->execute;



my $names = $sth->{NAME};
for (@$names)
{
    printf("%-10s",$_);
}
print "\n";

while (my $row_ref = $sth->fetch())
{
    for (@$row_ref)
    {
        if (defined($_))
        {
            printf("%-10s",$_);
        }
#        elsif ($_ eq '')
#        {
#            print 'NULL';
#        }
        elsif (undef($_))
        {
            print "\' \'";
        }
    }
    print "\n";
}
[download]

Comment on Perl DBI NULL and undef Download Code

Replies are listed 'Best First'.
Re: Perl DBI NULL and undef by NetWallah (Canon) on Aug 22, 2012 at 04:57 UTC
You are missing an 'else statement corresponding to your `if (defined($_))` [download] Also, your statement `elsif (undef($_))` [download] probably does not do what you think it does. Please read the documentation for what that function returns. It also effects the value of $_, which is probably undesirable. instead of that, just replace that line by `else` [download] I hope life isn't a big joke, because I don't get it. -SNL	[reply] [d/l] [select]
Re: Perl DBI NULL and undef by rovf (Priest) on Aug 22, 2012 at 08:26 UTC
How can I separate the undef data and NULL data? Hmmmm.... This question doesn't make sense to me, for 'undef' is a concept in the Perl domain, and 'NULL' is a concept in the SQL domain. This means that in Perl you don't have NULL, and in SQL, you don't have undef. From DBI: NULL values are represented by undefined values in Perl -- Ronald Fischer <ynnor@mm.st>	[reply] [d/l]
Re: Perl DBI NULL and undef by mje (Curate) on Sep 03, 2012 at 15:27 UTC
`SQL> create table mje (a int, b varchar(10) null default null); SQLRowCount returns -1 SQL> insert into mje (a,b) values(1,'fred'); SQLRowCount returns 1 SQL> insert into mje (a,b) values(2, ''); SQLRowCount returns 1 SQL> insert into mje (a) values(3); SQLRowCount returns 1 SQL> select * from mje; +------------+-----------+ \| a \| b \| +------------+-----------+ \| 1 \| fred \| \| 2 \| \| \| 3 \| \| +------------+-----------+ SQLRowCount returns -1 3 rows fetched` [download] `perl -MDBI -le 'my $h = DBI->connect; use Data::Dumper; my $r = $h->se +lectall_arrayref("select * from mje");print Dumper($r);' $VAR1 = [ [ 1, 'fred' ], [ 2, '' ], [ 3, undef ] ];` [download] So empty strings in this case are defined and NULLs in the databases are returned as undef in Perl. undef is a Perl thing and NULL is a SQL thing so when you say "How can I separate the undef data and NULL data" it does not make sense.	[reply] [d/l] [select]
Re: Perl DBI NULL and undef by BillKSmith (Monsignor) on Aug 22, 2012 at 13:29 UTC
You seem to think you have three cases (a valid string, a null string, and and undefined value.) Ronald's post suggests that this is not true for DBI. Assuming that it it true, the answer to your question is to do the tests in the right order. `if (!defined){ print qq(''); } elsif (!length) { print 'NULL'; } else { print; }` [download] Bill	[reply] [d/l]