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.