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
