Cf-script convert query result in structure with the unique key?

Question

I have few functions in my system that should be converted from CFML to CFSCRIPT. While working on this project I run to the situation where my structure outputs more result (a lot of empty rows) than it should, even query returns only one row. Here is example of my code in CFSCRIPT:

cfstoredproc( procedure="GetZip", datasource=Application.dsnRead ) {
    cfprocparam( dbvarname="@Zip", value=trim(52401), cfsqltype="cf_sql_char", maxlength=5 );
    cfprocresult( name="ZipResult" );

}

local.strZip = {};
strZip[ZipResult.RecID] = {
    "City" : trim(ZipResult.City),
    "State" : trim(ZipResult.State)
};

writeDump(strZip);

Here is what I get for the output:

array
1   [undefined array element]
2   [undefined array element]
3   [undefined array element]
4   [undefined array element]
5   [undefined array element]
6   [undefined array element]
7   [undefined array element]
8   [undefined array element]
9   [undefined array element]
10  [undefined array element]
11  [undefined array element]
12  [undefined array element]
13  [undefined array element]
14  [undefined array element] 
...

I'm wondering what is the best way to output query result(s) in structure and use RecID as a unique key? This example and query above always will return 1 record at the time but also I'm wondering how this code would work if I need to loop over the query result with multiple records?

Update: I think that I found the problem. The RecID is auto increment id in my db table. When i return RecID the value can be 56743 for example. So if I pass RecID as a key in my structure, that will cause so many rows in the structure. My question is how to prevent this? Is there a way to just set the key?

Answer 1

I'm not sure why your CF is converting strZip to an array, but that is why you are getting a bunch of empties. A structure key can be an integer, but when you tell an array to insert x[42], you will have 42 elements in your array. local.strZip = {}; pretty explicitly states that strZip is a struct. However, scopes can get weird in CF sometimes. Depending on how you're using it, I think when you do strZip[ZipResult.RecID] it may be creating a new unscoped variable that is an array. And then when you dump it, you are dumping the array version of strZip. You can try using local.strZip[ZipResult.RecID] and see if that changes your behavior. Or try dumping local.strZip and see if it's empty.

Or you can do:

<cfscript>
    // Build a fake query object.
    ZipResult = queryNew(
        "RecID, City, State",
        "integer, varchar, varchar",
        [
              { RecID: 99, City: "Nashville", State: "TN" }
        ]
    );
    writeDump(ZipResult); // What's in our query?


local.strZip = {
    "#ZipResult.RecID#" : {
      City : trim(ZipResult.City) , 
      State : trim(ZipResult.State)
    }
};

writeDump(strZip);

</cfscript>

https://trycf.com/gist/28440630b0aa7ba1642e45bab3503652/acf2016?theme=monokai

NOTE: I was unable to duplicate your behavior in TryCF, but I'm also not likely executing that code in the same scope that you are. CONFIRMED: https://cffiddle.org/app/file?filepath=50f4e710-bd9b-40dd-a03a-15695bdd5a0d/c476f91b-6931-4295-be67-e4309aecfa0c/0492a7c6-029f-4227-941a-faee9da5a7cc.cfm