returning the query row with the way the columns are called in sql query

Question

I have a query where I am calling the columns like the way i want, I cannot use ColumnList of query because it sorts the column alphabatically, another thing i need to get the specific row of the query and its associated columns in a structure:

So here is my function which i am trying to bring the columns in the manner i want:

<cffunction name="rowToStruct" access="public" returntype="struct" output="false">
    <cfargument name="queryObj" type="query" required="true" />
    <cfargument name="row" type="numeric" required="true" />
    <cfset var returnStruct = structNew()>
    <cfset var colname = "">
    <cfset arguments.queryObj = arrayToList(arguments.queryObj.getMeta().getColumnLabels())>        
    <cfloop list="#arguments.queryObj#" index="colname">
      <cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>
    </cfloop>
    <cfreturn returnStruct/>
</cffunction>

before the above change the function was like this below:

<cffunction name="rowToStruct" access="public" returntype="struct" output="false">
    <cfargument name="queryObj" type="query" required="true" />
    <cfargument name="row" type="numeric" required="true" />

    <cfset var returnStruct = structNew()>
    <cfset var colname = "">

    <cfloop list="#arguments.queryObj.columnList#" index="colname">
      <cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>
    </cfloop>

    <cfreturn returnStruct/>
</cffunction>

Mine above one is giving me an error:

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

Answer 1

The getMetadata() function returns the columns in the order they were defined in the original statement. Docs: GetMetaData.

I can't see why your code would produce that error, although I'd do this:

<cfset returnStruct[colname] = arguments.queryObj[colname][arguments.row]>

Can you update your question with the exact error as it displays on the screen, including the code it focuses on, and the line numbers concerned (switch Robust Exception Handling on, if it's not on already)

Answer 2

The reason for the error is because you are overwriting the query object passed into the function. The new code resets the value of arguments.queryObj to a simple string here:

<cfset arguments.queryObj = arrayToList(arguments.queryObj.getMeta().getColumnLabels())> 

Later in the code, you try and use the arguments.queryObj variable as if it were still a query object. Obviously, this causes an error because the variable now represents a string:

  <cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>

The solution is to use a different variable to store the list of column names instead. Since it will also be function local variable, just be sure to scope it properly with local or var.

That said, as Adam mentioned in his answer, the feature you need is already supported using one of the built-in functions. So there is no really need to use the undocumented methods of the coldfusion.sql.QueryTable query class IMO. GetMetaData(queryObject) returns an array of structures containing two keys: Name (column name), IsCaseSensitive (boolean). To iterate through it, use an "array" loop instead of a "list" loop:

...
<cfset var returnStruct = {}>
<cfset var col = "">
<cfset var colArray = getMetaData(arguments.queryObj)>        
<cfloop array="#colArray#" index="col">
  <cfset returnStruct[col.Name] = arguments.queryObj[col.Name][arguments.row]>
</cfloop>
...

Update 1:

I cannot use ColumnList of query because it sorts the column alphabatically

The bigger issue here is that CF structures are not ordered. Even if you add the values to the structure in the proper order, that order will not be maintained. You either need to address that in the calling code (ie use GetMetaData(query)) OR have the cffunction return both the ordered column names AND the row data. The latter seems a bit redundant, but it all depends on how you are using the function in your application.

Update 2:

IF you really do need a sorted structure .. there are also some java classes that do maintain the insertion order, such as a LinkedHashMap. Since it implements java.util.Map it can be used like a CF structure (in most ways).

<cfset var returnStruct = createObject("java", "java.util.LinkedHashMap").init()>

Runnable Example on trycf.com

NB: While having access to java objects is great, a common mistake is to forget that java is strongly typed, unlike CF. There are often subtle nuances that can easily bite you if you are not aware of them. So keep that in mind ... and be sure to read the API.