CFML: Using Query.cfc doesn't have to be the drama Adobe wants it to be

G'day:
Yesterday / today I was talking to me mate Adam Tuttle about the drawbacks of ColdFusion's Query.cfc, and using it. Adam's blogged about this ("Script Queries Are Dead; Long Live Script Queries!"), and written a proof of concept of how it could be better done.

This got me thinking about how bad Query.cfc's intended usage needs to be.

I'll start by saying - "repeating", actually, as I will say this to anyone who will listen - what a shitty abomination of an implementation Query.cfc (and its ilk) is. It's the worst implementation of functionality ColdFusion has. It is the nadir of "capability" demonstrated by the Adobe ColdFusion Team. Whoever is responsible for it should be removed from the team, and never be allowed to interfere with our language ever again.

But anyway.

OK, so Adobe's idea of how we should query a database via CFScript is this:

queryService = new query(); 
queryService.setDatasource("cfdocexamples"); 
queryService.setName("GetParks"); 
queryService.setcachedwithin(CreateTimeSpan(0, 6, 0, 0)); 
queryService.addParam(name="state",value="MD",cfsqltype="cf_sql_varchar"); 
queryService.addParam(value="National Capital Region",cfsqltype="cf_sql_varchar"); 
result = queryService.execute(sql="SELECT PARKNAME, REGION, STATE FROM Parks WHERE STATE = :state and REGION = ? ORDER BY ParkName, State "); 
GetParks = result.getResult(); 

That's from the docs. I'm not making that up. No wonder people think it's clumsy. Compare the above to the equivalent <cfquery>:

<cfquery name="GetParks" datasource="cfdocexamples" cachedwithin="#createTimeSpan(0, 6, 0, 0)#">
    SELECT        PARKNAME, REGION, STATE
    FROM        Parks
    WHERE        STATE    = <cfqueryparam value="MD" cfsqltype="cf_sql_varchar">
    and            REGION    = <cfqueryparam value="National Capital Region" cfsqltype="cf_sql_varchar">
    ORDER BY    ParkName
</cfquery>

That's nice. That's one of the things that makes tags in CFML cool: wrapping text and processing it like that.

However the docs are kinda leading us astray a bit here. We don't actually need to do all that horsing around. Our script-based query could be as simple as this:

GetParks = new Query(
    datasource="cfdocexamples",
    cachedwithin=createTimeSpan(0, 6, 0, 0),
    sql    = "
        SELECT        PARKNAME, REGION, STATE
        FROM        Parks
        WHERE        STATE    = ?
        and            REGION    = ?
        ORDER BY    ParkName
    ",
    parameters    = [
        {value="MD", cfsqltype="cf_sql_varchar"},
        {value="National Capital Region", cfsqltype="cf_sql_varchar"}
    ]
).execute().getResult();

That's not so bad, is it? The trick here is that we can dispense with all that getter/setter horseshit that Adobe suggest, and just pass the argument values straight to the constructor. And one thing I only worked out tonight whilst prepping for this article... this includes the parameter values.

Another one that throws people (there are a bunch of independent blog articles on it), is running a QoQ using Query.cfc. This is just as easy. Here we go:

numbers = queryNew("id,en,mi", "integer,varchar,varchar", [
    [1,"one", "tahi"],
    [2,"two", "rua"],
    [3,"three", "toru"],
    [4,"four", "wha"]
]);

number = new Query(
    dbtype    = "query",
    numbers    = numbers,
    sql        = "
        SELECT    *
        FROM    numbers
        WHERE    id = :ID
    ",
    parameters    = [
        {name="ID", value=randRange(1,numbers.recordCount), cfsqltype="CF_SQL_INTEGER"}
    ]
).execute().getResult();

writeDump(var=variables);

So all we need to do is to pass the query/ies we wish to perform the QoQ on as arguments to the constructor, like everything else. Also note here I'm using named parameters instead. Just to demonstrate their usage.

So it's not so bad, is it?

Fortunately ColdFusion 11 is making Query.cfc pretty obsolete, as it's implementing a queryExecute() function which rolls all this into a single function. I've seen the code being demonstrated, but I haven't got the exact syntax to hand. Once 11 goes public beta, I'll have a look and write it up here.

Railo supports Query.cfc for the sake of cross compatibility, but their recommended way of doing a query in CFScript via is via the old tags-without-angle-brackets approach. Which is really crappy when one's doing a tag that processes text, as one needs to use writeOutput() around the string values:

query name="number" dbtype="query" {
    writeOutput("
        SELECT    *
        FROM    numbers
        WHERE    id = 
    ");
    queryparam value=randRange(1,numbers.recordCount) cfsqltype="CF_SQL_INTEGER";
}

This query is not too much of a shambles as I've just got the one parameter at the end of the SQL statement. But imagine if I had a mix of SQL string and params , I'd need to be jumping in and out of writeOutput() statements. Bleah. I actually think ColdFusion's approach is better here. Other than it requiring a CFC instead of a native Java solution.

At CFCamp - whilst Rakshith was presenting about it, Micha from Railo said he implemented queryExecute() right then and there... this sounded cool a the time, but I don't see any evidence of it having gone into the language. Maybe Micha just thought he wrote the proof of concept, not that he was actually putting it into Railo.

Update:

Micha has replied, below, and pointed me in the direction of the posting he made to the Google Group with the code for queryExecute() for Railo. And here it is again:

function QueryExecute(required string sql, any params, any datasource){
    var args={};
    if(!isNull(params)) args.params=arguments.params;
    if(!isNull(datasource)) args.datasource=arguments.datasource;
    
    query name="local.rtn" attributeCollection="#args#" {
        echo(sql);
    }
    return rtn;
}

Test cases:

dump(queryExecute(sql:"SHOW tables where Tables_in_test = ?",params:['cf_session_data'],datasource:"mysql"));

dump(queryExecute(sql:"SHOW tables where Tables_in_test = :susi",params:{susi:'cf_session_data'},datasource:"mysql"));

So that's cool. I was under the impression he'd actually implemented the full-on Java implementation, but this is a nice enough proof of concept. And as he says in his comment, because Railo is developer-extensible, one can add this UDF to a server's CFML dialect by popping it into the <server-context>/library/ directory. Not bad!

Anyway, thanks to Adam Tuttle for the inspiration into looking at Query.cfc, so I could work out that one can just pass those parameters straight into the constructor.

Righto.

--
Adam