ColdFusion 11: queryExecute()

G'day:
No ifs or buts this time... Adobe dun good with a new feature in ColdFusion 11: queryExecute().

For years (up to and including ColdFusion 8), the biggest thing missing from CFScript, IMO, was the ability to make a DB call: a script equivalent of <cfquery>. We all pretty much wrote a wrapper UDF to deal with it.

In ColdFusion 9, Adobe reached what I consider their nadir of code quality in releasing a CFC to cover querying (and other things) in CFScript. This was a shitty implementation (go look at the code... it's CF101 level work, and shows an ignorance of OO too; it's awful code) of a shitty solution.

Fortunately they have now done themselves proud by putting a native solution into the language, and I'm very pleased to say I like how it works. It's easy and it makes sense.

Here's my test code:

numbers = queryExecute("
        SELECT        *
        FROM        words
        WHERE        type = :type
        ORDER BY    id
    ",
    {type={value="number", cfsqltype="CF_SQL_VARCHAR"}},
    {datasource="scratch_mssql"}
);
writeDump(numbers);

firstFour = queryExecute("
        SELECT        *
        FROM        numbers
        ORDER BY    id
    ",
    {},
    {
        dbtype    = "query",
        maxrows    = 4
    }
);
writeDump(firstFour);

colours = queryExecute("
        SELECT        *
        FROM        words
        WHERE        type = ?
        ORDER BY    id
    ",
    [{value="colour"}],
    {
        datasource    = "scratch_mssql"
    }
);
writeDump(colours);

Query execute takes three arguments:

• an SQL string
• a struct of named parameters, or an array of positional ones;
• a struct of other parameters.

And - as demonstrated - it works for normal DB queries and QoQ seamlessly. Cool.

Update:

Something Rob McDowall just asked:

@dacCfml are parameters 2/3 optional? like, can you just use a normal sql query? If not, i'll stick to my crazy multi-query enabled wrappers
— rmcdowall (@NXRK) February 21, 2014

I verified this and - provided one has the query's DSN defined in Application.cfc - one can get away with just the SQL string. However I was not able to get it to work with just the SQL string and the config struct and no params (array or struct). It's no real bother to pass an empty one of those in the second argument though.

Good question, Rob; and sorry I did not think of it from the outset.

Some people will be drawing in a breath to whine that it's not as good as <cfquery> because they can't as easily mishmash their CFML logic with their SQL logic like <cfquery> will afford. In my opinion this was a good thing back in the 1990s (OK, early 2000s) when one quite happily just slapped all one's logic in the middle of one's CFML file; but I really think this is actually quite a shit approach to coding these days. One should not want to have SQL-building logic mixed in with one's CFML logic, just like one would not generally want to mix one's view logic in with the rest of the CFML logic. If you want to have a bunch of CFML logic to build yer SQL string... pull it out into another file:

savecontent variable="sql" {
    include "sql.cfm";
}
numbers = queryExecute(sql, params,    {datasource="scratch_mssql"});
writeDump(numbers);

<!--- sql.cfm --->
SELECT        *
FROM        words
<cfif structKeyExists(URL, "type")>
    WHERE        type = :type
    <cfset params = {type={value=URL.type, cfsqltype="CF_SQL_VARCHAR"}}>
<cfelse>
    <cfset params = {}>
</cfif>
ORDER BY    id

Job done. The SQL-building logic is in its own file (so like and SQL "view" if you like), and the CFML logic is in another. A good separation of code.

But if one really still wants SQL and CFML logic in the SQL and the rest of the CFML logic all mishmashed in one file... use the tags and be done with it. No-one's compelling you to use script code if you prefer tag code. But don't complain that script code is different from tag code because... well of course it is. It's supposed to be.

Anyway... well-implemented feature from Adobe, this one. Nice work.

--
Adam