Bug #93529 for DBIx-DataModel: Generated sql causing syntax errors with mysql

Tue Mar 04 19:31:30 2014 jswitzer [...] shastaqa.com - Ticket created

Subject:	Generated sql causing syntax errors with mysql
Date:	Tue, 4 Mar 2014 16:30:58 -0800
To:	bug-DBIx-DataModel [...] rt.cpan.org
From:	James Switzer <jswitzer [...] shastaqa.com>

This may actually be a bug in SQL::Abstract::More, but DBIx::DataModel is currently what is exposing it. I've been using DBIx::DataModel with a sqlite database and am currently in the process in moving it over to being backed by mysql. I have run into a problem where a column name "Key" that was fine with sqlite does not work with mysql because it is a reserved word. As far as I can tell there is no way in DBIx::DataModel or SQL::Abstract::More to enable the quoting of table and column identifiers. It would be nice if this was possible. DBIx-DataModel-2.42 perl, v5.10.1 Darwin SQA-MAC-29.local 11.4.2 Darwin Kernel Version 11.4.2: Thu Aug 23 16:25:48 PDT 2012; root:xnu-1699.32.7~1/RELEASE_X86_64 x86_64 OSX 10.7.5 UPDATE Config SET Value = ? WHERE ( Key = ? ) / 1393977913, account_ts at XXX/Account/010_Create.t line 20. 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 'Key = 'account_ts' )' at line 1 at /Users/QATesting/perl5/perlbrew/perls/perl-5.10.1/lib/site_perl/5.10.1/DBIx/DataModel/ConnectedSource.pm line 286.

Wed Mar 05 14:41:49 2014 DAMI [...] cpan.org - Correspondence added

Le Mar 04 Mar 2014 19:31:30, jswitzer@shastaqa.com a écrit : Show quoted text

> This may actually be a bug in SQL::Abstract::More, but DBIx::DataModel > is > currently what is exposing it.

Hi James, SQL::Abstract::More inherits from SQL::Abstract, so there is an option that you can configure. Here is the way to do it (assuming you work in single-schema mode) : # define the schema DBIx::DataModel->Schema('FOO'); # feed the schema with a custom instance of SQL::Abstract::More my $sqlam = SQL::Abstract::More->new(quote_char => "`"); FOO->singleton->sql_abstract($sqlam); # define a table FOO->Table(qw/Config CONFIG KEY/); # produce SQL with quoted table and column names my ($sql, @bind) = FOO::Config->select( -columns => [qw/KEY VALUE/], -where => {KEY => 123}, -result_as => 'sql', ); print $sql; # SELECT `KEY`, `VALUE` FROM `CONFIG` WHERE ( `KEY` = ? )

Wed Mar 05 14:41:49 2014 The RT System itself - Status changed from 'new' to 'open'

Wed Mar 05 14:41:50 2014 DAMI [...] cpan.org - Status changed from 'open' to 'rejected'

Wed Mar 05 14:58:42 2014 DAMI [...] cpan.org - Correspondence added

Le Mer 05 Mar 2014 14:41:49, DAMI a écrit : Show quoted text

> SQL::Abstract::More inherits from SQL::Abstract, so there is an option > that you can configure.

I added a recipe in Cookbook https://github.com/damil/DBIx-DataModel/commit/9b56538bc064d3436aa39c6aedb446feaee0fa48

Wed Mar 05 18:16:34 2014 jswitzer [...] shastaqa.com - Correspondence added

Subject:	Re: [rt.cpan.org #93529] Generated sql causing syntax errors with mysql
Date:	Wed, 5 Mar 2014 15:16:02 -0800
To:	bug-DBIx-DataModel [...] rt.cpan.org
From:	James Switzer <jswitzer [...] shastaqa.com>

Thank you for the help. Jamess On Wed, Mar 5, 2014 at 11:58 AM, Laurent Dami via RT < bug-DBIx-DataModel@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=93529 > > > Le Mer 05 Mar 2014 14:41:49, DAMI a écrit : >

> > SQL::Abstract::More inherits from SQL::Abstract, so there is an option > > that you can configure.

> > I added a recipe in Cookbook > > > https://github.com/damil/DBIx-DataModel/commit/9b56538bc064d3436aa39c6aedb446feaee0fa48 >