Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 62787
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: bob [...] rdolson.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 0.08124
Fixed in: 0.08191



Subject: Join syntax errors when using MS Access
Download (untitled) / with headers
text/plain 2.1k
When using joins in DBIx::Class against a Microsoft Access (Jet) database, the queries fail because the Access SQL engine requires parenthesis that other database engines do not. For example: my @res = $self->schema->resultset('Dog')->search( { 'EventDateID.EventID' => 56 }, { join => { DogEntry => { 'TitleEventDateID' => 'EventDateID' } }, '+select' => [ 'EventDateID.EventDate' ] , '+as' => ['date'], } ); fails with DBIx::Class::ResultSet::search(): DBI Exception: DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DogEntry.DogID = me.DogID LEFT JOIN tblTitleEvent TitleEventDateID ON TitleEventDateID.TitleEventDateID = DogEntry.TitleEventDateID LEFT JOIN tblEventDate EventDateID ON EventDateID.EventDateID = TitleEventDateID.EventDateID'. (SQL-42000) [for Statement "SELECT me.DogID, me.OwnerID, me.HandlerID, me.BreedID, me.HeightatWithers, me.RegisteredName, me.CallName, me.Breeder, me.Sex, me.DateofBirth, me.Sire, me.Dam, me.RecordAddDateTime, me.DEOID, EventDateID.EventDate FROM tblDog me LEFT JOIN trelDogEntry DogEntry ON DogEntry.DogID = me.DogID LEFT JOIN tblTitleEvent TitleEventDateID ON TitleEventDateID.TitleEventDateID = DogEntry.TitleEventDateID LEFT JOIN tblEventDate EventDateID ON EventDateID.EventDateID = TitleEventDateID.EventDateID WHERE ( EventDateID.EventID = ? )" with ParamValues: 1='5.6.'] at C:\strawberry\perl\vendor\lib/DBI/ProxyServer.pm line 341. The attached patch to DBIx::Class::SQLMaker adds the appropriate parentheses. I unfortunately don't have a database handy to check this patch to see if it affects other database engines. With the patch installed the query above executes properly.
Subject: SQLMaker.patch
Download SQLMaker.patch
text/x-diff 509b
--- SQLMaker.pm.orig 2010-11-06 19:36:43.000000000 -0500 +++ SQLMaker.pm 2010-11-06 19:38:03.000000000 -0500 @@ -324,6 +324,7 @@ sub _recurse_from { my ($self, $from, @join) = @_; my @sqlf; + push(@sqlf, '(' x scalar(@join)); push(@sqlf, $self->_make_as($from)); foreach my $j (@join) { my ($to, $on) = @$j; @@ -347,6 +348,7 @@ push(@sqlf, $self->_make_as($to)); } push(@sqlf, ' ON ', $self->_join_condition($on)); + push(@sqlf, ')'); } return join('', @sqlf); }
Download (untitled) / with headers
text/plain 655b
Including this code as is would be wasted effort, as there is a good chance this change will get thrown away the next time someone decides to rewrite the join generation code. Please supply a test in addition to the actual fix. For prototypes look at e.g. t/sqlmaker/oracle.t. Note that these tests run without the presence of the relevant database, which increases the chances of it not being broken by a drive-by refactor. Since you are dealing with parenthesis you will also need to activate http://search.cpan.org/~frew/SQL-Abstract-1.69/lib/SQL/Abstract/Test.pm#$parenthesis_significant, so that is_same_sql_bind will not optimize away parenthesis.
Subject: Re: [rt.cpan.org #62787] Join syntax errors when using MS Access
Date: Sun, 7 Nov 2010 18:26:28 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Robert Olson <bob [...] rdolson.org>
Download (untitled) / with headers
text/plain 912b
Are you saying it is wasted effort because I did not submit a test, or for some other reason? On Nov 7, 2010, at 3:46 PM, Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=62787 > > > Including this code as is would be wasted effort, as there is a good > chance this change will get thrown away the next time someone > decides to > rewrite the join generation code. > > Please supply a test in addition to the actual fix. For prototypes > look > at e.g. t/sqlmaker/oracle.t. Note that these tests run without the > presence of the relevant database, which increases the chances of it > not > being broken by a drive-by refactor. > > Since you are dealing with parenthesis you will also need to activate > http://search.cpan.org/~frew/SQL-Abstract-1.69/lib/SQL/Abstract/Test.pm#$parenthesis_significant > , > so that is_same_sql_bind will not optimize away parenthesis.
Download (untitled) / with headers
text/plain 782b
On Sun Nov 07 19:26:36 2010, bob@rdolson.org wrote: Show quoted text
> Are you saying it is wasted effort because I did not submit a test, or > for some other reason?
I am saying that me applying this without a test is a waste. It *will* get broken, as there are at least 2 total rewrites (from different angles) due on the join-resolution code in the next 6mo or so. There is no way your change will survive (as it is has no immediately obvious benefit) unless there is a regression test to catch a behavior change. Since I am not familiar with the intricacies of the Access SQL dialect, I am asking you to write a test to go along with your change. Moreover I pointed out plenty of resources to get you started. Nevertheless please ask for clarification if the existing tests seem overwhelming.
Subject: Re: [rt.cpan.org #62787] Join syntax errors when using MS Access
Date: Sun, 7 Nov 2010 18:59:37 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Robert Olson <bob [...] rdolson.org>
Download (untitled) / with headers
text/plain 1.2k
I'm happy to write a test for it. However, after a few minutes of poking at the oraclejoin test it's not clear to me what the correct syntax is in the SqlMaker::select() routine to trigger the nested join case; I didn't see docs in the obvious places for the exact syntax of that method. On Nov 7, 2010, at 6:41 PM, Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=62787 > > > On Sun Nov 07 19:26:36 2010, bob@rdolson.org wrote:
>> Are you saying it is wasted effort because I did not submit a test, >> or >> for some other reason?
> > I am saying that me applying this without a test is a waste. It *will* > get broken, as there are at least 2 total rewrites (from different > angles) due on the join-resolution code in the next 6mo or so. There > is > no way your change will survive (as it is has no immediately obvious > benefit) unless there is a regression test to catch a behavior change. > > Since I am not familiar with the intricacies of the Access SQL > dialect, > I am asking you to write a test to go along with your change. > Moreover I > pointed out plenty of resources to get you started. > > Nevertheless please ask for clarification if the existing tests seem > overwhelming.
Download (untitled) / with headers
text/plain 630b
On Sun Nov 07 19:59:47 2010, bob@rdolson.org wrote: Show quoted text
> I'm happy to write a test for it. However, after a few minutes of > poking at the oraclejoin test it's not clear to me what the correct > syntax is in the SqlMaker::select() routine to trigger the nested join > case; I didn't see docs in the obvious places for the exact syntax of > that method. >
I guess I pointed you in the wrong direction - you can use DBIC natively, instead look here: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/sqlmaker/sqlite.t;h=86fcc82d9c1aa35530b43ce4dbbda6474119da35;hb=HEAD Sorry for the confusion
Subject: Re: [rt.cpan.org #62787] Join syntax errors when using MS Access
Date: Sun, 7 Nov 2010 19:43:58 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Robert Olson <bob [...] rdolson.org>
Download (untitled) / with headers
text/plain 633b
Show quoted text
> I guess I pointed you in the wrong direction - you can use DBIC > natively, instead look here: > http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/sqlmaker/sqlite.t;h=86fcc82d9c1aa35530b43ce4dbbda6474119da35;hb=HEAD > > Sorry for the confusion
Aha, cool. I'm hip deep in application code at the moment but I will look into putting together some tests for this change. DBIC is such a marked improvement over writing visual basic code :-) (I write a ton of perl code at my day job, but for some side jobs I use commercial software based on MS Access and am writing extensions for it). --bob
Download (untitled) / with headers
text/plain 749b
On Sun Nov 07 20:44:08 2010, bob@rdolson.org wrote: Show quoted text
> > I guess I pointed you in the wrong direction - you can use DBIC > > natively, instead look here: > > http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-
>
Class.git;a=blob;f=t/sqlmaker/sqlite.t;h=86fcc82d9c1aa35530b43ce4dbbda6474119da35;hb=HEAD Show quoted text
> > > > Sorry for the confusion
> > Aha, cool. I'm hip deep in application code at the moment but I will > look into putting together some tests for this change. DBIC is such a > marked improvement over writing visual basic code :-) (I write a ton > of perl code at my day job, but for some side jobs I use commercial > software based on MS Access and am writing extensions for it).
Stalling ticket, waiting for a new patch from author.
Download (untitled) / with headers
text/plain 709b
Hi, what version of Access are you using? I am using Access 2010. I have the following join code in the tests I'm currently working on which executes just fine: my $joined_artist = $schema->resultset('Artist')->search({ artistid => $first_artistid, }, { join => [ 'cds' ], '+select' => [ 'cds.title' ], '+as' => [ 'cd_title' ], })->next; is $joined_artist->get_column('cd_title'), 'Some Album', 'join works'; SELECT me.artistid, me.name, me.rank, me.charfield, cds.title FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid WHERE ( artistid = ? ): '1' ok 48 - join works Do you see this problem with only multiple joins or is a single join enough to reproduce it?
Subject: Re: [rt.cpan.org #62787] Join syntax errors when using MS Access
Date: Fri, 4 Feb 2011 21:09:27 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Robert Olson <bob [...] rdolson.org>
It was probably in access 2003. I'll have to dig up the code and give it a shot in the newer access. (I was just thinking about that project; it got sidelined due to other stuff going on). --bob On Feb 4, 2011, at 9:05 PM, Rafael Kitover via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=62787 > > > Hi, > > what version of Access are you using? I am using Access 2010. > > I have the following join code in the tests I'm currently working on > which executes just fine: > > my $joined_artist = $schema->resultset('Artist')->search({ > artistid => $first_artistid, > }, { > join => [ 'cds' ], > '+select' => [ 'cds.title' ], > '+as' => [ 'cd_title' ], > })->next; > > is $joined_artist->get_column('cd_title'), 'Some Album', 'join works'; > > SELECT me.artistid, me.name, me.rank, me.charfield, cds.title FROM > artist me LEFT JOIN cd cds ON cds.artist = me.artistid WHERE ( artistid > = ? ): '1' > ok 48 - join works > > Do you see this problem with only multiple joins or is a single join > enough to reproduce it?
Download (untitled) / with headers
text/plain 554b
On Fri Feb 04 22:09:36 2011, bob@rdolson.org wrote: Show quoted text
> It was probably in access 2003. > > I'll have to dig up the code and give it a shot in the newer access. > (I was just thinking about that project; it got sidelined due to other > stuff going on). > > --bob
In the process of testing my Schema::Loader driver, I found out that you do need the parenthesis for a two-step join, so reproduced on Access 2010. I'm adding some code similar to your patch. What is your name, email address and IRC handle you want to use for the CONTRIBUTORS section?
Subject: Re: [rt.cpan.org #62787] Join syntax errors when using MS Access
Date: Sat, 19 Feb 2011 07:03:41 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Robert Olson <bob [...] rdolson.org>
Download (untitled) / with headers
text/plain 879b
On Feb 19, 2011, at 4:09 AM, Rafael Kitover via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=62787 > > > On Fri Feb 04 22:09:36 2011, bob@rdolson.org wrote:
>> It was probably in access 2003. >> >> I'll have to dig up the code and give it a shot in the newer access. >> (I was just thinking about that project; it got sidelined due to other >> stuff going on). >> >> --bob
> > In the process of testing my Schema::Loader driver, I found out that > you do need the parenthesis for a two-step join, so reproduced on > Access 2010. > > I'm adding some code similar to your patch. > > What is your name, email address and IRC handle you want to use for the > CONTRIBUTORS section?
I'm sorry I hadn't gotten a chance to get back and check this - that is great that you are incorporating a fix. Thank you. Robert Olson bob@rdolson.org No IRC handle. --bob
Download (untitled) / with headers
text/plain 293b
Please test the hopefully complete MSAccess support that just merged to mainline: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commit;h=726c8f65ef37b47aad62e29a306f64528a00f65d Marking ticket as patched, but feel free to reopen it if more problems are identified.
Download (untitled) / with headers
text/plain 158b
Please try the newly-release DBIC 0.08191. It contains a massive amount of MSAccess support code, your particular issue should be among the resolved problems.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.