Highlighted

cfscript loop through query - querySetCell v direct assignment

Explorer ,
Jan 13, 2019

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.

TOPICS
Server administration

Views

825

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfscript loop through query - querySetCell v direct assignment

Explorer ,
Jan 13, 2019

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.

TOPICS
Server administration

Views

826

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 13, 2019 0
LEGEND ,
Jan 14, 2019

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2019 0
Explorer ,
Jan 14, 2019

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2019 0
LEGEND ,
Jan 14, 2019

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2019 0
LEGEND ,
Jan 14, 2019

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:

_CF_querySetCellExample.png

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2019 0
LEGEND ,
Jan 14, 2019

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2019 0
Explorer ,
Sep 17, 2019

Copy link to clipboard

Copied

It's interesting, yes? Little undocumented gotchas

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2019 0