tracker issue : CF-3732993

Extracted from http://cfmlblog.adamcameron.me/2014/03/querycfc-queryexecute-have-good-feature.html:
{quote}
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.
{quote}

-- 
Adam

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3732993

External Customer Info:
External Company:  
External Customer Name: Adam Cameron.
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments: