Oddly named Mysql table and backticks

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

I'm doing some work on a database that has a table named 'customer-trans'. When working with the Mysql shell I have to surround this name with backticks. Trying that within Perl has not worked:

$statement = 'SELECT trnemailaddress, activedate FROM
     `customer-trans` WHERE (UNIX_TIMESTAMP(activedate)
      + ? ) < (UNIX_TIMESTAMP(NOW());';

$sth = $dbh->prepare($statement) or die "Couldn't prepare statement:".
+$dbh->errstr;
$sth->execute($grace) or die "Couldn't prepare statement:".$dbh->errst
+r;

# get and print results
while ($ref = $sth->fetchrow_hashref()){

    print $ref->{'trnemailaddress'} . "\t\t\t". $ref->{'activedate'};

}
[download]

Returns:

DBD::mysql::st execute failed: You have an error in your SQL syntax.  
+Check the manual that corresponds to your MySQL server version for th
+e
 right syntax to use near '' at line 1 at ./chpasswd line 35.
Couldn't prepare statement:You have an error in your SQL syntax.  Chec
+k the manual that corresponds to your MySQL server version for the ri
+g
ht syntax to use near '' at line 1 at ./chpasswd line 35.
[download]

If I leave the backticks off I get an error that starts at -trans. Any suggestions?

Neil Watson
watson-wilson.ca

Comment on Oddly named Mysql table and backticks Select or Download Code

Replies are listed 'Best First'.
Re: Oddly named Mysql table and backticks by ercparker (Hermit) on Jun 18, 2005 at 14:44 UTC
looks like the SELECT statement you posted is missing a ')' should be: SELECT trnemailaddress, activedate FROM `customer-trans` WHERE (UNIX_T +IMESTAMP(activedate) + ? ) < (UNIX_TIMESTAMP(NOW())); [download]	[reply] [d/l]
Re^2: Oddly named Mysql table and backticks by neilwatson (Priest) on Jun 18, 2005 at 15:00 UTC
It was the extra backet. Thanks for setting me straight. Neil Watson watson-wilson.ca	[reply]
Re^3: Oddly named Mysql table and backticks by ercparker (Hermit) on Jun 18, 2005 at 15:04 UTC
no problem, an extra set of eyes always helps -Eric	[reply]
Re: Oddly named Mysql table and backticks by Fletch (Bishop) on Jun 18, 2005 at 15:28 UTC
Turning on DBI's tracing (call `$dbh->trace( 2 )`) can be handy as it'll show you more of the conversation going on back and forth at the database driver layer. See `perldoc DBI` for more details (like saving the trace output to a file rather than STDERR). -- We're looking for people in ATL	[reply] [d/l] [select]
Re: Oddly named Mysql table and backticks by jZed (Prior) on Jun 18, 2005 at 14:18 UTC
I notice that the error statement is in the execute, not in the prepare even though you check both places. My guess is that the problem may have to do with your placeholder value - what's in $grace? Does the statement work correctly without a placeholder? update Just noticed that you include a semicolon at the end of the SQL statement, generally that's not correct for DBI statements.	[reply]