Weird Trouble with CF + MySQL.
I'm having this problem. The query works fine directly on the database, but when used on coldfusion it gives this error. If I copy the string from the sql field here, and executes on mysql , it works fine again. Don't get what can be wrong. Maybe CF is doing something stupid when talking to the datasource? Error Executing Database Query. 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 'select b.id_article, group_concat( c.name order by c.use The error occurred in D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 Called from D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 3 : cffunction name=ListArticles access=remote returntype=query 4 : cfargument name=dsn type=string required=yes 5 : cfquery name=qListArticles datasource=#dsn# 6 : select a.id_article, a.title, u.users, t.tags 7 : from articles as a SQLselect a.id_article, a.title, u.users, t.tags from articles as a left outer join ( select b.id_article, group_concat( c.name order by c.userseparator ', ') as users from articlexuser as b inner join users as c on c.id_user = b.id_user group by b.id_article ) as u on u.id_article = a.id_article left outer join ( select d.id_article, group_concat( e.tagorder by e.tag separator ', ') as tags from articlextags as d inner join tags as e on e.id_tag = d.id_tag group by d.id_article ) as t on t.id_article = a.id_article ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272968 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Weird Trouble with CF + MySQL.
Would this line be your culprit? Maybe it has gotten combined due to the paste operation, but shouldn't it be (e.tag order by e.tag separator ', ') ( e.tagorder by e.tag separator ', ') -Original Message- From: Adrien Akkhazza R. Cardoso [mailto:[EMAIL PROTECTED] Sent: Monday, March 19, 2007 4:07 AM To: CF-Talk Subject: Weird Trouble with CF + MySQL. I'm having this problem. The query works fine directly on the database, but when used on coldfusion it gives this error. If I copy the string from the sql field here, and executes on mysql , it works fine again. Don't get what can be wrong. Maybe CF is doing something stupid when talking to the datasource? Error Executing Database Query. 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 'select b.id_article, group_concat( c.name order by c.use The error occurred in D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 Called from D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 3 : cffunction name=ListArticles access=remote returntype=query 4 : cfargument name=dsn type=string required=yes 5 : cfquery name=qListArticles datasource=#dsn# 6 : select a.id_article, a.title, u.users, t.tags 7 : from articles as a SQLselect a.id_article, a.title, u.users, t.tags from articles as a left outer join ( select b.id_article, group_concat( c.name order by c.userseparator ', ') as users from articlexuser as b inner join users as c on c.id_user = b.id_user group by b.id_article ) as u on u.id_article = a.id_article left outer join ( select d.id_article, group_concat( e.tagorder by e.tag separator ', ') as tags from articlextags as d inner join tags as e on e.id_tag = d.id_tag group by d.id_article ) as t on t.id_article = a.id_article ~| ColdFusion MX7 by AdobeĀ® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272971 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Weird Trouble with CF + MySQL.
Problem solved. The trouble was that mysql 4 don't suport subselects. Using mysql 5 now and everything works fine. Nothing wrong with CF. ;) Doug Brown wrote: Would this line be your culprit? Maybe it has gotten combined due to the paste operation, but shouldn't it be (e.tag order by e.tag separator ', ') ( e.tagorder by e.tag separator ', ') -Original Message- From: Adrien Akkhazza R. Cardoso [mailto:[EMAIL PROTECTED] Sent: Monday, March 19, 2007 4:07 AM To: CF-Talk Subject: Weird Trouble with CF + MySQL. I'm having this problem. The query works fine directly on the database, but when used on coldfusion it gives this error. If I copy the string from the sql field here, and executes on mysql , it works fine again. Don't get what can be wrong. Maybe CF is doing something stupid when talking to the datasource? Error Executing Database Query. 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 'select b.id_article, group_concat( c.name order by c.use The error occurred in D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 Called from D:\Hosting\myCFC\article2.cfc: line 5 Called from D:\Hosting\article\article2.cfm: line 5 3 : cffunction name=ListArticles access=remote returntype=query 4 : cfargument name=dsn type=string required=yes 5 : cfquery name=qListArticles datasource=#dsn# 6 : select a.id_article, a.title, u.users, t.tags 7 : from articles as a SQLselect a.id_article, a.title, u.users, t.tags from articles as a left outer join ( select b.id_article, group_concat( c.name order by c.userseparator ', ') as users from articlexuser as b inner join users as c on c.id_user = b.id_user group by b.id_article ) as u on u.id_article = a.id_article left outer join ( select d.id_article, group_concat( e.tagorder by e.tag separator ', ') as tags from articlextags as d inner join tags as e on e.id_tag = d.id_tag group by d.id_article ) as t on t.id_article = a.id_article ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273057 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4