Re: DBI placeholders

On Tue, Aug 21, 2001 at 04:55:20PM +0200, Adrian Phillips wrote:
> >>>>> "Evan" == Evan Zane Macosko <macosko(at)fas(dot)harvard(dot)edu> writes:
>
> Evan> Hi everyone, I'm wondering if someone could explain to me
> Evan> how placeholders work with Perl DBI linking to Postgresql.
> Evan> I seem to have trouble using them in a loop. For example,
> Evan> the code:
>
> Evan> my $i = 0; $sth = $dbh->prepare("UPDATE yeast1 set ? = '?'
> Evan> where yeast1.orf = temp.orf"); while ($i<$#columns) {
> Evan> $sth->execute($i, $tables[$i-1]; $i++; }
>
> Evan> This returns an error from the Pg parser. Any suggestions?
> Evan> Also, this query is incredibly slow--does anyone have
> Evan> suggestions for optimization?
>
> Placeholders are for values not for column names, and you shouldn't
> have quotes around the ? either.

so you could do something like this, instead:

sub badexample {
my $table = shift; # table name
my $where = shift; # search-by field name
my $value = shift; # search-by value
my $fields = join ',',(@_ || '*');

my $sql = "SELECT $fields FROM $table WHERE $where LIKE ?";
my $sth = $DBH->prepare( $sql )
|| die DBI->errstr . ": $sql";
$sth->execute($value);

return $sth;
}

postgres quotes the values you're plugging in to the ?
automatically, i think...

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!