I've been emulating a pseudo-SQL command, UNLOAD, in DBI and
DBD::Informix, and one of the messy parts is handling the mapping of
undef into an empty string so as not to get warnings with 'perl -w'.
I've also routinely encountered this as a usability problem in ordinary
operation - and DBD::Informix testing. Would there be any merit in
having a DBI-wide mechanism that identifies whether the SQL NULL
non-values should be mapped to Perl undef or to a defined string -
probably the empty string?
Here are some code fragments. The basic UNLOAD format is a text string
for each field, followed by a user-selectable delimiter, default '|'.
Each record ends with a newline. Backslash is used as the escape
character: backslash, newline and delimiter. The 'byte blob' encoding
is ghastly (but simple), and doesn't affect my discussion.
# Common code prefix for all options.
use DBI;
$dbdelim = $ENV{DBDELIMITER} ? $ENV{DBDELIMITER} : "|";
$dbh = DBI->connect('dbi:Informix:dbase@server', $username, $password,
{RaiseError=>1});
$dbh->{ChopBlanks} = 1;
$sth = $dbh->prepare("SELECT * FROM Customers");
$sth->execute;
# Option 1: ...omitted...
# Option 2: works with no nulls in data; noisy if -w flag set
# -- no escapes of $dbdelim in data
# -- no escapes of embedded newlines
# -- byte blobs are not hex-encoded
$, = $dbdelim;
while ($ref = $sth->fetchrow_arrayref)
{
print @$ref, "\n";
}
# Option 3: maps nulls to empty strings; quiet if -w flag set
# -- no escapes of $dbdelim in data
# -- no escapes of embedded newlines
# -- byte blobs are not hex-encoded
$, = $dbdelim;
while ($ref = $sth->fetchrow_arrayref)
{
my(@row) = map { defined $_ ? $_ : "" } @$ref;
print @row, "\n";
}
# Option 4: ...omitted...
# Option 5: ...omitted...
If there's any significant resistance or lack of consensus, I'll simply
add an Informix-specific attribute that deals with this issue (settable
per $dbh and inherited by $sth, overridable per $sth). OTOH, I suspect
I'm not the only person to have noticed that this is a (minor) usability
problem and maybe having it standardized would be of benefit to most
users.
What say you?
--
Jonathan Leffler #include <disclaimer.h>
STSM, Informix Database Engineering, IBM Data Management Solutions
Phone: +1 650-926-6921 Tie-line: 630-6921
Email: [EMAIL PROTECTED] ([EMAIL PROTECTED])
Notes ID: Jonathan Leffler/Menlo Park/IBM@IBMUS
Guardian of DBD::Informix v1.00.PC1 -- http://dbi.perl.org
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Please update your address book to use [EMAIL PROTECTED] because
[EMAIL PROTECTED] will not work starting 2002-07-01. Expect
slower responses because I can't use Lotus Notes as fast as Unix
email. One day, this signature will shrink!
