Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Perl DBI NULL and undef

by chefchanyu (Novice)
on Aug 22, 2012 at 03:10 UTC ( #988938=perlquestion: print w/ replies, xml ) Need Help??
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 Iwon'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"; }

Comment on Perl DBI NULL and undef
Download Code
Re: Perl DBI NULL and undef
by NetWallah (Abbot) on Aug 22, 2012 at 04:57 UTC
    You are missing an 'else statement corresponding to your
    if (defined($_))
    Also, your statement
    elsif (undef($_))
    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

                 I hope life isn't a big joke, because I don't get it.
                       -SNL

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>
Re: Perl DBI NULL and undef
by BillKSmith (Chaplain) 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; }
    Bill
Re: Perl DBI NULL and undef
by mje (Deacon) 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
    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 ] ];

    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://988938]
Approved by rovf
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (11)
As of 2014-08-20 09:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (108 votes), past polls