Don't ask to ask, just ask | |
PerlMonks |
Want DBI to load empty strings as NULLby 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:
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
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.
Back to
Seekers of Perl Wisdom
|
|