Query.cfc / queryExecute() have a good feature <cfquery> lacks

G'day:
<cfquery> has been one of the mainstays of CFML for longer than I have been aware of CFML as a language. I presume it existed in some way, shape, or form in Cold Fusion 1.0. Up until ColdFusion 9, there was no way to perform a DB query in CFScript, unless one was to write a wrapper to do so. And I think we all had, at some stage or another.

Along came ColdFusion 9. It was during the development of ColdFusion 9 that Adobe first made the promise to implement all tag functionality in a scriptable way (note: it's taken them until CF11 to actually follow through on this promise), and when the first attempt at providing DB querying support in CFScript. I think it is astounding that it took until version 9 of the language before such a fundamental piece of functionality was available in CFScript, but there you go.

They did a very poor job of implementing <cfquery> in CFScript. Instead of doing what would have been sensible - adding a queryExecute() function - they just followed the CFML community's lead and added a CFML-written wrapper function to wrap around <cfquery>: Query.cfc (yes, it's a CFC... it's not a Java implementation like the rest of CFML is). In an enterprise product this is nothing short of embarrassing in how amateurish it is (both in general approach, and even more so in the code within the CFC... go have a look: it's not encrypted). I did point this out to Adobe at the time, but their response was just a blank, uncomprehending stare.

In tags, to run a query one just needs to do this:

<cfquery name="colours" datasource="scratch_mssql">
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id  BETWEEN    <cfqueryparam value="#URL.low#">
                AND        <cfqueryparam value="#URL.high#">
</cfquery>

Via Query.cfc, Adobe's idea is one does this:

queryService = new Query();
queryService.setSql("
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id BETWEEN :low AND :high
");
queryService.setDataSource("scratch_mssql");
queryService.addParam(name="low", value=URL.low);
queryService.addParam(name="high", value=URL.high);
queryResult = queryService.execute();
coloursViaQueryCfc = queryResult.getResult();

Unbelievable: we've gone from one statement to seven.

Thankfully if one reads between the lines, one can get it down to something more manageable via judicious argument passing, and method chaining:

coloursViaQueryCfcShorthand = new Query(
    datasource    = "scratch_mssql",
    parameters    = [
        {name="low", value=URL.low},
        {name="high", value=URL.high}
    ],
    sql            = "
        SELECT  en AS english, mi AS maori
        FROM    colours
        WHERE   id BETWEEN :low AND :high
    "
).execute().getResult();

But - whilst it seems more like "proper code" to me - it still lacks the brevity of a <cfquery> call. It seems fundamentally irksome to me that something in script code is more verbose than its tag equivalent, but oh well.

Along comes ColdFusion 11 and we finally get the solution we should have had from the outset: queryExecute():

coloursViaQueryExecute = queryExecute("
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id BETWEEN :low AND :high
    ",
    {low=URL.low, high=URL.high},
    {datasource="scratch_mssql"}
);

One function call. three arguments. Not bad.

So we can just close the chapter of ColdFusion's history that is Query.cfc, and move on.

However in all of this, we've actually picked up a feature in the function-based solutions that <cfquery> itself doesn't have. One no longer needs to have a specific piece of functionality to pass a parameter (ie: there is no equivalent of <cfqueryparam> in these functions). One can simply put a placeholder in the SQL string. the placeholder can either be a named label as per these examples, or simply a ? for positional parameters (pass an array instead of a struct in this case).

I think this is a much nicer approach to passing parameters: it completely decouples the parameter value from the SQL string, which is how it should be. The whole idea of parameters is that they are passed separately to the SQL string. The SQL string is the instructions; the parameters are the values. The values should not be hard-coded in the instructions. That's just bad programming.

So what I would like to have back-ported into <cfquery> is this:

<cfset params = {
    low     = URL.low,
    high    = URL.high
}>
<cfquery name="colours" datasource="mssql_scratch" parameters="#params#">
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id BETWEEN :low AND :high
</cfquery>

Or for positional parameters:

<cfset params = [URL.low, URL.high]>
<cfquery name="colours" datasource="mssql_scratch" parameters="#params#">
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id BETWEEN ? AND ?
</cfquery>

The conceit here being that parameters are passed separately to the SQL, via a parameters attribute: same as with queryExecute().

I think this approach makes the <cfquery> statement much clearer, and closer to an SQL statement's intent.

Thoughts? I've raised an E/R for this anyhow. ColdFusion: 3732993. It seems there is already a partially implemented feature covering this in Railo: RAILO-2203.I must check it out (I'll report back...)

Update:

The ColdFusion ticket has been closed with a status of "Closed/Deferred/EnhancementRequired", with no further explanation from Adobe. Disappointing.

Update 2:

It's been reopened and marked for implementation in ColdFusion 12. Make sure to go vote for it.

--
Adam