Perl DBI NULL and undef

[chefchanyu]

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";
}

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);

[cheekuperl]

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";
        }

[chromatic]

... but that will never execute, because undef always returns the undefined value.

[gsiems]

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

[choroba]

undef $_

undefines $_. To test its definedness, use

defined $_

In case of $_, the parameter is optional.

[Marshall]

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.