Subject: | (MySQL) table names not correctly quoted in queries |
I'm using Class::DBI in a parser that fills the output of a config dump
script into a MySQL database. We've several link tables here, which are
named in the manner of "TABLE1-TABLE2", with "TABLE1" and "TABLE2"
beeing the respective names of the tables where the foreign keys come from.
When accessing these tables with Class::DBI, the query fails. Error
message is:
Model::Cluster_Serv can't SELECT cluster_id
FROM CLUSTER-SERV
: 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 '-SERV' at line 2 [for Statement "SELECT cluster_id
FROM CLUSTER-SERV
"] at /usr/lib/perl5/vendor_perl/5.10.0/DBIx/ContextualFetch.pm line 52.
at test_dbaccess.pl line 79
I can supply the perl code that causes the error message, but I guess
it's pretty much clear what goes wrong.
When I use something like "Model::Fobar->table('`CLUSTER-SERV`')"
instead of the version without backticks, it works, which makes me think
there's a bug within Class::DBI. While investigating, I found that the
table names do not get quoted in &Class::DBI::Query::table. I am,
however, hesitant to replace the simple join with something that adds
backticks around table names because I'm not sure whether the
`TABLENAME` syntax is MySQL-specific or not.
Thanks for any reply. :-)