cfscript loop through query - querySetCell v direct assignment

Report · Jan 13, 2019

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.

Report · Jan 14, 2019

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,

^ _ ^

Report · Jan 14, 2019

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

Report · Jan 14, 2019

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.

Report · Jan 14, 2019

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,

^ _ ^

Report · Jan 14, 2019

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,

^ _ ^

Report · Sep 17, 2019

It's interesting, yes? Little undocumented gotchas

Adobe Community

cfscript loop through query - querySetCell v direct assignment