Subject: | sqlt's generated dumper script should quote column names it is selecting from |
Date: | Wed, 9 May 2007 17:31:16 -0400 |
To: | bug-SQL-Translator [...] rt.cpan.org |
From: | Sudish Joseph <sudish [...] vitrue.com> |
% perl -e 'use SQL::Translator; print SQL::Translator->version, "\n"'
0.08
perl version 5.8.6, running under Mac OS X 10.4.9
First: this is an awesomely useful tool. Thanks for writing and
maintaining it!
I generated a data dumper using:
sqlt -f MySQL --producer-db-user someone --producer-db-pass omitted --
producer-dsn dbi:mysql:sftest -t Dumper <in.sql >dump.pl
The script is then run as:
perl -w dump.pl
One of the tables in the schema has a member named 'from'. Since
dump.pl issues SELECTs with unquoted column names, mysql generates
errors like the following:
---
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 'from, to, cc, bcc, subject, date,
content_type, content_encode, mime_version, x_' at line 1 at dump.pl
line 828.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
dump.pl line 830.
---
The SELECT that was issued is:
---
select id, message_id, from, to, cc, bcc, subject, date,
content_type, content_encode, mime_version, x_mailer, x_sender,
x_priority, reply_to, return_path, mail_followup_to,
disposition_notification_to, received, create, read from inbox_messages
---
The problem here is the unquoted 'from' in the select above. The
following statement would work:
---
select 'id', 'message_id', 'from', 'to', 'cc', 'bcc', 'subject',
'date', 'content_type', 'content_encode', 'mime_version', 'x_mailer',
'x_sender', 'x_priority', 'reply_to', 'return_path',
'mail_followup_to', 'disposition_notification_to', 'received',
'create', 'read' from inbox_messages
---
I fixed this by transforming the code that generates the SELECT from:
---
my $sql =
'select ' . join(', ', @{ $table->{'fields'} } ) . " from
$table_name"
---
to:
---
my $sql =
'select ' . join(', ', map { "\'$_\'" } @{ $table->
{'fields'} } ) . " from $table_name"
---
The actual template that's used by -t Dumper to generate the code
should be changed to match.
Thanks,
-Sudish