Ah good point--if you want multiple statements per query with MySQL you have to enable that in your connection string.
I don't know if the SQL Server drivers work the same way or not but worth checking out. On Apr 25, 2011 11:19 PM, "Stephen Moretti" <[email protected]> wrote: > Hi Colin, > > I don't know much about the SQL Server drivers, but the MySQL drivers only > allow one statement at a time by default. Handy for preventing SQL > injection. > > If the same is true for SQL Server, then only the declare statement may be > running. > > To be honest I'd be inclined to chuck all this SQL into an SP on the server > and run it like that. > > Hope that helps > > Stephen > On Apr 26, 2011 12:15 AM, "Colin MacAllister" <[email protected]> wrote: >> Hmmm ... so Eclipse's SQL Explorer using the Microsoft JDBC driver showed > it >> working as well, returned the one result it should have. So why is this >> returning zero records: >> >> <CFQUERY name="GetAdminUser" datasource="RT"> >> >> DECLARE @group_list TABLE >> ( >> group_id int >> ); >> >> with group_hierarchy ( parent_group, child_group, g_name >> ) >> as >> ( select g1.group_id, >> g2.member_group_id, g1.group_name from rtsb.rt_groups_groups g2 >> join rtsb.rt_groups g1 on >> g1.group_id = g2.group_id >> where g1.group_id = 7316 >> union all >> select g1.group_id, >> g2.member_group_id, g1.group_name from rtsb.rt_groups_groups g2 >> join rtsb.rt_groups g1 on >> g1.group_id = g2.group_id >> join group_hierarchy gh on >> g1.group_id = gh.child_group >> ) >> >> INSERT INTO @group_list (group_id) >> (select distinct id from >> (select distinct parent_group as 'id' from >> group_hierarchy >> union all >> select distinct child_group as 'id' from >> group_hierarchy) >> s); >> >> select distinct u.username from rtsb.rt_users u >> LEFT OUTER JOIN >> rtsb.rt_users_groups ug ON u.site_id = ug.site_id AND u.user_id = >> ug.user_id >> LEFT OUTER JOIN rtsb.rt_metadata >> md ON u.user_id = md.item_id AND md.table_id = 6 >> where >> ( u.primary_group_id in (select * from @group_list) >> or ug.group_id in (select * from @group_list)) >> and u.username = <cfqueryparam >> cfsqltype="cf_sql_varchar" value="#trim(Session.username)#" /> >> AND (md.status = 1 OR md.status is NULL); >> >> >> </CFQUERY> >> <cfdump var="#GetAdminUser#"> >> <cfoutput>#Session.username# in Profile Editors: >> #GetAdminUser.RecordCount#</cfoutput><!--- THIS PRINTS ZERO RECORDS ---> >> <cfabort> >> >> On Mon, Apr 25, 2011 at 6:44 PM, Colin MacAllister <[email protected] >>wrote: >> >>> That's great advice, thanks! >>> >>> >>> On Mon, Apr 25, 2011 at 6:32 PM, Matthew Woodward <[email protected] >>wrote: >>> >>>> On Mon, Apr 25, 2011 at 3:27 PM, Colin MacAllister < >>>> [email protected]> wrote: >>>> >>>>> I have a query that runs fine in SQL Server Studio, returning the >>>>> single username as it is meant to. However, when I place this query in >>>>> a cfquery tag I get zero results returned. Both are hitting the same >>>>> datasource using the same account. I'm wondering whether the "with" >>>>> clause or the table variable is unsupported. Or maybe it is the >>>>> recursive nature of the "with" clause that isn't working in openbd? >>>>> >>>> >>>> Since this is a common point of confusion, I'd like to point out that > once >>>> you're inside the cfquery block all it's doing is passing whatever is in >>>> there to the database over JDBC. So if you're seeing issues as you're >>>> describing it's an issue with the driver and/or what is and isn't > supported >>>> over JDBC. OpenBD doesn't care about the TABLE variable, or the WITH > clause, >>>> or anything like that. At that point it's entirely JDBC and OpenBD > doesn't >>>> know or care what's going on with your SQL. >>>> >>>> Hitting the database in SQL Server Studio and hitting it over JDBC are > two >>>> vastly different things. To troubleshoot you might try using a tool like >>>> Aqua Data Studio that also communicates over JDBC. >>>> >>>> -- >>>> Matthew Woodward >>>> [email protected] >>>> http://blog.mattwoodward.com >>>> identi.ca / Twitter: @mpwoodward >>>> >>>> Please do not send me proprietary file formats such as Word, PowerPoint, >>>> etc. as attachments. >>>> http://www.gnu.org/philosophy/no-word-attachments.html >>>> >>>> -- >>>> official tag/function reference: http://openbd.org/manual/ >>>> mailing list - http://groups.google.com/group/openbd?hl=en >>>> >>> >>> >>> >>> -- >>> Colin >>> >> >> >> >> -- >> Colin >> >> -- >> official tag/function reference: http://openbd.org/manual/ >> mailing list - http://groups.google.com/group/openbd?hl=en > > -- > official tag/function reference: http://openbd.org/manual/ > mailing list - http://groups.google.com/group/openbd?hl=en -- official tag/function reference: http://openbd.org/manual/ mailing list - http://groups.google.com/group/openbd?hl=en
