Coldfusion Query broken column structure

Question

A coldfusion query's column can be referenced like a 2D array from what I know of my past experience. Sometimes though I get this issue.

create query from spreadsheet
put column names into an array
only get first element when trying to access the row
have to use a workaround



//imports is a query object after this function
var imports = convertSpreadsheetWOHeaders(spreadsheet, 1);
//this is used to give name values in the json struct, not to access the query columns
var jsonHeaders = ListToArray("number,constructiontype,description,site_address,parcel,permit_date,note_Applicant,note_Contractor,valuation,note_Bld_Fees,note_Other_Fees");
//this gives me ["col_1","col_2","col_3",,,etc]. used to access query columns
var columnHeaders = imports.getColumnNames();
writeDump(imports[columnHeaders[1]]);
writeDump(imports);

I am left with just the first element in column one. And I get of course:

Message: You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.

When trying to do this:

structInsert(jsonStruct,jsonHeaders[j],imports[columnHeaders[j]][i]);

However, this works:

 writeDump(ListToArray(ArrayToList(imports[columnHeaders[1]],'|'),'|',true));

I first do a dump of imports["col_1"] and i get only the first element. Then I do a dump of ListToArray(ArrayToList(imports["col_1"])) like you see in the above image and it gives me the whole column.

Why can I not access the column correctly in the first place?

Sorry, all. Just added the question. Why does it work after the conversions? — Matt Wilde, Jun 22 '16 at 15:54
The last screen shot is a dump of what object: columnHeaders, imports, ...? — Leigh, Jun 22 '16 at 16:04
@Leigh dump of first column in imports `writeDump(ListToArray(ArrayToList(imports[columnHeaders[i]],'|'),'|',true));` — Matt Wilde, Jun 22 '16 at 16:07
Ohh wait... if `imports` is a *query* object that would explain why `imports["col_1"]` does not work. Traditionally in CF, `#queryName.columnName#` or `queryName["columnName"]` are treated as a shortcut for `#queryName.columnName[1]#` ie Value in the first row of the query, which is obviously a simple value. `ArrayToList` is an exception to this convention. I imagine it behaves differently because that function *requires* an array, not a simple value. — Leigh, Jun 22 '16 at 16:57
@Leigh. Yes I made a comment above imports variable to clarify that. That makes sense. I guess it's normally most convenient to have this shortcut format?? ArrayToList must _force_ it to return the array format. Could you post this as an answer please. — Matt Wilde, Jun 22 '16 at 17:02
(Edit) Yes, because there is no way the function could work if passed in a simple string. So it always uses the query column array. Which version are you using? Unless I am missing something, the code seemed to work fine : http://trycf.com/gist/3651354432ba716e4f726147fb9af81c/acf?theme=monokai — Leigh, Jun 22 '16 at 17:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115336/discussion-between-matt-wilde-and-leigh). — Matt Wilde, Jun 22 '16 at 17:28

score 3 · Accepted Answer · answered Jun 23 '16 at 20:21

The Real Problem:

I was originally trying to access the jsonHeaders list as an array without doing ListToArray() on it. After that my function worked.

BUT. This next part is helpful.

When attempting to access a query object, doing queryObject["columnName"] is considered a shortcut of queryObject["columnName"][1] so coldfusion will just give you the first element.

But when I said ListToArray(ArrayToList()) coldfusion sees that ArrayToList MUST take in an array so an exception is made and the column is returned as an array.

To get a column back as an array to work with you can do a couple things

ListToArray(ArrayToList(query["column"]));
ListToArray(valueList(query.column));
valueArray(query, "column");

Coldfusion Query broken column structure

1 Answers1