Tuesday, October 8, 2013

Railo CFML - Get Query Data by full ROW

Methods previously described for obtaining query data by column name and row number (variables.myquery["columnName"][rowNumber]) are correct, but not convenient for getting a full row of query data.

I'm running Railo 4.1.  And this is a cool solution. Too bad this can't be done the way we would want outright to get a full row of data, but the following method allows us to get what we want through a few hoops.

When you serializeJSON(variables.myquery) it changes the query to a JSON formatted cfml struct object with two items, "Columns" and "Data". Both of these are arrays of data. The "data" array is a two-dimensional array for rows and then columnar data.

The issue is that now we have an unusable string. Then if we re-serialize it it's NOT a query but rather usable regular struct in the format described above.

Assume we already have a query variable named 'variables.myquery'. Then look at the following code:

<cfset variables.myqueryobj = deserializeJSON(serializeJSON(variables.myquery)) />

Now you get the two dimensional array by getting this:
<cfset variables.allrowsarray = variables.myqueryobj.data />

And you get one query row array by getting this:
<cfset variables.allrowsarray = variables.myqueryobj.data[1] />
OR the last row this way:
<cfset variables.allrowsarray = variables.myqueryobj.data[variables.myquery.recordCount] />

And you can get individual column values by column order number iteration:
<cfset variables.allrowsarray = variables.myqueryobj.data[1][1] />

Now this might be slow and possibly unwise with large query results, but this is a cool solution nonetheless.

I haven't tested this in older version of railo or Adobe CFML. Let me know if this works for you.

No comments: