Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Perl DBI NULL and undef

by chefchanyu (Novice)
on Aug 22, 2012 at 00:48 UTC ( #988914=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"; }
so how can I print out the "NULL" with "NULL" and '' with "undef"? I try this code work for "NULL" but not for "undef":
if(defined($_)) { print $_; } elsif(undef($_)) { print "undef"; } else { print "NULL"; }
the result:
[cacti@cacti other]$ perl cacti.pl id local_data_template_data_idlocal_data_iddata_template_iddata +_input_idt_name name name_cachedata_source_patht_active acti +ve t_rrd_steprrd_step t_rra_id 3 0 0 3 2 on |host_desc +ription| - Hard Drive Space NULL on + 300 4 0 0 4 1 |host_desc +ription| - CPU Usage - System NULL on + 300 5 0 0 5 1 |host_desc +ription| - CPU Usage - User NULL on + 300 6 0 0 6 1 |host_desc +ription| - CPU Usage - Nice NULL on + 300 7 0 0 7 2 on |host_desc +ription| - Noise Level NULL on 300 + 8 0 0 8 2 on |host_desc +ription| - Signal Level NULL on 30 +0 9 0 0 9 2 on |host_desc +ription| - Wireless Transmits NULL on + 300 10 0 0 10 2 on |host_desc +ription| - Wireless Re-Transmits NULL on + 300 11 0 0 11 4 |host_desc +ription| - Load Average NULL on 30 +0 13 0 0 13 6 |host_desc +ription| - Memory - Free NULL on 3 +00 15 0 0 15 6 |host_desc +ription| - Memory - Free Swap NULL on + 300 16 0 0 16 7 |host_desc +ription| - Processes NULL on 300 + 17 0 0 17 5 |host_desc +ription| - Logged in Users NULL on + 300 18 0 0 18 10 |host_desc +ription| - Ping Host NULL on 300 + 19 0 0 19 1 |host_desc +ription| - Total Users NULL on 300 + 20 0 0 20 1 |host_desc +ription| - Total Logins NULL on 30 +0 22 0 0 22 1 |host_desc +ription| - File System Reads NULL on + 300 23 0 0 23 1 |host_desc +ription| - File System Writes NULL on + 300 24 0 0 24 1 |host_desc +ription| - Cache Checks NULL on 30 +0 25 0 0 25 1 |host_desc +ription| - Cache Hits NULL on 300 + 26 0 0 26 1 |host_desc +ription| - Open Files NULL on 300 + 27 0 0 27 1 |host_desc +ription| - 5 Minute CPU NULL on 30 +0 30 0 0 30 1 |host_desc +ription| - Load Average - 1 Minute NULL on + 300 31 0 0 31 1 |host_desc +ription| - Load Average - 5 Minute NULL on + 300 32 0 0 32 1 |host_desc +ription| - Load Average - 15 Minute NULL on + 300 33 0 0 33 1 |host_desc +ription| - Memory - Buffers NULL on + 300 34 0 0 34 1 |host_desc +ription| - Memory - Free NULL on 3 +00 35 0 0 35 2 on |host_desc +ription| - Volumes NULL on 300 + 36 0 0 36 2 on |host_desc +ription| - Directory Entries NULL on + 300 37 0 0 37 11 on |host_desc +ription| - Hard Drive Space NULL on + 300 38 0 0 38 2 on |host_desc +ription| - Errors/Discards NULL on + 300 39 0 0 39 2 on |host_desc +ription| - Unicast Packets NULL on + 300 40 0 0 40 2 on |host_desc +ription| - Non-Unicast Packets NULL on + 300 41 0 0 41 2 on |host_desc +ription| - Traffic NULL on 300 + 55 0 0 42 2 |host_desc +ription| - CPU Utilization NULL on + 300 56 0 0 43 12 |host_desc +ription| - Hard Drive Space NULL on + 300 57 0 0 44 12 |host_desc +ription| - CPU Utilization NULL on + 300 58 0 0 45 1 |host_desc +ription| - Processes NULL on 300 + 59 0 0 46 1 |host_desc +ription| - Logged in Users NULL on + 300 62 13 3 13 6 NULL|host_descriptio +n| - Memory - FreeLocalhost - Memory - Free<path_rra>/localhost_mem_b +uffers_3.rrdNULLon NULL300 NULL 63 15 4 15 6 NULL|host_descriptio +n| - Memory - Free SwapLocalhost - Memory - Free Swap<path_rra>/local +host_mem_swap_4.rrdNULLon NULL300 NULL 64 11 5 11 4 NULL|host_descriptio +n| - Load AverageLocalhost - Load Average<path_rra>/localhost_load_1m +in_5.rrdNULLon NULL300 NULL 65 17 6 17 5 NULL|host_descriptio +n| - Logged in UsersLocalhost - Logged in Users<path_rra>/localhost_u +sers_6.rrdNULLon NULL300 NULL 66 16 7 16 7 NULL|host_descriptio +n| - ProcessesLocalhost - Processes<path_rra>/localhost_proc_7.rrdNUL +Lon NULL300 NULL 68 0 0 47 1 |host_desc +ription| - Memory - Cache NULL on +300 69 0 0 48 1 on |host_desc +ription| - NULL on 300 + 70 0 0 49 0 10 minutes + NULL on 600 71 70 8 49 0 NULL10 minutes10 min +utes/var/www/cacti_rrd/MMS/Daily/CDR_TOTAL_901S902M905S906WAPENC.rrdN +ULLon NULL600 NULL 72 70 9 49 0 NULL10 minutes10 min +utes/var/www/cacti_rrd/MMS/Daily/CDR_TOTAL_901R902M905S906FOREIGNOPTU +S.rrdNULLon NULL600 NULL [cacti@cacti other]$
my database:
INSERT INTO data_template_data (id, local_data_template_data_id, local +_data_id, data_template_id, data_input_id, t_name, name, name_cache, +data_source_path, t_active, active, t_rrd_step, rrd_step, t_rra_id) V +ALUES (3, 0, 0, 3, 2, 'on', '|host_description| - Hard Drive Space', '', N +ULL, '', 'on', '', 300, ''), (4, 0, 0, 4, 1, '', '|host_description| - CPU Usage - System', '', N +ULL, '', 'on', '', 300, ''), (5, 0, 0, 5, 1, '', '|host_description| - CPU Usage - User', '', NUL +L, '', 'on', '', 300, ''), (6, 0, 0, 6, 1, '', '|host_description| - CPU Usage - Nice', '', NUL +L, '', 'on', '', 300, ''), (7, 0, 0, 7, 2, 'on', '|host_description| - Noise Level', '', NULL, +'', 'on', '', 300, ''), (8, 0, 0, 8, 2, 'on', '|host_description| - Signal Level', '', NULL, + '', 'on', '', 300, ''), (9, 0, 0, 9, 2, 'on', '|host_description| - Wireless Transmits', '', + NULL, '', 'on', '', 300, ''), (10, 0, 0, 10, 2, 'on', '|host_description| - Wireless Re-Transmits' +, '', NULL, '', 'on', '', 300, ''), (11, 0, 0, 11, 4, '', '|host_description| - Load Average', '', NULL, + '', 'on', '', 300, ''), (13, 0, 0, 13, 6, '', '|host_description| - Memory - Free', '', NULL +, '', 'on', '', 300, ''), (15, 0, 0, 15, 6, '', '|host_description| - Memory - Free Swap', '', + NULL, '', 'on', '', 300, ''), (16, 0, 0, 16, 7, '', '|host_description| - Processes', '', NULL, '' +, 'on', '', 300, ''), (17, 0, 0, 17, 5, '', '|host_description| - Logged in Users', '', NU +LL, '', 'on', '', 300, ''), (18, 0, 0, 18, 10, '', '|host_description| - Ping Host', '', NULL, ' +', 'on', '', 300, ''), (19, 0, 0, 19, 1, '', '|host_description| - Total Users', '', NULL, +'', 'on', '', 300, ''), (20, 0, 0, 20, 1, '', '|host_description| - Total Logins', '', NULL, + '', 'on', '', 300, ''), (22, 0, 0, 22, 1, '', '|host_description| - File System Reads', '', +NULL, '', 'on', '', 300, ''), (23, 0, 0, 23, 1, '', '|host_description| - File System Writes', '', + NULL, '', 'on', '', 300, ''), (24, 0, 0, 24, 1, '', '|host_description| - Cache Checks', '', NULL, + '', 'on', '', 300, ''), (25, 0, 0, 25, 1, '', '|host_description| - Cache Hits', '', NULL, ' +', 'on', '', 300, ''), (26, 0, 0, 26, 1, '', '|host_description| - Open Files', '', NULL, ' +', 'on', '', 300, ''), (27, 0, 0, 27, 1, '', '|host_description| - 5 Minute CPU', '', NULL, + '', 'on', '', 300, ''), (30, 0, 0, 30, 1, '', '|host_description| - Load Average - 1 Minute' +, '', NULL, '', 'on', '', 300, ''), (31, 0, 0, 31, 1, '', '|host_description| - Load Average - 5 Minute' +, '', NULL, '', 'on', '', 300, ''), (32, 0, 0, 32, 1, '', '|host_description| - Load Average - 15 Minute +', '', NULL, '', 'on', '', 300, ''), (33, 0, 0, 33, 1, '', '|host_description| - Memory - Buffers', '', N +ULL, '', 'on', '', 300, ''), (34, 0, 0, 34, 1, '', '|host_description| - Memory - Free', '', NULL +, '', 'on', '', 300, ''), (35, 0, 0, 35, 2, 'on', '|host_description| - Volumes', '', NULL, '' +, 'on', '', 300, ''), (36, 0, 0, 36, 2, 'on', '|host_description| - Directory Entries', '' +, NULL, '', 'on', '', 300, ''), (37, 0, 0, 37, 11, 'on', '|host_description| - Hard Drive Space', '' +, NULL, '', 'on', '', 300, ''), (38, 0, 0, 38, 2, 'on', '|host_description| - Errors/Discards', '', +NULL, '', 'on', '', 300, ''), (39, 0, 0, 39, 2, 'on', '|host_description| - Unicast Packets', '', +NULL, '', 'on', '', 300, ''), (40, 0, 0, 40, 2, 'on', '|host_description| - Non-Unicast Packets', +'', NULL, '', 'on', '', 300, ''), (41, 0, 0, 41, 2, 'on', '|host_description| - Traffic', '', NULL, '' +, 'on', '', 300, ''), (55, 0, 0, 42, 2, '', '|host_description| - CPU Utilization', '', NU +LL, '', 'on', '', 300, ''), (56, 0, 0, 43, 12, '', '|host_description| - Hard Drive Space', '', +NULL, '', 'on', '', 300, ''), (57, 0, 0, 44, 12, '', '|host_description| - CPU Utilization', '', N +ULL, '', 'on', '', 300, ''), (58, 0, 0, 45, 1, '', '|host_description| - Processes', '', NULL, '' +, 'on', '', 300, ''), (59, 0, 0, 46, 1, '', '|host_description| - Logged in Users', '', NU +LL, '', 'on', '', 300, ''), (62, 13, 3, 13, 6, NULL, '|host_description| - Memory - Free', 'Loca +lhost - Memory - Free', '<path_rra>/localhost_mem_buffers_3.rrd', NUL +L, 'on', NULL, 300, NULL), (63, 15, 4, 15, 6, NULL, '|host_description| - Memory - Free Swap', +'Localhost - Memory - Free Swap', '<path_rra>/localhost_mem_swap_4.rr +d', NULL, 'on', NULL, 300, NULL), (64, 11, 5, 11, 4, NULL, '|host_description| - Load Average', 'Local +host - Load Average', '<path_rra>/localhost_load_1min_5.rrd', NULL, ' +on', NULL, 300, NULL), (65, 17, 6, 17, 5, NULL, '|host_description| - Logged in Users', 'Lo +calhost - Logged in Users', '<path_rra>/localhost_users_6.rrd', NULL, + 'on', NULL, 300, NULL), (66, 16, 7, 16, 7, NULL, '|host_description| - Processes', 'Localhos +t - Processes', '<path_rra>/localhost_proc_7.rrd', NULL, 'on', NULL, +300, NULL), (68, 0, 0, 47, 1, '', '|host_description| - Memory - Cache', '', NUL +L, '', 'on', '', 300, ''), (69, 0, 0, 48, 1, 'on', '|host_description| -', '', NULL, '', 'on', +'', 300, ''), (70, 0, 0, 49, 0, '', '10 minutes', '', NULL, '', 'on', '', 600, '') +, (71, 70, 8, 49, 0, NULL, '10 minutes', '10 minutes', '/var/www/cacti +_rrd/MMS/Daily/CDR_TOTAL_901S902M905S906WAPENC.rrd', NULL, 'on', NULL +, 600, NULL), (72, 70, 9, 49, 0, NULL, '10 minutes', '10 minutes', '/var/www/cacti +_rrd/MMS/Daily/CDR_TOTAL_901R902M905S906FOREIGNOPTUS.rrd', NULL, 'on' +, NULL, 600, NULL);

Replies are listed 'Best First'.
Re: Perl DBI NULL and undef
by cheekuperl (Monk) on Aug 22, 2012 at 02:48 UTC
    As per DBI.pm doc,
    NULL values are represented by undefined values in Perl
    So, I suppose the last elsif should be like:
    elsif (undef($_)) { print "NULL"; }
Re: Perl DBI NULL and undef
by gsiems (Deacon) on Aug 22, 2012 at 02:25 UTC

    You cannot differentiate between the two as they are merely different labels for the same thing-- that is, a value which is not defined.

Re: Perl DBI NULL and undef
by choroba (Bishop) on Aug 22, 2012 at 08:36 UTC
    undef $_
    undefines $_. To test its definedness, use
    defined $_
    In case of $_, the parameter is optional.
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Perl DBI NULL and undef
by Marshall (Abbot) on Aug 22, 2012 at 09:26 UTC
    Update#2:
    NULL will be treated as an undefined value.
    When you query back this row, you will get undef - there is no distinction between NULL and undef. NULL and undef mean the same thing: there is no data, i.e. the value is "unknown".

    I asked for, but didn't get the schema (what kind of field each of these variables is stored as). However it appears to me that these NULL values relate to text fields (either char() or varchar()).

    Since you are creating the DB, I would put a null string instead of NULL for these text fields. A null string is not "undefined" and you won't get "undef" values as a result of a query. You could also put in some application specific string like "UNKNOWN", "NOT_SPECIFIED" or whatever. It is possible to store the string "NULL" or "null" instead of undef, but that could get confusing.

    On input when creating the DB:

    #!/usr/bin/perl -w use strict; my $line = "36,'', NULL, '', 'on', '', 300"; my @tokens = split(/\,\s*/, $line); foreach (@tokens) { $_ = "''" if $_ eq 'NULL'; print "$_\n"; } __END__ 36 '' '' '' ## NULL is now an empty string 'on' '' 300
    On output if undef values exist:
    foreach (@tokens) { $_ //= ''; #sets undefined value to empty string #the // operator (new in Perl 5.10) tests #for "defined-ness" instead of truthful-ness. print "$_\n"; }
    ==== original post continues ==
    # each line has 14 comma separated tokens
    # this does not appear to "jive" with your input statement
    # Note: lost variable names due to reap of your subsequent post.
    # Keep posting in the same thread - don't start new threads.
    # To display this takes a 100 char line - no problem.
    # but this wraps in Monk format

    Show the create table statement for this DB.

    Could be that you on input, put "" instead of the string NULL.
    But what is "right" depends upon the values that are allowed when you create the table.

    __DATA__ 3, 0, 0, 3, 2, 'on', 'host_description - Hard Drive Space', '', + NULL, '', 'on', '', 300, '' 4, 0, 0, 4, 1, '', 'host_description - CPU Usage - System', '', + NULL, '', 'on', '', 300, '' 5, 0, 0, 5, 1, '', 'host_description - CPU Usage - User', '', + NULL, '', 'on', '', 300, '' 6, 0, 0, 6, 1, '', 'host_description - CPU Usage - Nice', '', + NULL, '', 'on', '', 300, '' 7, 0, 0, 7, 2, 'on', 'host_description - Noise Level', '', + NULL, '', 'on', '', 300, '' 8, 0, 0, 8, 2, 'on', 'host_description - Signal Level', '', + NULL, '', 'on', '', 300, '' 9, 0, 0, 9, 2, 'on', 'host_description - Wireless Transmits', '', + NULL, '', 'on', '', 300, '' 10, 0, 0, 10, 2, 'on', 'host_description - Wireless Re-Transmits', '', + NULL, '', 'on', '', 300, ''

    Update:

    When you create the DB, I would suggest that you not use "" as a value.
    Give each field a value that makes sense, e.g. 'on' or 'off'. Don't fiddle with stuff like "the null string means 'off' ", if you mean off, say 'off'.

    3, 0, 0, 3, 2, 'on', 4, 0, 0, 4, 1, '', # 4, 0, 0, 4, 1, 'off' 5, 0, 0, 5, 1, '', # 5, 0, 0, 5, 1, 'off', 6, 0, 0, 6, 1, '', # 6, 0, 0, 6, 1, 'off', 7, 0, 0, 7, 2, 'on', 8, 0, 0, 8, 2, 'on', 9, 0, 0, 9, 2, 'on', 10, 0, 0, 10, 2, 'on',

    You could encode 'on' to be '1' and 'off' to be 0, but that optimization is often not needed. It could be necessary, but often it is not. Encoding "" to mean 'off' is, in my opinion not a good idea.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2018-09-24 23:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Eventually, "covfefe" will come to mean:













    Results (195 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!