Colin: I am using the MS driver that comes with the currently nightly distro of Open BlueDragon and I am able to run CTE expressions as well as multiple requests per query call against my SQL2005 instance. That being said I give a +1 to moving your CTE to a stored procedure since it will operate quicker due to SQL Server building an execution plan, etc. I definitely am a proponent of using the database server to do the heavy lifting for business-type persistent data logic. I could go further but I will leave it at that.
Best of luck! -JSLucido On Tue, Apr 26, 2011 at 11:20 AM, Colin MacAllister < [email protected]> wrote: > 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 > -- official tag/function reference: http://openbd.org/manual/ mailing list - http://groups.google.com/group/openbd?hl=en
