Constructing a query with data in one fell swoop

G'day:
A coupla quick ones today.

An unglamorous but handy bit of functionality was slipped into ColdFusion 10. When using queryNew() to create a recordset, one can now as the data for it in the same statement. There's two formats for this:

<cfprocessingdirective pageEncoding= "UTF-8">
<cfscript>
    daysOfTheWeek = queryNew(
        "id,english,maori",
        "Integer,Varchar,Varchar" ,
        [
            [1, "Monday", "Rāhina" ],
            [2, "Tuesday", "Rātū" ],
            [3, "Wednesday", "Rāapa" ],
            [4, "Thursday", "Rāpare" ],
            [5, "Friday", "Rāmere" ],
            [6, "Saturday", "Rāhoroi" ],
            [7, "Sunday", "Rātapu" ]
        ]
    );
    writeDump(daysOfTheWeek );
    daysOfTheWeek = queryNew(
        "id,english,maori",
        "Integer,Varchar,Varchar" ,
        [
            {id=1, english ="Monday", maori ="Rāhina"} ,
            {id=2, english ="Tuesday", maori ="Rātū"} ,
            {id=3, english ="Wednesday", maori ="Rāapa"} ,
            {id=4, english ="Thursday", maori ="Rāpare"} ,
            {id=5, english ="Friday", maori ="Rāmere"} ,
            {id=6, english ="Saurday", maori ="Rāhoroi"} ,
            {id=7, english ="Sunday", maori ="Rātapu"}
        ]
    );
    writeDump(daysOfTheWeek );
</cfscript> 

One might question why have the far more verbose struct-based format as well as the array-based format, but it is handy in non-example code because one if probably gonna be more likely to have existing data in an array of structs than an array of arrays.  There's seldom reason to have a data struct that's an array of arrays in CF, I think.

Anyway: not the most exciting functionality, but jolly handy on occasion, so it's cool!  This is partially documented: they've put an example in, but missed the references to it in the syntax example, history and parameters sections of the page.


One situation where this could be handy for us lot in my office is for unit tests. Currently if err need to mock-up a recordset for test data, err fall back to the querySim() function that if included in MockBox. It works like this:

<cfprocessingdirective pageencoding="UTF-8">
<cfscript>
mockbox = createObject("mockbox.system.testing.MockBox").init();
daysOfTheWeek = mockbox.querySim("
    id,english,maori
    1|Monday|Rāhina
    2|Tuesday|Rātū
    3|Wednesday|Rāapa
    4|Thursday|Rāpare
    5|Friday|Rāmere
    6|Saturday|Rāhoroi
    7|Sunday|Rātapu
");
writeDump(daysOfTheWeek);
</cfscript>

That's OK I guess, but it's always struck me add bring a bit jerry-built though.

So, anyway, we could now use this new feature of CF10. Well: we could, if we were on CF10. We're not. And I dunno what the timeframe for it is. If it was up to me - it's not - it would not be until after a proper 10.0.1 updater comes out (I see that thing that is sometimes referred to as 10.0.1 - but sometimes not - that Adobe released a while back as a hotfix roll-up rather than an updater). And after I stop hearing about so many people having scaling issues with IIS. I'm not sure if CF10 was adequately load-tested, so I an apprehensive about it for production environments.

Anyway: we're on CF9 still, so that sucks.

One neat trick that OpenBD does to mitigate this shortfall in functionality - OpenBD is like CF9 in this regard: the new queryNew() syntax is not supported - was pointed out by Alan Williamson of OpenBD fame over on their google group:

<cfprocessingdirective pageencoding="UTF-8">
<cfscript>
    daysOfTheWeek = deserializeJson('
        {
            "COLUMNS"    : ["ID","ENGLISH","MAORI"],
            "DATA"        : [
                [1,"Monday","Rāhina"],
                [2,"Tuesday","Rātū"],
                [3,"Wednesday","Rāapa"],
                [4,"Thursday","Rāpare"],
                [5,"Friday","Rāmere"],
                [6,"Saturday","Rāhoroi"],
                [7,"Sunday","Rātapu"]
            ]
        }
    ');
    writeDump(daysOfTheWeek);
</cfscript>

This deserialises back into a query (on CF and Railo, it deserialises as a struct, unfortunately). I think that's actually a good bit of lateral thinking!  I wish it worked on CF9.

That's all I wanted to say.

Stop press:
I actually had this entire thing drafted (during my commute on the Tube) to say that the deserializeJson() technique worked on ColdFusion as well, before testing it and finding out that it didn't (so I hastily re-worded bits of it ;-).

To me this is a bug in ColdFusion's serialisation/deserialisation of JSON: there should be a reverseable way of taking a recordset to/from JSON.  What do you think?

Stop press (again):
In case you missed Brian's comment below, contrary to my lamentations above both CF and Railo can be made to deserialise a previously-serialised query back into a query as opposed to a struct.  As is documented, one can use this syntax:

deserializeJson(JSONVar[, strictMapping])

Where specifying strictMapping as false will result in the query being reconstructed.

Tested on CF9 and Railo 4.0.0.013.

Cool!

Righto.

--
Adam