cfquery sql erratic error

> Qcontacts2.contact_type_acronym

Note, I'm assuming the underlying data used to generate the sql in the first place isn't changing.  If however, it's constantly changing, then that type of error is to be expected.  ie It's just one of the hazards of dynamic data.

QContacts2 is a constant query reused to generate dynamic sql strings. Everything I see seems to be properly scoped.  The one constant with the particular error that occurs erratically is it's the second occurance of the cfloop;

<cfloop query="Qcontacts2">
                        left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 1
                        left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid
                        left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id
                  </cfloop>

It generates the same dynamic sql strings for each iteration of the loop.  For example;

left join client_services_contacts as #Qcontacts2.contact_type_acronym#

should resolve to;

left join client_services_contacts as hr
left join client_services_contacts as mkt
left join client_services_contacts as tax
left join client_services_contacts as ag

but the resulting sql string from the error message shows;


left join client_services_contacts as hr
left join client_services_contacts as hr
left join client_services_contacts as hr
left join client_services_contacts as hr

The other three occurances of the cfloop of QContacts2 show the proper iterations and creation of sql strings.

  > Everything I see seems to be properly scoped.

"Qcontacts2" isn't.  Any variable you create within the function has to be var scoped.  Otherwise it ends up in the shared variables scope. In this case, you could have multiple threads all trying to initialize or loop through "Qcontacts2" at the same time.  As it's the same object, which thread would win? How would CF even know which row the loop was on?  Like I said, lack of var scoping can cause all sorts of wacky things to happen. The only way to prevent it is to ensure you var scope everything.

Gah.. hit return too soon.

I was going to add that since var scoping is quick and easy, you should try it first. See if it resolves the issue. If not, then try more extensive debugging.

I guess I meant to say that I've added;

<cfset var Qcontacts2="" />

and nothing else needs to be var scoped.  All I can do now is wait and see since I can't reproduce the error.

Ah, ok.

Sounds like a plan. Btw, if you ever need to check whether variables are var scoped, structCount() the variables scope at the very beginning and end of the function. If the count stays the same, you know everything was properly var scoped.

Justgot another error;
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The correlation name 'billtypes' is specified multiple times in a FROM clause.

This time the same cfloop should have had iterations of "bill", "hr",
"tax", and"ag".  It actually resolved to "bill", "hr", "tax", and "bill" causing a duplicate of the "bill" portion.

SELECT 0 as activecheck,jb.ID, jb.status, jb.jobtitle, jb.comptitle, jb.jobdate, jb.closedate, jb.feetype, c.name as companyname ,uibill.lastname + ', ' + uibill.firstname as billcontactname, bill.contact_id as billcontact_id, bill.active as billactive ,uihr.lastname + ', ' + uihr.firstname as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,uitax.lastname + ', ' + uitax.firstname as taxcontactname, tax.contact_id as taxcontact_id, tax.active as taxactive ,uiag.lastname + ', ' + uiag.firstname as agcontactname, ag.contact_id as agcontact_id, ag.active as agactive ,uicmp.lastname + ', ' + uicmp.firstname as cmpcontactname, cmp.contact_id as cmpcontact_id, cmp.active as cmpactive FROM Board_Details as jb LEFT JOIN companies as c on c.id = jb.companyid left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = jb.ID and hr.active = 1 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS uihr ON uihr.ID = hr.contact_id left join client_services_contacts as tax on tax.contacttypeid = 10 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id WHERE 0=0 and ad_type = 'Intern' AND ( (bill.contact_id = 94299 and bill.package = 0) or (hr.contact_id = 94299 and hr.package = 0) or (tax.contact_id = 94299 and tax.package = 0) or (ag.contact_id = 94299 and ag.package = 0) or (cmp.contact_id = 94299 and cmp.package = 0) or 0 = 1) union all SELECT 1 as activecheck,jb.ID, jb.status, jb.jobtitle, jb.comptitle, jb.jobdate, jb.closedate, jb.feetype,c2.name as companyname ,ui2bill.lastname + ', ' + ui2bill.firstname + '(PREV)' as billcontactname, bill.contact_id as billcontact_id, bill.active as billactive ,ui2hr.lastname + ', ' + ui2hr.firstname + '(PREV)' as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,ui2tax.lastname + ', ' + ui2tax.firstname + '(PREV)' as taxcontactname, tax.contact_id as taxcontact_id, tax.active as taxactive ,ui2ag.lastname + ', ' + ui2ag.firstname + '(PREV)' as agcontactname, ag.contact_id as agcontact_id, ag.active as agactive ,ui2cmp.lastname + ', ' + ui2cmp.firstname + '(PREV)' as cmpcontactname, cmp.contact_id as cmpcontact_id, cmp.active as cmpactive FROM Board_Details as jb LEFT JOIN companies as c2 on c2.id = jb.companyid left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 0 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS ui2bill ON ui2bill.ID = bill.contact_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = jb.ID and hr.active = 0 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS ui2hr ON ui2hr.ID = hr.contact_id left join client_services_contacts as tax on tax.contacttypeid = 10 and tax.service_id = jb.ID and tax.active = 0 left join client_services_contact_Types as taxtypes on taxtypes.id = tax.contacttypeid left JOIN Users_Info AS ui2tax ON ui2tax.ID = tax.contact_id left join client_services_contacts as ag on ag.contacttypeid = 19 and ag.service_id = jb.ID and ag.active = 0 left join client_services_contact_Types as agtypes on agtypes.id = ag.contacttypeid left JOIN Users_Info AS ui2ag ON ui2ag.ID = ag.contact_id left join client_services_contacts as cmp on cmp.contacttypeid = 20 and cmp.service_id = jb.ID and cmp.active = 0 left join client_services_contact_Types as cmptypes on cmptypes.id = cmp.contacttypeid left JOIN Users_Info AS ui2cmp ON ui2cmp.ID = cmp.contact_id WHERE 0=0 and ad_type = 'Intern' AND ( (bill.contact_id = 94299 and bill.package = 0) or (hr.contact_id = 94299 and hr.package = 0) or (tax.contact_id = 94299 and tax.package = 0) or (ag.contact_id = 94299 and ag.package = 0) or (cmp.contact_id = 94299 and cmp.package = 0) or 0 = 1) ORDER BY jb.jobdate DESC, jb.id, activecheck

