jwh
Posts: 13
|
| Posted: 06/02/2006, 8:27 AM |
|
MySQL changed the syntax of multi-table-joins in their Version 5.0.12.
CCS generates SQL-Statements with lots of () when using the graphic table-join-tool. But running against MySQL 5.0.12+ I keep getting "unknown column xyz in ON clause". I can fix it by always completly rewriting the SQL-Statements.
The Changes in MySQL 5.0.12+ where mentioned as more SQL:2003 alike (see http://dev.mysql.com/doc/refman/5.0/en/join.html)
So shouldn't there be a switch in the database connection parameters weather to use SQL:2003 syntax or not???
I hope someone understands my problem,
thanks for any useful feedback in advance
|
 |
 |
WKempees
|
| Posted: 06/02/2006, 12:01 PM |
|
Although not a direct solution I have 2 questions for you:
1: In Project Settings-> Connections -> Server
Do you use MySQL or improved MySQL in the PhP Database Library ?
2: Apparently you know what is wrong with your joined VQB build Select
Can you post the resulting SELECT here please, I would love to see it and
put my teeth in it.
Walter
"jwh" <jwh@forum.codecharge> schreef in bericht
news:2448058f164da9@news.codecharge.com...
> MySQL changed the syntax of multi-table-joins in their Version 5.0.12.
> CCS generates SQL-Statements with lots of () when using the graphic
> table-join-tool. But running against MySQL 5.0.12+ I keep getting "unknown
> column xyz in ON clause". I can fix it by always completly rewriting the
> SQL-Statements.
> The Changes in MySQL 5.0.12+ where mentioned as more SQL:2003 alike (see
> http://dev.mysql.com/doc/refman/5.0/en/join.html)
>
> So shouldn't there be a switch in the database connection parameters
> weather to
> use SQL:2003 syntax or not???
>
> I hope someone understands my problem,
>
> thanks for any useful feedback in advance
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
jwh
Posts: 13
|
| Posted: 06/02/2006, 12:48 PM |
|
Hello Walter
thanks for your quick responce.
Until today I did not notice the two different SQL-Modes. After your hint I checked my config and it was not the improved MySQL. That gave me hope an I changed it to improved MySQL and checked my app again.
Unfortunatly there is no success.
Here is the SQL-Statement that gets generated:
-----------------------------------------------------------------------------------------------------------
SELECT Location.Name AS Location_Name, URMDept.Name AS URMDept_Name, LoginName, Lastname, Firstname, URMGroup.Name AS URMGroup_Name,
URMRight.Name AS URMRight_Name, URMGroupRight.FKidLocation AS URMGroupRight_FKidLocation
FROM (((((URMGroup INNER JOIN URMUser ON
URMUserGroup.FKidURMGroup = URMGroup.idURMGroup) INNER JOIN URMGroupRight ON
URMGroup.idURMGroup = URMGroupRight.FKidURMGroup) INNER JOIN URMDept ON
URMDept.idURMDept = URMUser.FKidURMDept) INNER JOIN Location ON
Location.idLocation = URMUser.FKidLocation) INNER JOIN URMUserGroup ON
URMUser.idURMUser = URMUserGroup.FKidURMUser) INNER JOIN URMRight ON
URMGroupRight.FKidURMRight = URMRight.idURMRight
------------------------------------------------------------------------------------------------------
the error message is:
'Unknown column URMUserGroup.FKidURMGroup' in 'on clause'
I've taken two screen shots, one from the visual querry builder and one from the mysql error message in reponce of trying to get some data out of it.
If you like to have them, just drop me a note to "jwh@csmed.de"
cu jwh
|
|
|
|
DonB
|
| Posted: 06/02/2006, 5:53 PM |
|
The issue is with improper JOINs in your queries. If you look at the "ON"
and it's preceding "JOIN", the tables are not matching the joined columns.
MySQL5 is strictly adhering to the SQL rules, where it used to be fairly
lenient. If you go into Query Builder and remove, then re-add, the joining
lines those bad joins should repair themselves.
I ran into this myself and fixed it this way. What I'm not clear on is how
the joins get messed up to start with. But that's definitely what happens.
--
DonB
http://www.gotodon.com/ccbth
"jwh" <jwh@forum.codecharge> wrote in message
news:2448095f0ed966@news.codecharge.com...
> Hello Walter
>
> thanks for your quick responce.
>
> Until today I did not notice the two different SQL-Modes. After your hint
I
> checked my config and it was not the improved MySQL. That gave me hope an
I
> changed it to improved MySQL and checked my app again.
> Unfortunatly there is no success.
>
> Here is the SQL-Statement that gets generated:
> --------------------------------------------------------------------------
---------------------------------
> SELECT Location.Name AS Location_Name, URMDept.Name AS URMDept_Name,
LoginName,
> Lastname, Firstname, URMGroup.Name AS URMGroup_Name,
> URMRight.Name AS URMRight_Name, URMGroupRight.FKidLocation AS
> URMGroupRight_FKidLocation
> FROM (((((URMGroup INNER JOIN URMUser ON
> URMUserGroup.FKidURMGroup = URMGroup.idURMGroup) INNER JOIN URMGroupRight
ON
> URMGroup.idURMGroup = URMGroupRight.FKidURMGroup) INNER JOIN URMDept ON
> URMDept.idURMDept = URMUser.FKidURMDept) INNER JOIN Location ON
> Location.idLocation = URMUser.FKidLocation) INNER JOIN URMUserGroup ON
> URMUser.idURMUser = URMUserGroup.FKidURMUser) INNER JOIN URMRight ON
> URMGroupRight.FKidURMRight = URMRight.idURMRight
> --------------------------------------------------------------------------
----------------------------
>
> the error message is:
>
> 'Unknown column URMUserGroup.FKidURMGroup' in 'on clause'
>
>
> I've taken two screen shots, one from the visual querry builder and one
from
> the mysql error message in reponce of trying to get some data out of it.
>
> If you like to have them, just drop me a note to "jwh@csmed.de"
>
> cu jwh
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
jwh
Posts: 13
|
| Posted: 06/04/2006, 5:53 AM |
|
Hello together,
I have tried to switch on "all columns" (sugested by Walter in a separate EMail) without any influence to the sequence of the joins and the columns in the ON-clauses.
I have also tried to remove and reinstall the links (as sugested by DonB) and/or tables, but again without any success.
I have also tried to change the order of the tables on the left side of the VQB under FROM with an right click.
That all does not help  .
No, it's not only ODBC-related (as guessed bay Walter), if I take the generated SQL-Code and run it against the MySQL-DB itself (trough phpMyAdmin) the same error message pops up.
The problem, as I understand it, is that with the new join behaviour you can only refer to columns within the ON-clause that belong to already named/joined tables.
Within my case that is not given,
SELECT *
FROM ((((((URMGroup INNER JOIN URMUser ON
URMUserGroup.FKidURMGroup = URMGroup.idURMGroup) ...
so here URMUserGroup.FKidURMGroup is referred in a JOIN of URMGroup and URMUser.
BTW: I have still shortcommings (after reading the docs) in understanding the JOIN syntax at all, what are all the () good for. I can write the whole statement without any () and it works. But I have to go and RTFM again in the hope to understand it one day 
cu jwh
|
|
|
|
zero
|
| Posted: 06/04/2006, 11:21 PM |
|
I think it will be more useful to contact with support.
|
|
|
|
matheus
Posts: 386
|
| Posted: 07/25/2006, 1:58 PM |
|
Someone have a solution for this?
_________________
Matheus Trevizan
Dynamix Software Ltda.
Blumenau SC Brasil
www.dynamix.com.br |
|
|
|
|
