We have some legacy database loading scripts that do their own interpolation of data into SQL. I want to use prepared statements handles with placeholders and execute them with our raw data.

Old way:

$seen =~ s/'/''/g;
$interpolated = $seen? "'$seen'" : 'NULL';
$dbh->do("INSERT INTO foo (bar) VALUES ($interpolated)");

[download]

which handles the quoting correctly. Notice that any value that is false to Perl is loaded as NULL, including zero and the empty string. This is how we want it.

I want to say 

my $sth = $dbh->prepare('INSERT INTO foo (bar) VALUES (?)');
$sth->execute($seen);

[download]

which will handle the quotes correctly, but loads empty strings as empty strings.

I can code the required behavior explicitly, but I wondered if there was a DBI setting I could tweak to make this happen implicitly. Maybe not for zeros, but at least for empty strings? Surely ours is not the only shop where empty strings are required to be loaded as NULL.

Later: With thanks to keszler, let me clarify that I can certainly say

$sth->execute( map {$_ ? $_ : undef} @seen_values );

if that's what I have to do. But I wanted to tell my team "Look how simple life is when you use DBI with placeholders!", and map {$_ ? $_ : undef} detracts from that simplicity.

Conclusion: Yes to placeholders, and yes to DBI's design decision to map undef to NULL but empty strings etc. to defined values.

If our business expects empty strings to be reported in the database as NULLs, we ought to document that expectation and it's better to be coding for it explicitly.

Special thanks to roboticus for the idea to do this on the database side.

If I get tired of repeating the code for converting empty strings to undef in too many places, I can try to build it in to our existing routine that splits extract records into their component fields.

In reply to Want DBI to load empty strings as NULL by Narveson

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.