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

Hi all,

I am curious to know if there is a "cleaner" way to check for and handle NULL values, when processing the result set of an SQL query.
Please consider the following code (assume -w and use strict): 
    178     #   Build the first half of the INSERT
    179     #   using the field list we got from the earlier DESCRIBE
    180     #   Of course we only do this if we have any rows to inser
+t
    181     if ($affected_rows) {
    182         my $base_insert = "INSERT IGNORE INTO $thisTable (host
+name, host_id, "
    183             . join("," , @fields)
    184             . ") VALUES (\"$site_hostname\", \"$site_id\", ";
    185
    186         #   Now we iterate through the result set from the pre
+vious query
    187         #   completing each INSERT as we go, and dumping them 
+all into a big list
    188         while (my @row = $dbq->fetchrow_array) {
    189             #   Need to check for NULL's
    190             for(@row) { $_ = "NULL" if !defined $_; }
    191             my $this_insert = $base_insert
    192                 . join("," , map {qq("$_")} @row)
    193                 . ");";
    194             push @central_inserts, $this_insert;
    195         }
    196     }

[download]
The above code is part of a script that is used to backup tables from several remote hosts to a central database. It works "okay", but after running it a few times I discovered that it wasn't correctly handling NULL's.
So I inserted: 
for(@row) { $_ = "NULL" if !defined $_; }

[download]
at line 190.

Is that a reasonable approach?
Is there a better (cleaner) way?

Thanks,
--Darren

Replies are listed 'Best First'.
Re: DBI - Handling NULL values
by Tanktalus (Canon) on Sep 29, 2005 at 04:20 UTC

    Yes. Use placeholders.

    unshift @fields, qw(hostname host_id);
my $insert = "INSERT IGNORE INTO $thisTable (" . join(", ", @fields) .
+ ") VALUES (" . join(",", ('?') x @fields) . ")";

while (my @row = $dbq->fetchrow_array) {
  my $this_insert = [ $site_hostname, $site_id, @row ];
  push @central_insert, $this_insert;
}

    [download]
    Then you will have to prepare the $insert, and execute that statement handle while passing in the variables: 
    my $sth = $dbh->prepare($insert);
$sth->execute(@$_) for @central_insert;

    [download]
    Of course, add in some error handling, and probably turn off autocommit if there's lots of inserts going on at a time.

    Try a Super Search for placeholders - should turn up a lot of hits.

[reply]
[d/l]
[select]
      Excellent, thanks - that works a treat:)
      I'd actually used placeholders in other parts of the same script, but it hadn't occurred to me to use them here.

      And of course by using placeholders, the line: 
      join("," , map {qq("$_")} @row)

      [download]
      in my original example becomes no longer necessary, as the quoting is automagically taken care of by DBI.

      Thanks again,
      --Darren
[reply]
[d/l]
      An alternative to the join's and building the multiple ?'s is using SQL::Abstract: 
      use SQL::Abstract;
my $SA = SQL::Abstract->new;
while (my @row = $dbq->fetchrow_array) {
  my %fields;
  @fields{@fields} = ( $site_hostname, $site_id, @row );   # hash slic
+e to set %fields
  my ($sql, @bind) = $SA->insert( $thisTable, \%fields );
  push @central_insert, [ $sql, \@bind ];
}
...
$dbh->do( $_->[0], {}, @{$_->[1]} ) for @central_insert;

      [download]
      In this case, you lose the ability to prepare the statement, but in general SQL::Abstract can be very handy... Good example is with dynamic where clauses.

      (i'm also not sure why OP needs to queue up the insert's for later and just doesn't do it in the main loop...)
[reply]
[d/l]
[select]
Re: DBI - Handling NULL values
by rnahi (Curate) on Sep 29, 2005 at 05:57 UTC

    Look in the Monastery's Tutorials. DBI Recipes shows several ways of inserting records safely with placeholders. (look at the section "creating an insertion query")

[reply]
Re: DBI - Handling NULL values
by jZed (Prior) on Sep 29, 2005 at 06:34 UTC
    As pg said, DBI uses undef to represent NULL values. This has two implications: 1) when you read a record that has NULLs, the values in the NULL fields will be undefined so if you want to print them, you'll need to change them to empty strings or a string like 'NULL' or 'UNKNOWN' or 'EMPTY' because you can't print undef, you can only print strings. However, 2) when you insert NULL values into the database, you insert undefs, not strings like 'NULL' or 'EMPTY'. DBI and the DBD will pass the undef to the RDBMS in a way that the RDBMS will be able to use its own internal reprsentation of NULLs. So this means that you turn the undef into a string before you print but that you leave the undef as undef when you insert.

    A related issue is the difference between the SQL keyword NULL (which never has quotes around it) and the literal string 'NULL' which, because it has quotes around it, is a string value, not a NULL value. So: 

    This uses the SQL keyword NULL to insert a NULL into the table:

  $dbh->do("INSERT INTO x VALUES (1,NULL)");

But, this inserts the literal string N-U-L-L into the table: 

  $dbh->do("INSERT INTO x VALUES (1,'NULL')");

And when using placeholders.

This inserts a NULL value:

  $sth->execute(1,undef);

But this inserts the literal string N-U-L-L:
  
  $sth->execute(1,'NULL');
[reply]
      Yes, thanks
      It actually occurred to me soon after my original post that my initial code may not have been doing what I expected. I was going to test that but then I got the reply from [id://tanktalus] and got sidetracked.

      Your examples make it perfectly clear, and further demonstrate why it is much better practice to use placeholders and let DBI do all the dirty work.

      I'll be sure to remember that in future :)
[reply]
Re: DBI - Handling NULL values
by pg (Canon) on Sep 29, 2005 at 05:17 UTC

    DBI maps SQL NULL with Perl undef, which makes sense.

[reply]

Back to Seekers of Perl Wisdom