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

In reply to Re: DBI - Handling NULL values by jZed
in thread DBI - Handling NULL values by McDarren

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.