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(dot)Grabowski(at)7bulls(dot)com
office: +48 56 655 79 65
mobile: +48 604 181 907