Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
Recently I needed a way to convert a single query row into a structure that I could pass into a function. Google didnt turn up anything useful on the adobe site so I decided to write something myself. This is what I came up with.
Note: This code was written and tested in CF9 so I cant guarantee it will work on older versions of CF.
Convert A Single Query Row Into A Structure
This turned out to be very simple to implement. I simply looped over the column list and extracted the values of that column name from the given row number.
<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>
Convert A Query Object Into An Array Of Structures
This is a simple expansion of the previous function. This time I loop over the entire query and build up an array of strucures.
<cffunction name="rowToStructArray" access="public" returntype="array" output="false">
<cfargument name="queryObj" type="query" required="true" />
<cfset var returnArray = arrayNew(1)>
<cfset var rowStruct = structNew()>
<cfset var colname = "">
<cfloop query="arguments.queryObj">
<cfset rowStruct = structNew()>
<cfloop list="#arguments.queryObj.columnList#" index="colname">
<cfset "rowStruct.#colname#" = arguments.queryObj[colname][arguments.queryObj.currentRow]>
</cfloop>
<cfset arrayAppend(returnArray,rowStruct)>
</cfloop>
<cfreturn returnArray/>
</cffunction>
At the end of the day a change in requirements meant I did not need this code but I hope it might be helpful to somebody else.
Reader Comments
@phenotypical
Wednesday, November 16, 2011 at 1:35:12 PM Coordinated Universal Time
Not to plug Open BlueDragon, but they've got a function for that: http://www.openbd.org/manual/?/function/queryrowstruct
@sneiland
Wednesday, November 16, 2011 at 10:20:55 PM Coordinated Universal Time
Jason: Not surprised at that. Would not be surprised either if Railo had something similar.
Gota love open source cfml!
@lightningwhelk
Wednesday, January 11, 2012 at 2:39:03 PM Coordinated Universal Time
You might want to check out some posts on Ben Nadel's blog related to this topic:
Converting a Struct to a Query (And More Java)
Converting a Query to A Struct
Converting a Query to an Array
Monday, June 24, 2013 at 6:34:00 PM Coordinated Universal Time
Found this while googling similar as I have recently upped from CF8 to CF10.
Thought I would mention that you can use the new for-in syntax even if your query is one row to convert it to a struct to do what you need as well.