How to set a dynamic key name in a struct with cfquery column?

Question

I have structure where I want to replace currentRow key with cfquery column recID. This column is integer that is auto incremented in sql table. For some reason my code is failing to create structure with unique key. Here is my code:

<cfquery name="qryAccounts" datasource="myDB">
   SELECT RecID, FirstName, LastName
   FROM Accounts WITH (NOLOCK)
</cfquery>

<cfloop query="qryAccounts">
    <cfset fnAccounts[RecID] = StructNew()>
    <cfset fnAccounts[RecID].RecordID = RecID>
    <cfset fnAccounts[RecID].FirstName = FirstName>
    <cfset fnAccounts[RecID].LastName = LastName>
</cfloop>

Code above produced this result:

[
  {
    "FIRSTNAME": "Mike",
    "LASTNAME": "Richards",
    "RECORDID": 1
  },
  null,
  null,
  null,
  {
    "FIRSTNAME": "John",
    "LASTNAME": "Matt",
    "RECORDID": 6
  }
]

Then I tried to do this:

<cfquery name="qryAccounts" datasource="myDB">
   SELECT RecID, FirstName, LastName
   FROM Accounts WITH (NOLOCK)
</cfquery>

<cfloop query="qryAccounts">
    <cfset fnAccounts["ID"&RecID] = StructNew()>
    <cfset fnAccounts["ID"&RecID].RecordID = RecID>
    <cfset fnAccounts["ID"&RecID].FirstName = FirstName>
    <cfset fnAccounts["ID"&RecID].LastName = LastName>
</cfloop>

And code above produced correct output:

{
  "ID1": {
  "FIRSTNAME": "Mike",
  "LASTNAME": "Richards",
  "RECORDID": 1
  },
"ID6": {
  "FIRSTNAME": "John",
  "LASTNAME": "Matt",
  "RECORDID": 6
  }
}

I'm wondering why first code is failing to produce correct output? Why second version with appended string works fine? Is there any way to fix or work around this problem?

Just a note: Make sure you need and understand what `WITH (NOLOCK)` is doing. It's not always good to add it to queries, and it can cause your query to return incorrect results. Plus, since SQL 2005, it's not usually necessary. — Shawn, May 17 '18 at 16:13
@Shawn I use no lock to keep table available if more than one user tries to access data. I'm not sure if with SQL 2008 that is still necessary. — espresso_coffee, May 17 '18 at 16:14
That's usually what it's used for. My only caution is to make sure that you're ok if you read dirty data. `NOLOCK` is the same as `READ UNCOMMITTED`, and will allow your query to complete while a user is being inserted, but then your struct may cause issues if the inserted user is rolled back and removed from your database. If you're having trouble with blocking on your `Accounts` table, you may want to look at the reasons for the blocking rather than covering it up with `NOLOCK`. — Shawn, May 17 '18 at 16:38

score 5 · Accepted Answer · edited Oct 07 '18 at 01:07

5

All you need to do is to define the variable fnAccounts as a structure.

<cfset fnAccounts = {}>

Without the definition, CF has the liberty to select whatever it seems fit.

<cfquery name="qryAccounts" datasource="myDB">
   SELECT RecID, FirstName, LastName
   FROM Accounts WITH (NOLOCK)
</cfquery>

<cfset fnAccounts = {}>
<cfloop query="qryAccounts">
    <cfset fnAccounts[RecID] = StructNew()>
    <cfset fnAccounts[RecID].RecordID = RecID>
    <cfset fnAccounts[RecID].FirstName = FirstName>
    <cfset fnAccounts[RecID].LastName = LastName>
</cfloop>

Since you are trying to use the RecID as an integer value key, it is similar to how we access an array (fnAccounts[1] first position in the array). Once you have defined fnAccounts as a structure, ColdFusion can look at the variable as a structure.

DEMO

edited Oct 07 '18 at 01:07

Gert Grenander

16,866
6
40
43

answered May 16 '18 at 16:49

rrk

15,677
4
29
45

Thanks for providing solution. I'm kind of confused because we have to define structure twice... Is that just because of the ColdFusion way of getting value from an Array or something else? – espresso_coffee May 16 '18 at 17:39
It's only defined once. The code inside the loop isn't defining `fnAccounts`. It just adds values to it. – SOS May 16 '18 at 17:58
That make sense. Code inside just sets the key for the current row. Thanks again. – espresso_coffee May 16 '18 at 18:48
@espresso_coffee - Small clarification. `fnAccounts = {}` explicitly tells CF to define the variable as a structure. Whereas `fnAccounts[RecID]` instructs CF to create the variable (if needed) and use its best guess about the type. If `RecID` is an integer(1,12,14, ...), CF considers it an array position and creates an array. Otherwise, it creates a structure. – SOS May 16 '18 at 19:31
@Ageax So we must declare `fnAccounts = {}` in case where key is integer otherwise CF will take that as an array. Correct ? – espresso_coffee May 16 '18 at 19:35
@espresso_coffee - Correct. Though IMO, it's good practice to declare it up front and eliminate the guesswork. – SOS May 17 '18 at 00:14
Seems to me it would make more sense for fnAccounts to be an array of structs rather than a struct of struct? – Scott Stroz May 17 '18 at 12:17
@ScottStroz Yes, but all depends on how you have planned to access it. But there is always more than one way of doing something. – rrk May 17 '18 at 12:40

score 5 · Answer 2 · answered May 17 '18 at 12:27

Here is a more generic answer that will work for any query. I put the struct keys to lower case because I typically use this kind of functionality for passing query data to json.

<cfscript>
    function queryToArray(q) {
        var ret = [];
        var cols = listToArray( q.columnList );
        for(var i in q) {
            var row = {};
            for(var col in cols ) {
                row[lcase(col)] =  i[col];
            }
            arrayAppend(ret, row);
        }
        return ret;
    }
</cfscript>

Putting the data into an array of structs (instead of a struct if structs) seems more intuitive to me when referring to query data.

*an array of structs (instead of a struct if structs) seems more intuitive* I agree - unless there is a specific need to look up data by unique key. — SOS, May 17 '18 at 12:35

score 3 · Answer 3 · answered May 17 '18 at 00:39

3

My solution is based on RRK's, but I like cfscript better

<cfquery name="qryAccounts" datasource="myDB">
  SELECT RecID, FirstName, LastName
  FROM Accounts WITH (NOLOCK)
</cfquery>

<cfscript>
  fnAccounts = {};
  for (i in qryAccounts) {
     fnAccounts[i.RecID] = {
       RecordID : i.RecID,
       FirstName : i.FirstName,
       LastName : i.LastName
       };
     } // end for 
</cfscript>

The cfquery could be moved to cfscript too

answered May 17 '18 at 00:39

James A Mohler

11,060
15
46
72

1

Also note the sleeker syntax for creating structures `{ RecordID : "value", FirstName : "value", ....}` – SOS May 17 '18 at 12:45

How to set a dynamic key name in a struct with cfquery column?

3 Answers3