I think we're on to something, about the driver. I just uploaded the same code to a windows machine running coldfusion 5, and it worked. May go the SP route.
On Apr 26, 9:52 am, Matthew Woodward <[email protected]> wrote: > 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
