I had a friend of mine, Dale Fraser ask me if there was a way to return a row from a query recordset as a structure. There are many query functions in ColdFusion but sadly there is not one that will just give you a single row from a recordset. We had a look at queryConvertForGrid() but it just gave back a query object. What we were looking for was a plain structure object.
Now the simplest way to acheive this would be to create a UDF and loop over each column and create a new structure to return, maybe something like this:
<cffunction name="queryRowToStruct" access="private" returntype="struct">
<cfargument name="query" type="query" required="true" />
<cfargument name="row" type="numeric" default="1" />
<cfset var local = {} />
<cfset local.result = {} />
<cfloop index="local.i" list="#query.columnList#">
<cfset local.result[local.i] = arguments.query[local.i][arguments.row] />
</cfloop>
<cfreturn local.result />
</cffunction>
Then i got to thinking, what if we can acheive the same result just using ColdFusion functions without having to do a loop. I thought what if we could somehow use the new implicit structure creation with {} to create a string and use Evaluate() to create our new sctructure. Well the string part was easy enough using regular expressions but where it fell over was trying to evaluate a string with {} in it.
Then i thought, "Hey! JSON! I can use the JSON functions to decode a JSON string!". So rather than try to evaluate the string and try to create my structure that way i could just create my JSON style string and use DESerialJSON() to create my structure. On a side note, I really cant understand why they called the function that. Array functions start with "array", structure functions start with "struct", spreadheet functions start with "spreadsheet", date functions start with "date", query functions start with "query" and so on. Why not call the JSON functions JSONEncode() and JSONDecode()? Instead they called them SerialJSON() and DESerialJSON() ....go figure!
So here we have one line of nested functions to extract a single row from a query and return it as a structure object instead of a query object.
<cfscript>
q = queryNew("");
queryAddColumn(q, "col1", listToArray("a,b,c,d,e,f"));
queryAddColumn(q, "col2", listToArray("g,h,i,j,k,l"));
function queryRowToStruct(query, row) {
return DESerializeJson("{#evaluate(DE("#REReplaceNoCase(q.columnList, '\b([^,]*)\b', '"\1" : "##IIF(row GT q.recordCount, DE(''''), ''query.\1[1]'')##"', 'ALL')#"))#}");
}
</cfscript>
<cfdump var="#queryRowToStruct(q, 2)#">
The process ends up returning a string that looks like this:
{"COL1" : "a","COL2" : "g"}
This is just a JSON string so we can just decode it with DESerializeJson() to create out ColdFusion structure. The key here i think is the use of the regular expression and using the backreference to inject into our JSON string.
I think this is pretty! What do you think?
3 comments - Posted by Steve Onnis at 11:59 PM - Categories: Coldfusion | General | Regular Expressions