Issue in prepare() and execute() in DBI

abubacker has asked for the wisdom of the Perl Monks concerning the following question:

Dear All,
I was trying to execute the following postgres query using DBI module

select * from timestamp_testing where start = timestamp '2011-06-02 12
+:33:07' ;
[download]

I ran the code using perl DBI module

 my $sth = $dbh->prepare ("SELECT * from timestamp_testing where start
+ = (timestamp ?) ");

 if ( $sth->execute("2011-06-02 12:33:07" )){
         print Dumper $sth->fetchall_arrayref;
 }
else {
            print "WRONG QUERY\n" ;
}
[download]

but this says an error

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: ...ELECT * from timestamp_testing where start = (timestamp $1)
+ 
                                                                   ^ a
+t NORMAL_DBI.pl line 63
[download]

please help me out !

Comment on Issue in prepare() and execute() in DBI Select or Download Code

Replies are listed 'Best First'.
Re: Issue in prepare() and execute() in DBI by salva (Canon) on Jun 02, 2011 at 10:01 UTC
Try using the following syntax: `my $sth = $dbh->prepare ("SELECT * from timestamp_testing where start += ?::timestamp");` [download] BTW, the PostgreSQL client library libpq does not use `?` but `$1`, `$2`, `$3`, etc. as placeholders (the DBD::Pg driver makes the conversion internally), that's why you see a `$1` on the error string.	[reply] [d/l] [select]
Re: Issue in prepare() and execute() in DBI by mje (Curate) on Jun 02, 2011 at 09:42 UTC
A presume timestamp is a function in Postgres. I think it is indicating it does not like your parameter or the function call. Is that date in a valid format? Do you need brackets around "?" after timestamp?	[reply]