Re: PERL DBD::pg question

Peter V Trofimov wrote:

> Strange thing:
>
> I can execute this query in psql without any errors:
>
> delete from news_pending where news_id in (37,15,38,41,43,0);
>
> but in PERL this script:
> _____________________________________________________________
> my $ids = join(',', @news_id);
> $sth = $dbh->prepare_cached("delete from news_pending where news_id in
> (?)");
> $sth->execute($ids);
> $err_str=$DBI::errstr;
> while (defined($err_str)) {
>   $dbh->rollback();
>   $dbh->disconnect();
>   exit;
> }
> $dbh->commit();
> _______________________________________________________________
>
> executs with error:
> ERROR:  pg_atoi: error in "37,15,38,41,43,0": can't parse ",15,38,41,43,0"
>
> How I can fix it?
>

If you put variables by execute method then all vars will be quoted ...
PostgreSQL try to execute this query:
delete from news_pending where news_id in ('12,13,14') not
delete from news_pending where news_id in (12,13,14)

Try to make this query like this...

my $dbh;
eval {
    $dbh = DBI->connect('dbi:Pg:____',  {RaiseError => 1, PrintError => 0,
AutoCommit => 0});
    my $sth = $dbh->prepare('delete from news_pending where news_id = ?');
    foreach my $id (@news_id) {
        $sth->execute($id);
    }
    $sth->commit();
};
if ($@) {
    $dbh->rollback();
}
$dbh->disconnect();

regards...

--
Robert Grabowski   7bulls.com S.A.

email: Robert.Grabowski@7bulls.com
office: +48 56 655 79 65
mobile: +48 604 181 907