Copy link to clipboard
Copied
I was rather surprised to find out that in cfscript, using querySetCell inside a query loop does not set the column value immediately available for use.
The column value is only present AFTER the loop is complete.
for (row in query) {
value = row["LAST"] & ", " & row["FIRST"]; //value="Doe, John"
querySetCell(query,"NAME",value,query.currentRow);
//value of row.NAME is not set
if (value == row["NAME"]) {
writeoutput("Match"); //never
} else {
writeoutput("No Match"); //always
}
}
writedump(query); //value of row.NAME is set
However, if you use direct assignment, the column value is immediately available for use.
for (row in query) {
value = row["LAST"] & ", " & row["FIRST"]; //value="Doe, John"
row["NAME"] = value;
//value of row.NAME is set
if (value == row["NAME"]) {
writeoutput("Match"); //always
} else {
writeoutput("No Match"); //never
}
}
writedump(query); //value of row.NAME is set
I find this rather disturbing. Is it a bug? Depends on how you look at it, I suppose.
Certainly, the documentation should clarify the difference.
Copy link to clipboard
Copied
In your first example, you are setting a variable called value to be "#last#, #first#" of the query object output, then checking to see if row.NAME (which doesn't exist, or it shouldn't exist unless it was part of the original query) equals the value of value. Which it doesn't, so it will always fail.
In your second example, you are setting value to be "#last#, #first#" of the query object output, then you are setting row.NAME to equal value (so it now exists where it didn't in your first example), THEN you check to see if the variable in the ROW scope that you just set matches (which it does, you just created it and set the value).
This is not a bug.
HTH,
^ _ ^
Copy link to clipboard
Copied
Let me clarify - this is the query
query = queryNew("FIRST,LAST","Varchar,Varchar", [ ["John","Doe"], ["Jane","Doe"], ["Jimmy","Doe"] ]);
//query fields with data are FIRST,LAST
queryAddColumn(query,"NAME","varchar",arrayNew(1));
//add column NAME to query, no data
So the column NAME exists in the query, it just has no data
Copy link to clipboard
Copied
Thank you for the clarification. However since NAME is blank, value NEQ row.NAME. Your first example will always be false.
HTH,
^ _ ^
UPDATE: Okay, nevermind.. I see what you did, there. Still, I am not aware of any issues regarding querySetCell(). In fact, I used it quite recently with no issues.
Just curious, what version of CF and what platform you are running. If it's a recent version (CF2016/18), then it may be a bug. I'm on CF10 and CF11.
UPDATE2: Also, I never use for(row in query), I prefer for(i=1; i lte query.recordcount; i++) for iterating objects.
Copy link to clipboard
Copied
It might be the environment you're using. I'm running CF11 on a Windows server in our DEV environment, and the following code produced the screencap I will attach.
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
</head>
<body>
<cfscript>
q = queryNew("FIRST,LAST","varchar,varchar",[["John","Doe"],["Jane","Doe"],["John","Dough"]]);
queryAddColumn(q,"NAME","varchar",arrayNew(1));
for(row in q){
value = row["LAST"] & ", " & row["FIRST"];
querySetCell(q,"NAME",value,q.currentRow);
writeDump(q); writeOutput("<br /><br />");
}
</cfscript></body>
</html>
Here is the screencap from my FireFox browser:
This worked in CF11 on a Windows server, so I'm going to guess that you're either on a different version, or on a different platform. If that's the case, then I'd suggest you submit a bug to Tracker. If you do, please provide a link to it, and I will vote for it.
V/r,
^ _ ^
Copy link to clipboard
Copied
I take that back. Apparently you have discovered something, here. I changed my code a little bit, and it's not working. Try the following:
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
</head>
<body>
<cfscript>
q = queryNew("FIRST,LAST","varchar,varchar",[["John","Doe"],["Jane","Doe"],["John","Dough"]]);
queryAddColumn(q,"NAME","varchar",arrayNew(1));
for(row in q){
value = row["LAST"] & ", " & row["FIRST"]; writeOutput(value);
//querySetCell(q,"NAME",value,q.currentRow);
row["NAME"] = value;
writeDump(q); writeOutput("<br /><br />");
}
</cfscript>
<cfdump var="#q#" />
</body>
</html>
This, for me, is leaving NAME blank in every iteration. Every writeDump and the CFDUMP leaves all NAME column values blank.
V/r,
^ _ ^
Copy link to clipboard
Copied