Want DBI to load empty strings as NULL

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

Comment on Want DBI to load empty strings as NULL Select or Download Code

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);` [download] 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)` [download] or `$where_clause = defined $age? "age = ?" : "age IS NULL";` [download] My guess is that due to that NULL vs '' distinction the DBI module intentionally does not treat '' or 0 as NULL.	[reply] [d/l] [select]
Re^2: Want DBI to load empty strings as NULL by dsheroh (Monsignor) on Feb 14, 2010 at 10:04 UTC
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.	[reply] [d/l] [select]
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.	[reply] [d/l]
Re^2: Want DBI to load empty strings as NULL by ikegami (Patriarch) on Feb 14, 2010 at 16:32 UTC
That will also convert the string `0` into undef. Fix: `$sth->execute( map { defined($_) && length($_) ? $_ : undef } @seen_values );` [download] or `$sth->execute( map { no warnings 'uninitialized'; length($_) ? $_ : undef } @seen_values );` [download]	[reply] [d/l] [select]
Re^3: Want DBI to load empty strings as NULL by jdrago999 (Pilgrim) on Feb 15, 2010 at 21:40 UTC
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.	[reply] [d/l] [select]
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)` [download] (Or use the nullif function, etc.) ...roboticus	[reply] [d/l]