On Tue, 11 Sep 2001 16:26:13 -0400 (EDT), Rob Ransbottom wrote:
>With the below code, with PostgreSQL, with Perl 5.005.
>I wish to map Perl's 'undef' to SQL's 'NULL' but get the
>warning
>
> Use of uninitialized value at /home/rir/Rbks/Lib/Dept.pm line 26.
>
>when passing in a hash object like:
>
> { dept => "somestring", descr => undef }
>
>This is proper perl behavior but I'd like to accomplish
>this without warnings.
...
> sub store {
> carp "Wrong argument count to store" unless ( @_ == 1);
> my $self = shift @_;
> $dbh->do( #### line 26
> "insert into Dept ( dept, descr)
> values ( '$self->{dept}', '$self->{descr}')"
> );
> }
It's a good thing perl gives you that warning, because this code snippet
won't do what you want. In fact, if one of the two (or both) is undef,
you'll end up doing
$dbh->do("insert into Dept ( dept, descr)
values ( '', '')");
Thus you're inserting empty strings, not NULLs. If you were to replace
undef() with 'NULL', you'll end up inserting the string "NULL", not NULL
itself, for the same reason: You do
$dbh->do("insert into Dept ( dept, descr)
values ( 'NULL', 'NULL')");
while you should be doing
$dbh->do("insert into Dept ( dept, descr)
values (NULL, NULL)");
What can you do? I *think* placeholders do the right thing, so
my $sth = $dbh->prepare("insert into Dept ( dept, descr)
values (?, ?)");
$sth->execute($self->{dept}, $self->{descr});
just might work. Or I also think you can do
my %quoted = map { $_ => $dbh->quote($self->{$_})}
qw(dept descr);
$dbh->do("insert into Dept ( dept, descr)
values ($quoted{dept}, $quoted{descr})");
because DBI's quote method does the proper thing for undef.
All untested code though.
--
Bart.
