| Subject: | Join syntax errors when using MS Access |
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 |
--- 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);
}