The DBMS returned an unspecified error. The command code was 0.
Hello All,
I am running into a problem where I think that I am exceeding limits for integers. I have a client that needs to have 2000 groups with subgroups. All of which have group ID's and when I try to query through the (see below in bold), it is throughing an error at that line. Is it because <cfqueryparam cfsqltype="cf_sql_integer" value="#AdHocGroupID#" /> exceeds the limits for cf_sql_integer??
That is what I suspect. Any help or advice would be greatly appreciated.
Thanks.
Here it the code. You'll see the trouble point in bold....
<cffunction name="GetAccountGroupsAndSubgroups" access="public" returntype="Array" hint="Returns an array of groups and subgroups based on account." output="false">
<cfargument name="AccountID" type="string" required="yes" hint="ID of the currently logged in account.">
<cfargument name="AllowedGroupIDList" type="string" required="yes" hint="List of groups this user can manage. -1 means all." />
<cfargument name="AllowedSubGroupIDList" type="string" required="yes" hint="List of subgroups this user can manage. -1 means all." />
<cfargument name="AdHocGroupID" type="numeric" required="no" default="0" hint="If not 0 (zero) return the add hoc group.">
<cfargument name="IncludeUserCounts" type="boolean" required="no" default="false" hint=".">
<cfargument name="ForceRefresh" type="boolean" required="no" default="false" hint="If true, does not return a cached value.">
<cfset var queryGroupCounts = "">
<cfset var querySubGroupCounts = "">
<cfset var queryGetSpecificSubGroups = "">
<cfset var i = 0>
<cfset var g = 0>
<cfset var groups = ArrayNew(1)>
<cfset var CachedWithin = CreateTimeSpan(0,0,30,0)>
<!--- If they cant manage anything just return an empty list --->
<cfif AllowedGroupIDList eq "" and AllowedSubGroupIDList eq "">
<cfreturn groups>
</cfif>
<!--- No need to cache the query if no user counts are made --->
<cfif ForceRefresh or Not IncludeUserCounts>
<cfset CachedWithin = CreateTimeSpan(0,0,0,0)>
</cfif>
<!--- Get groups even if they can't manage any, because they can manage subgroups --->
<cfquery name="queryGroupCounts" timeout="1800" datasource="#This.Dsn#" result="queryGroupCountsResult" CachedWithin="#CachedWithin#">
DECLARE @account_id INT
SET @account_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#AccountID#" />
SELECT
<cfif IncludeUserCounts>COUNT(DISTINCT ss.sub_id)<cfelse>0</cfif> AS userCount,
<cfif IncludeUserCounts>COUNT(DISTINCT tt.sub_id)<cfelse>0</cfif> AS sirenCount,
<cfif IncludeUserCounts>COUNT(DISTINCT uu.sub_id)<cfelse>0</cfif> AS qualcommCount,
ag.ad_hoc, ag.group_id, ag.group_title, ag.do_not_delete
FROM ACCOUNT_GROUPS ag
LEFT JOIN ACCOUNT_USER_GROUPS aug ON ( ag.group_id = aug.group_id and aug.account_id = @account_id )
LEFT JOIN SUBSCRIPTION s ON ( aug.sub_id = s.sub_id and s.account_id = @account_id and s.active = 1 )
<cfif IncludeUserCounts>
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
LEFT JOIN SIRENS b ON a.sub_id = b.sub_id
LEFT JOIN QUALCOMM_UNITS c ON a.sub_id = c.sub_id
WHERE a.ACCOUNT_ID = @account_id
AND b.sub_id IS NULL
AND c.sub_id IS NULL
) ss ON s.sub_id = ss.sub_id
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
INNER JOIN SIRENS b ON a.sub_id = b.sub_id
WHERE a.ACCOUNT_ID = @account_id
) tt ON s.sub_id = tt.sub_id
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
INNER JOIN QUALCOMM_UNITS b ON a.sub_id = b.sub_id
WHERE a.ACCOUNT_ID = @account_id
) uu ON s.sub_id = uu.sub_id
</cfif>
WHERE ag.ACCOUNT_ID = @account_id
AND ag.admin_user = 0
AND ag.active = 1
AND ag.group_id IN
(
SELECT group_id
FROM ACCOUNT_GROUPS
WHERE account_id = @account_id
AND active = 1
AND admin_user = 0
AND ad_hoc = 0
<cfif AllowedGroupIDList neq -1 and AllowedGroupIDList neq "">
AND group_id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#AllowedGroupIDList#" list="yes" />)
</cfif>
<!--- This allows us to return groups they cannot manage, but which contain subgroups they can manage --->
<cfif AllowedSubGroupIDList neq -1 and AllowedSubGroupIDList neq "">
UNION ALL
SELECT group_id
FROM ACCOUNT_SUB_GROUPS
WHERE sub_group_id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#AllowedSubGroupIDList#" list="yes" />)
AND account_id = @account_id
AND active = 1
</cfif>
<cfif AdHocGroupID neq 0>
UNION ALL
SELECT group_id
FROM ACCOUNT_GROUPS
WHERE account_id = @account_id
AND active = 1
AND admin_user = 0
AND ad_hoc = 1
AND group_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#AdHocGroupID#" />
</cfif>
)
GROUP BY ag.ad_hoc,
ag.group_title,
ag.group_id ,
ag.do_not_delete
ORDER BY ag.ad_hoc DESC,
ag.group_title,
ag.group_id
</cfquery>
<!---
<cfdump var="#queryGroupCounts#" />
<cfoutput><pre>#queryGroupCountsResult.sql#</pre></cfoutput>
<cfabort>
--->
<cfif AllowedSubGroupIDList neq "">
<cfquery name="querySubGroupCounts" datasource="#This.Dsn#" result="querySubGroupCountsResult" CachedWithin="#CachedWithin#">
DECLARE @account_id INT
SET @account_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#AccountID#" />
SELECT
<cfif IncludeUserCounts>COUNT(DISTINCT ss.sub_id)<cfelse>0</cfif> AS userCount,
<cfif IncludeUserCounts>COUNT(DISTINCT tt.sub_id)<cfelse>0</cfif> AS sirenCount,
<cfif IncludeUserCounts>COUNT(DISTINCT uu.sub_id)<cfelse>0</cfif> AS qualcommCount,
asg.group_id, asg.sub_group_id, asg.sub_group_title, asg.do_not_delete
FROM ACCOUNT_SUB_GROUPS asg
<cfif IncludeUserCounts>
LEFT JOIN ACCOUNT_USER_SUB_GROUPS ausg ON
(
asg.sub_group_id = ausg.sub_group_id
AND ausg.account_id = @account_id
)
LEFT JOIN SUBSCRIPTION s ON ( ausg.sub_id = s.sub_id and s.account_id = @account_id and s.active = 1 )
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
LEFT JOIN SIRENS b ON a.sub_id = b.sub_id
LEFT JOIN QUALCOMM_UNITS c ON a.sub_id = c.sub_id
WHERE a.ACCOUNT_ID = @account_id
AND b.sub_id IS NULL
AND c.sub_id IS NULL
) ss ON s.sub_id = ss.sub_id
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
INNER JOIN SIRENS b ON a.sub_id = b.sub_id
WHERE a.ACCOUNT_ID = @account_id
) tt ON s.sub_id = tt.sub_id
LEFT JOIN
(
SELECT a.sub_id
FROM SUBSCRIPTION a
INNER JOIN QUALCOMM_UNITS b ON a.sub_id = b.sub_id
WHERE a.ACCOUNT_ID = @account_id
) uu ON s.sub_id = uu.sub_id
</cfif>
WHERE asg.account_id = @account_id
AND asg.active = 1
<cfif AllowedSubGroupIDList neq -1>
AND asg.sub_group_id in (<cfqueryparam cfsqltype="cf_sql_integer" value="#AllowedSubGroupIDList#" list="yes" />)
</cfif>
GROUP BY
asg.sub_group_id,
asg.sub_group_title,
asg.group_id,
asg.do_not_delete
ORDER BY
asg.sub_group_title,
asg.sub_group_id
</cfquery>
<!---<cfdump var="#querySubGroupCounts#" />
<cfoutput><pre>#querySubGroupCountsResult.sql#</pre></cfoutput>
<cfabort>
--->
</cfif>
<cfloop from="1" to="#queryGroupCounts.RecordCount#" index="i">
<cfscript>
ArrayAppend(groups, StructNew());
groups.id = queryGroupCounts.group_id;
groups.title = queryGroupCounts.group_title;
groups.adHoc = ( queryGroupCounts.ad_hoc eq 1 );
groups.subgroups = ArrayNew(1);
groups.count = queryGroupCounts.userCount;
groups.sirenCount = queryGroupCounts.sirenCount;
groups.qualcommCount = queryGroupCounts.qualcommCount;
groups.doNotDelete = queryGroupCounts.do_not_delete;
</cfscript>
<cfif AllowedSubGroupIDList neq "">
<!--- Use query of queries to get the subgroups for current group id --->
<cfquery name="queryGetSpecificSubGroups" dbtype="query">
select *
from querySubGroupCounts
where group_id = #queryGroupCounts.group_id#
</cfquery>
<cfscript>
if(queryGetSpecificSubGroups.RecordCount gt 0)
{
for(j = 1; j lte queryGetSpecificSubGroups.RecordCount; j++)
{
g = StructNew();
g.id = queryGetSpecificSubGroups.sub_group_id
g.title = queryGetSpecificSubGroups.sub_group_title
g.count = queryGetSpecificSubGroups.userCount
g.sirenCount = queryGetSpecificSubGroups.sirenCount
g.qualcommCount = queryGetSpecificSubGroups.qualcommCount
g.doNotDelete = queryGetSpecificSubGroups.do_not_delete
ArrayAppend(groups.subgroups, g);
}
}
</cfscript>
</cfif>
</cfloop>
<cfreturn groups>
</cffunction>
AdChoices