On Wed, 12 Sep 2001, Bart Lateur wrote:
> 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
> 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
Thanks for pointing this out, my explorations and tests have not
found that problem yet. Lack of understanding and low priority;
in my data the empty string and the undef are not significantly
different. I do want to do the right thing though; for who knows
what may come up later.
> 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
I will try that.
Has anyone tried caching the prepared statements?
Something like:
package Dept;
sub store{
unless ( $cache::dept_store ) {
$cache::dept_store =
$dbh->prepare( "insert into Dept (dept,descr) values (?,?)");
}
$cache::dept_store->execute( $self->{dept}, $self->{descr});
}
Is this a good idea? I could easily have 150 prep'd statement handles
lying around.
> 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.
That is good to know.
Publishing a preview routine seems like a good idea for DBI education.
Viz:
$sth=$dbh->prepare( "insert into Dept (dept,descr) values (?,?)");
$str= $sth->string( $self->{dept}, $self->{descr});
$str would now contain the text of the query and perhaps a key to what is
being returned, hash, array, aref etc. Like:
(void)insert into Dept ( dept, descr) values ( 'hi', NULL)
Thanks.
rob Live the dream.
