Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Want DBI to load empty strings as NULL

by Narveson (Chaplain)
on Feb 13, 2010 at 18:20 UTC ( [id://823038]=perlquestion: print w/replies, xml ) Need Help??

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

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

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

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.

Replies are listed 'Best First'.
Re: Want DBI to load empty strings as NULL
by keszler (Priest) on Feb 13, 2010 at 18:33 UTC
    You need to change Perl-false values to undef.

    From DBI:

    NULL Values

    Undefined values, or undef, are used to indicate NULL values. You can insert and update columns with a NULL value as you would a non-NULL value. These examples insert and update the column age with a NULL value:

    $sth = $dbh->prepare(qq{ INSERT INTO people (fullname, age) VALUES (?, ?) }); $sth->execute("Joe Bloggs", undef);

    Later

    What I failed to say was that the first sentence quoted above would have been better written as:

    Undefined values, or undef, are THE ONLY WAY used to indicate NULL values.
    SQL, for reasons better understood by by its creators than me, makes a huge distinction between NULL values and either empty strings or zero values. Because of this, a WHERE clause that needs to account for both has to jump through hoops like
    WHERE ISNULL(age, xx) = ISNULL(?, xx)
    or
    $where_clause = defined $age? "age = ?" : "age IS NULL";
    My guess is that due to that NULL vs '' distinction the DBI module intentionally does not treat '' or 0 as NULL.
      My guess is that due to that NULL vs '' distinction the DBI module intentionally does not treat '' or 0 as NULL.
      I'd disagree slightly. Equating undef with NULL seems completely natural to me, as both of them mean "the value is either unknown or inapplicable". Both 0 and '' are known values, so they are neither undef nor NULL.

      The major difference between undef and NULL is just that perl takes the pragmatic course of assuming that the unknown is false, where SQL is strictly correct about it and insists that the truth of any expression depending on an unknown is also unknown.

Re: Want DBI to load empty strings as NULL
by AnomalousMonk (Archbishop) on Feb 13, 2010 at 19:25 UTC
    Look how simple life is ... with placeholders!

    I'm far from an expert when it comes to DB and DBI, but my impression is that most of the 'simplicity' associated with the use of placeholders stems from avoidance of the screaming horrors you will experience in the midst of an SQL injection attack. Almost any syntactic cost would be justified to avoid these terrors, and
        $sth->execute( map {$_ ? $_ : undef} @seen_values );
    seems quite cheap.

      That will also convert the string 0 into undef. Fix:
      $sth->execute( map { defined($_) && length($_) ? $_ : undef } @seen_values );
      or
      $sth->execute( map { no warnings 'uninitialized'; length($_) ? $_ : undef } @seen_values );

        YES DO THIS

        If map is unfamiliar territory, just look up some examples of using map and grep functions on lists (like your list of arguments for execute()).

        Also - don't add single-quotes to your values - they will be added automatically (if necessary) for you.

Re: Want DBI to load empty strings as NULL
by roboticus (Chancellor) on Feb 14, 2010 at 15:10 UTC

    Narveson:

    A couple notes: First, if you want to convert an empty string to a null on the insert, you shouldn't consider the map statement as an argument against placeholders. After all, you'll have to perform the conversion in *any* method you use. It's a different issue entirely.

    Second, if you're considering an empty string as an illegal value in your database, you might consider using a stored procedure to perform your inserts, and enforce it there, so your various perl scripts don't all have to do the checking. Something like:

    create sproc insert_foo @bar as varchar(77) as if @bar = '' set @bar=null insert into foo (bar) values (@bar)

    (Or use the nullif function, etc.)

    ...roboticus

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://823038]
Front-paged by keszler
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others meditating upon the Monastery: (3)
As of 2024-03-29 02:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found