Select allOpen in new window

1. Can you post the current code for "getPackages" and "getContentTypes"?

2. Put a cftry/cfcatch around the query that's failing.  Inside the cfcatch, email yourself a full dump of #Qcontacts2#, along with the error message.  So next time the error occurs, you can check the query results against the actual query generated for further clues.

3. Are either of these functions/components being stored in a shared scope?

4. As a last resort you try could supplying the row number explicitly, though you shouldn't have to.
ie <cfloop query="Qcontacts2">
         #Qcontacts2.columnName[currentRow]#
   </cfloop>

The following functions are all invoked in sequence and each one is very similar to the other.  I have attached getPackages and getContractTypes.  The error can happen in any one of the functions. The function getContactTypes will return the apporpriate acronyms for the particular function that calls it by changing the argument servicelineid..

getContractStaffing
GetExecutiveSearches
getJobAds
getOrgAssessment
getPackages
getPurchases
getSalaryBenchmarking

<cffunction name="getPackages" output="false" returntype="query">
		<cfargument name="indivNum" required="No" type="string" default="" />
		<cfargument name="companyNum" required="No" type="string" default="" />
		<cfset var queryResults="" />
		<cfset var Qcontacts2="" />
		<cfinvoke component="/control/sitewide/cfc.services" method="getContactTypes" servicelineid="13" returnvariable="Qcontacts2" />
		<cfquery datasource="#request.dsn#" name="queryResults">
			SELECT 0 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 1 
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum#) or
					</cfloop>
					0 = 1)
			</cfif>
			union all
			SELECT 1 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 0			
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
 					and (
					<cfloop query="Qcontacts2">
						(#Qcontacts2.contact_type_acronym#.contact_id > 0 and #Qcontacts2.contact_type_acronym#.package = 0) or
					</cfloop>
					0 = 1) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# ) or
					</cfloop>
					0 = 1)
			</cfif>
			ORDER BY id desc, activecheck, start_date DESC 
		</cfquery>
		<cfreturn queryResults />
	</cffunction>

Select allOpen in new window

<cffunction name="getContactTypes" returntype="query" access="remote">
		<cfargument name="servicelineID" required="yes" />
		<cfif val(arguments.servicelineID) eq 0>
			<cfquery datasource="#request.dsn#" name="contacttypes">
				select * from client_services_contact_types group by id, description, contact_type_acronym, servicelineids order by id
			</cfquery>
		<cfelse>
			<cfquery datasource="#request.dsn#" name="contacttypes">
				select * from client_services_contact_types where (CHARINDEX(',#arguments.servicelineid#,', ','+servicelineids+',') > 0) group by id, description, contact_type_acronym, servicelineids order by id 
			</cfquery>
		</cfif>
		<cfreturn contacttypes />
	</cffunction>

Select allOpen in new window

>  <cfquery datasource="#request.dsn#" name="contacttypes">

Looks like there's still queries/variables that aren't VAR scoped.  You need to go through all of the functions you listed and make sure every single query, cfinvoke result, etc... is var scoped.  Use structCount(variables) at the beginning and end of the function to make sure you've gotten them all.

The structcount before and after each cfquery in each function is a constant 100. That is, as of now.
I am emailing myself the count before and after.

The structCount() is just a one time thing. Once you've fixed the code, it won't change.

I understand. But, those functions I listed above are called from two different pages.  The only time I get an error is when the indivnum is passed in. I am getting emails whether theyare called with indivnum or companynum. At first I was getting a count of 99 then 100.  Now they are all 100.  So, I guess that problem has been corrected.  All I can do now is wait and see if I get something from cftry/cfcatch.

So far, so good.  Looks like it's working.  Thanks.

Tough one for me.  Easy for EE.

> So far, so good.  Looks like it's working.  

Great! Glad to hear it :)