ODBC SQL syntax issue

Hi there,

I have a problem using MySQL 3.23.51 as database in my Crystal Reports
9.2. I'm using MyODBC 2.50.39 as there is no native support for MySQL in
Crystal Reports. It works perfect except for one thing - the outer
joins. When the whole JOIN statement is not situated right after the
FROM, MyODBC returns error in the SQL syntax (near the opening bracket).
Here is an example:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM `inchier` `inchier`, (`incompanies` `incompanies` LEFT OUTER
JOIN `incontacts` `incontacts` ON
`incontacts`.`CompanyID`=`incompanies`.`CompanyID`), `indepartment`
`indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

But when I place "`inchier` `inchier`" after the JOIN statement
everything works just fine:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM (`incompanies` `incompanies` LEFT OUTER JOIN `incontacts`
`incontacts` ON `incontacts`.`CompanyID`=`incompanies`.`CompanyID`),
`inchier` `inchier`, `indepartment` `indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

I thing this issue is somehow connected to the brackets, because without
brackets the join works well no matter where it is situated. Here is the
first query without the brackets and working perfectly as well:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM `inchier` `inchier`, `incompanies` `incompanies` LEFT OUTER JOIN
`incontacts` `incontacts` ON
`incontacts`.`CompanyID`=`incompanies`.`CompanyID`, `indepartment`
`indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

Since there is no problem with other databases accessed through ODBC
with this syntax I consider this as a bug in the ODBC SQL syntax parsing
(especially the brackets). There is no way or sence to correct the SQL
query in Crystal Reports so I thing the best solution is to fix this
problem in MySQL. Automatic removal of these specific brackets should
solve the problem ad hoc. You can include a new flag in MyODBC for this
bracket removal or something else, but you know better.

I'll be thankful if you inform me on the future development on this
issue.


Ivan Vazharov
System Integrator
Alen Mak


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail myodbc-thread6172@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail myodbc-unsubscribe@lists.mysql.com instead.