Perl::DBI and interval syntax

[Allen <dba@xxxxxxxxxxx>]

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;


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings