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
