ColdFusion 2016 output cfquery results in array of structures?

Question

I have query with multiple line of records. I would like to output each line in structure with column name key. After looping over the query records I want to set that structure in array. So far I have correct format of my data but for some reason each line of data is the same. Seems that all the data is coming from one row. Here is example of my code:

<cfset strGraphData = StructNew()>
<cfset arrGraphData = arrayNew(1)>

<cfquery name="getGraphData" datasource="myDB">
    SELECT gr_date, gr_LabelA,  gr_LabelB
    FROM GraphData WITH (NOLOCK)
    WHERE gr_ID = <cfqueryparam value="#arguments.graphID#" cfsqltype="cf_sql_integer">
    ORDER BY gr_date ASC
</cfquery>

<cfoutput query="getGraphData">
    <cfloop list="#getGraphData.getColumnNames()#" index="columnName">
        <cfset strGraphData[columnName] = Trim(getGraphData[columnName][getGraphData.CurrentRow])>
    </cfloop>
    <cfset arrayAppend(arrGraphData, strGraphData)>
</cfoutput>

If I try to dump array this is how my output looks like:

array
1   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

2   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

3   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

4   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

5   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

6   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

7   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

8   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

9   
struct
GR_DATE 2014-05-12 00:00:00.0
GR_LABELA   17
GR_LABELB   [empty string]

Here is example of actual data query:

GR_DATE
2014-01-14 00:00:00.000
2014-02-04 00:00:00.000
2014-02-18 00:00:00.000
2014-03-04 00:00:00.000
2014-03-18 00:00:00.000
2014-04-01 00:00:00.000
2014-04-15 00:00:00.000
2014-04-29 00:00:00.000
2014-05-12 00:00:00.000

GR_LABELA
1
3
5
5
10
16
16
16
17

GR_LABELB
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL

As you can see data in array of structures repeats the data from the last row. I'm not sure where is the bug in my code. If anyone knows how to fix this problem please let me know. Thanks.

Just a note: `NOLOCK` may not do what you think it does. It will let your query read through another locking process, but it's a dirty read (the same as `READ UNCOMMITTED`): meaning that your query may return `x=1` but then the locking process rolls back and now `x=42` in the database. If that's OK, then stick with `NOLOCK` hint, otherwise, look at transaction isolation levels. I also find script-syntax to be much cleaner for looping, but that's almost a religious debate there. — Shawn, Feb 07 '18 at 20:10

score 3 · Accepted Answer · answered Feb 07 '18 at 15:17

3

The problem is the code only creates a single structure. So all those loops are doing is updating that one structure, and appending the same structure to the array, over and over again.

To populate the array with separate structures, you must create a new structure before the inner the loop:

<cfoutput query="getGraphData">
    <!--- create new structure here --->
    <cfset strGraphData = StructNew()>

    <cfloop list="#getGraphData.getColumnNames()#" index="columnName">
        <cfset strGraphData[columnName] = Trim(getGraphData[columnName][getGraphData.CurrentRow])>
    </cfloop>

    <cfset arrayAppend(arrGraphData, strGraphData)>
</cfoutput>

answered Feb 07 '18 at 15:17

SOS

6,430
2
11
29

@Agex I realized that structure can only have one unique key and my code was setting the same keys over and over. Thanks for your help. – espresso_coffee Feb 07 '18 at 15:21
@espresso_coffee - Might also look into using arrays and the structure shortcut syntax instead. They're a little shorter. – SOS Feb 07 '18 at 15:44
2

There are newer options for working with arrays instead of lists, like ArrayEach(), etc... The result is the same, but it's sleeker syntax . – SOS Feb 07 '18 at 15:58

ColdFusion 2016 output cfquery results in array of structures?

1 Answers1