Ok, I'm having a very weird problem with QoQ's in CFMX 6.1 with or without
the cfquery/cfqueryparam hotfix. The problem is if I have apostrophes in the
QoQ results, then I'm getting unexpect results.
In test's 1-3 I should be getting zero records return because the string
"Employee's Field" is in both queries. This is the result I get in CFMX 7,
but in v6.1 it seems to think that the Employee's Field doesn't exist.
To make this situation even more bizarre, Test 6 (which compares to
identical string values with an apostrophe) should return all records, but
instead returns zero. If you take the apostrophes out of the string
comparison, it works as you'd expect.
Anyone seen this before? Anyone know the cause?
--- start code ---
<cfscript>
// query #1
getOptions = queryNew("id,description");
queryAddRow(getOptions);
querySetCell(getOptions, "id", "1");
querySetCell(getOptions, "description", "Employee's Field");
queryAddRow(getOptions);
querySetCell(getOptions, "id", "2");
querySetCell(getOptions, "description", "Contractor Field");
queryAddRow(getOptions);
querySetCell(getOptions, "id", "3");
querySetCell(getOptions, "description", "Temporary Field");
queryAddRow(getOptions);
querySetCell(getOptions, "id", "4");
querySetCell(getOptions, "description", "Other");
// query #2
getImportData = queryNew("description");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Employee's Field");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Employee's Field");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Employee's Field");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Employee's Field");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Temporary Field");
queryAddRow(getImportData);
querySetCell(getImportData, "description", "Temporary Field");
sDelim = chr(11);
</cfscript>
<!---// test 1 //--->
<cfset sValueList = valueList(getOptions.description, sDelim)>
<cfset sValueList = replace(sValueList, "'", "''", "all")>
<cfset sValueList = listQualify(sValueList, "'", sDelim, "all")>
<cfset sValueList = replace(sValueList, sDelim, ",", "all")>
<cfquery name="getInvalidTypes" dbtype="query">
select
*
from
getImportData
where
description NOT IN (#preserveSingleQuotes(sValueList)#)
and
description <> ''
</cfquery>
<cfdump var="#getInvalidTypes#">
<!---// test 2 //--->
<cfset sValueList = valueList(getOptions.description, sDelim)>
<cfset sValueList = replace(sValueList, "'", "''", "all")>
<cfset sValueList = listQualify(sValueList, "'", sDelim, "all")>
<cfset sValueList = replace(sValueList, sDelim, ",", "all")>
<cfquery name="getInvalidTypes2" dbtype="query">
select
*
from
getImportData
where
description NOT IN (<cfqueryparam
value="#valueList(getOptions.description, sDelim)#"
cfsqltype="cf_sql_varchar" list="true" separator="#sDelim#">)
and
description <> ''
</cfquery>
<cfdump var="#getInvalidTypes2#">
<!---// test 3 //--->
<cfquery name="getInvalidTypes3" dbtype="query">
select
*
from
getImportData
where
description NOT IN ('Employee''s Field','Contractor
Field','Temporary Field','Other')
and
description <> ''
</cfquery>
<cfdump var="#getInvalidTypes3#">
<!---// test 4 //--->
<cfquery name="getInvalidTypes4" dbtype="query">
select
*
from
getImportData
where
description = 'Employee''s Field'
</cfquery>
<cfdump var="#getInvalidTypes4#">
<!---// test 5 //--->
<cfquery name="getInvalidTypes5" dbtype="query">
select
*
from
getImportData
where
description LIKE 'Employee''s Field'
</cfquery>
<cfdump var="#getInvalidTypes5#">
<!---// test 6 //--->
<cfquery name="getInvalidTypes6" dbtype="query">
select
*
from
getImportData
where
'Employee''s Field' LIKE 'Employee''s Field'
</cfquery>
<cfdump var="#getInvalidTypes6#">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213421
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
