Re: DBI placeholders

On Tue, Aug 21, 2001 at 04:55:20PM +0200, Adrian Phillips wrote:
> >>>>> "Evan" == Evan Zane Macosko <macosko@fas.harvard.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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!