Perl::DBI and interval syntax - Mailing list pgsql-general

Hi again!

I have a perl program running a query on FreeBSD under PostgreSQL 7.4.8
that has worked for some time. Converting the program to Linux under
PostgreSQL 8.0.3 returns a database error. The interval clause is the
issue.

SELECT count(*) from post where post_ts >= current_date - interval ?
execute argument: '21 days'

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1" at
character 74

The value '21 days' is achieved computationally so shouldn't be
hard-coded in the query.

How should this be specified?

Allen

Sample code follows:

#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;

my $dbName='allen';
my $host='localhost';
my $dbUser=$dbName;
my $dbPassword='';
my $csrnum=0;
my $sql="SELECT count(*) from post where post_ts >= current_date -
interval ?";
my @parms=('21 days');

my $dbh = DBI->connect("DBI:Pg:dbname=$dbName;host=$host", i
         $dbUser, $dbPassword,
         { RaiseError => 0, AutoCommit => 0, PrintError => 1 })
         or die "Can't connect to db\n";

my $sth = $dbh->prepare($sql);
$sth->execute(@parms) or die "execute err: $DBI::errstr";
while (my $hr = $sth->fetchrow_hashref) {
   print Dumper($hr);
}
$sth->finish();

$dbh->commit();
$dbh->disconnect();
exit;