Trouble with Perl qq or SQL syntax

software development

#1

I’m having trouble with this SQL statement in a perl script:

15 $SQLstatement = qq {Select UNIX_Timestamp(Date), Chinook From tblRoza2004 order by Date DESC WHERE date > “2004-04-10” };
16

20 my $dbh = DBI->connect(“dbi:mysql:host=mysql.ykfp.org:xxxxxx”, “xxxxxx”, “xxxxx”) or die $DBI::errstr;
21 my $sth = $dbh->prepare($SQLstatement) or die $DBI::errstr;
22
23
24 $sth->execute() or die $DBI::errstr;

At run time I get :

DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE date > “2004-04-10”’ at line 1 at ./dbroza5.pl line 24.
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE date > “2004-04-10”’ at line 1 at ./dbroza5.pl line 24.
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE date > “2004-04-10”’ at line 1 at ./dbroza5.pl line 24.

I get this syntax error only when I try to add a WHERE date condition on the end of the SQL statement with the double quotes around the date. My SQL statement works at the mysql monitor, so it must be that I don’t understand how the double quotes and the qq{} function are working in perl on DH.


#2

Nope, thats not it.

Read up on the syntax of the SELECT statement at http://dev.mysql.com/doc/mysql/en/SELECT.html, particularly the paragraph that begins “All clauses…”

:cool: Perl / MySQL / HTML+CSS


#3

Good hint. I was barking up the wrong tree by looking at the quotes. I wonder why I thought it worked on the mysql monitor. I must have been just testing the WHERE. The whole statement doesn’t work at the monitor.

Now my statement is:

$SQLstatement = qq {Select UNIX_Timestamp(Date), Chinook From tblRoza2004
WHERE date > "2004-04-25"
order by Date };

Thanks.