ColdFusion Query Column Order: Did You Know?

Now first let me say that I've only seen this behavior on ColdFusion 9.01 with Cumulative Hotfix 2. My ColdFusion 10 VM blew up this morning, and I haven't had 7 or 8 for a while now, so someone else might have to verify this behavior on those platforms for me.

This morning I was testing out a new feature of Ext JS 4.1, and ran into something interesting. One of the reasons that I wrote CFQueryReader, as well as serializeCFJSON, was because of the way that ColdFusion handles it's JSON conversions of it's native query object.

view plain print about
1{
2    "COLUMNS":["ID","NAME","EMAIL"],
3    "DATA":[
4        [1,"Ed Spencer","ed@sencha.com"],
5        [2,"Abe Elias","abe@sencha.com"],
6        [3,"Cutter","no@address.giv"]
7    ]
8}

I've written before, about how Adobe's dataset serialization is much trimmer, but many client-side libraries and plugins expect a more standardized format.

view plain print about
1{
2 "users": [
3 {
4 "id": 1,
5 "name": "Ed Spencer",
6 "email": "ed@sencha.com"
7 },
8 {
9 "id": 2,
10 "name": "Abe Elias",
11 "email": "abe@sencha.com"
12 }
13 ]
14}

It's not too difficult for us, client-side, to map the column to the proper data position in the record array. In fact, that's basically what CFQueryReader and serializeCFJSON both do. But why is it even necessary? Most of these client-side libraries allow us to write mappings in configurations anyway, so why write something else to do it?

Well, there's two reasons really. First of all, the client-side developer may not have that much insight into the server-side developer's code (they can be different people), and would probably be unaware of API changes. The second reason? Well, if you've used ColdFusion for a while, you might know that there was a time when you could not depend on the column order always being the same. At one point, the columns were returned in alphabetical order. This would make it really hard to figure the position out prior to run time, unless you have intimate knowledge of every change to your API as it happens.

It appears that this isn't as much of a problem today. Kind of. I'll run this query:

view plain print about
1LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
2        views,
3        posted,
4        title
5FROM    tblblogentries
6WHERE 0 = 0";
7LOCAL.q = new Query(sql = LOCAL.sql, datasource = VARIABLES.dsn);
8LOCAL.retVal.getEntries = LOCAL.q.execute().getResult();

If you dump the results to the page, you'll see the following:

Now, server-side, ever query comes with a variable we can use to reference the columns used in a query. I'll use ColdFusion's ArrayToList(LOCAL.q.columnList) method to dump the query's columnList property to the page:

Look closely and you'll see that both appear to be in alphabetical order. The query dump and the column list showed them in alphabetical order. That said, we then look at the JSON return:

Wait a minute? Now the columns appear in the same order that they are in the query. That's a change.Let's change the column order in the query, and see if it changes anything.

view plain print about
1LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
2        title,
3        posted,
4        views
5FROM    tblblogentries
6WHERE 0 = 0";

Notice that, again, the column order matches that of the order it was used in the query. Running a cfdump of the query, and it's columnList, still show alphabetical order. Why is it different? Why does this matter? Well, I'm going to show you that in a follow-up post (on Ext JS 4.1 and ColdFusion), but in the mean time it's a good thing to know, that the JSON returnFormat will give you query column orders identical to that used in the query. And, as I said above, somebody please verify this for me on other versions of the platform.

JQuery Plugin: serializeCFJSON

Quick note about my serializeCFJSON project out on GitHub. I wrote this quick JQuery plugin to convert ColdFusion's JSON representation of it's native query objects. ColdFusion represents datasets in a trim manner, as an object containing two arrays: One, an array of column names, and the other, an array of arrays, each representing one record of the set. Most representations of recordsets are an array of objects, each record represented as a collection of name/value pairs. ColdFusion's representation is much smaller, removing a lot of unnecessary duplication, but many pre-built frameworks and plugins look for the name/value pair objects.

What the plugin does is convert ColdFusion's representation into the more standard form. Ajax transfer is unaffected, as the trimmer format is still being passed. What we get is some very minor client-side overhead in the creation of a new object. What's more, the plugin recursively searches through a JSON object and converts any ColdFusion query that it finds. So, if you nest your query inside a larger object, the plugin will still convert it for you.

I put up a demo in the Projects menu, at the top of this site. This is a refactor of my Grouped jqGrid example, using the plugin for the data conversion. The full sample code for the demo can be found in the GitHub repository.

Take her out for a spin, and let me know what you think.

ColdFusion JSON Serialization Changes

Because I've had to search on this three times in the last year, I thought it'd be a good idea to document this here, for myself and others. There were a number of important changes that can trip you up from version to version and, if you know which version the changes came in it can be beneficial.

First is a change that affects JSON output, though it's not specifically about JSON. The ColdFusion 9.01 updater (the current base install, at the time of this post) addressed Bug 82980 by removing ColdFusion Debug Output from direct CFC requests. This was only important if you had debugging output turned on in the Administrator, but caused lots of issues when testing ajax applications in development environments that kept that setting on by default.

Unfortunately, Adobe made some changes to JSON serialization that were not only unpopular, but technically dead wrong. They were trying to resolve the treatment of numbers in JSON serialization. Numbers were being converted to floats (1 became 1.0, 12 became 12.0, and so forth), so Adobe changed that by converting all numbers into strings (11 became "11", 17.24 became "17.24"). This was wrong too, as numbers weren't numeric anymore. At the time it raised quite a stir.

They fixed this (Bug 83638) really quickly, releasingCumulative Hotfix 1 for ColdFusion 9.01, which fixed the issue of integers being converted to float and having them as numeric values (ie: [1,12,true,"this var",17.24]). Hurray! Except....

Enter the improperly titled Cumulative Hotfix 2 for ColdFusion 9.01. I say this because it really isn't 'cumulative'. Most things are straight, from a cumulative standpoint, with the seeming exception of the fix to JSON formatting. If you have not applied CHF 1 prior to installing CHF 2, then you will still have issues with JSON formatting.

Overall, Hotfix 2 is great, fixing many bugs. The install is crazy/scary, and must be followed to the letter to prevent major issues, but it's worth it. As long as you did apply CHF 1 first, that is. Thankfully the hotfix/upgrade install process is much better in ColdFusion 10.

Big thanks to Ray for helping me to realize that I wasn't crazy, and pointing me in the right directions for all of this info. Hopefully this helps someone down the line.