Perl::DBI and interval syntax

From: Allen <dba(at)girders(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Perl::DBI and interval syntax
Date: 2005-11-07 21:56:00
Message-ID: 436FCD70.5030700@girders.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Cristian Prieto 2005-11-07 22:51:51 Help with Array Function in C language...
Previous Message shenanigans 2005-11-07 21:11:08 [OTAnn] Feedback