Postgres Pro
Downloads
Home   >   mailing lists

Thread: DBI placeholders

DBI placeholders

From
Evan Zane Macosko
Date:
Hi everyone,


I'm wondering if someone could explain to me how placeholders work with
Perl DBI linking to Postgresql.  I seem to have trouble using them in a
loop.  For example, the code:

my $i = 0;
$sth = $dbh->prepare("UPDATE yeast1 set ? = '?' where yeast1.orf =
temp.orf");
while ($i<$#columns) {
    $sth->execute($i, $tables[$i-1];
    $i++;
}

This returns an error from the Pg parser.  Any suggestions?  Also, this
query is incredibly slow--does anyone have suggestions for optimization?

Thanks!
Evan

Re: DBI placeholders

From
Adrian Phillips
Date:
>>>>> "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.

Sincerely,

Adrian Phillips

--
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]

Re: DBI placeholders

From
will trillich
Date:
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!