On March 31, 2003 10:41 am, Ronald J Kimball wrote:
> On Mon, Mar 31, 2003 at 11:42:37AM -0600, Michael Muratet wrote:
> > I have discovered that it is the placement of single quotes around the
> >
> > key value in the query:
> > > my $rth = $dbh->prepare("SELECT * FROM demographics_1 WHERE
> > > KEY1=$key");
> >
> > that makes the difference. The above takes 90 secs.
> >
> > The below takes milliseconds (as it does from the command line):
> > > my $rth = $dbh->prepare("SELECT * FROM demographics_1 WHERE
> > > KEY1='$key'");
> >
> > Without the single quotes, the command line interface will tell you that
> > you have a bogus column name. I guess the DBI interface is able to
> > figure it out, but it takes awhile.
>
> What is the value of $key?
>
> Anyway, you should probably be using placeholders.
Excuse my ignorance, but I'm trying to figure out why people would not be
using placeholders (excepting legacy code, perhaps)? It seems to solve
nearly every other problem I've seen while lurking on this list and have the
fewest gotchas.
sub get_keys
{
my $dbh = shift;
my @keys = @_;
my $sth = $dbh->prepare('SELECT * FROM demographics_1 WHERE KEY1 IN (' .
join(",", map { '?' } @keys) . ')');
$sth->execute(@keys);
$sth->fetchall_arrayref();
}
It doesn't seem that difficult to me - no more so than handling an arbitrary
number of possible matches without placeholders.
sub get_keys_no_placeholders
{
my $dbh = shift;
my @keys = @_;
$dbh->selectall_arrayref('SELECT * FROM demographics_1 WHERE KEY1 IN (' .
join(",", map { $dbh->quote($_) } @keys }) . ')');
}
The single possibility case seems even better for placeholders since there's
no quoting.
Perhaps someone can enlighten me?
Thanks,
