Wanting script to work like tags (#1 in a series of "fool's errands")

G'day:
Ever since ColdFusion 9 implemented a jerry-built¹ way of making DB calls in CFScript via Query.cfc, people have been observing/complaining/hand-wringing that the way one passes the SQL string from the calling code to JDBC via CFML is much more elegant with the CFQUERY tag than it is with using Query.cfc. In case  the situation if not clear, here's an example:

<!--- tags --->
<cfquery name="records">
    SELECT        m.aColumn, m.anotherColumn, l.thirdColumn, l.lastColumn
    FROM        mainTable m
    INNER JOIN    lookupTable l
    ON            m.fk = l.pk
    WHERE        l.lastColumn = <cfqueryparam value="#myValue#">
    ORDER BY    m.aColumn, l.thirdColumn
</cfquery>

<cfscript>
// CFScript
queryService = new Query(datasource="dsn",sql="
    SELECT        m.aColumn, m.anotherColumn, l.thirdColumn, l.lastColumn
    FROM        mainTable m
    INNER JOIN    lookupTable l
    ON            m.fk = l.pk
    WHERE        l.lastColumn = ?
    ORDER BY    m.aColumn, l.thirdColumn
");
queryService.addParam(value=myValue);
records = queryService.execute().getResult();
</cfscript>

Well actually that's not at all bad (in fact... it's just fine). The problem comes when people are used to doing this:

<!--- tags --->
<cfquery name="records">
    SELECT        m.aColumn, m.anotherColumn, l.thirdColumn, l.lastColumn
    FROM        mainTable m
    INNER JOIN    lookupTable l
    ON            m.fk = l.pk
    <cfif doFilter>
    WHERE        l.lastColumn = <cfqueryparam value="#myValue#">
    </cfif>
    ORDER BY    m.aColumn, l.thirdColumn
</cfquery>

<cfscript>
// CFScript
queryService =new Query(datasource="dsn");
sql = "
    SELECT        m.aColumn, m.anotherColumn, l.thirdColumn, l.lastColumn
    FROM        mainTable m
    INNER JOIN    lookupTable l
    ON            m.fk = l.pk
";
if (doFilter){
    sql &= " WHERE        l.lastColumn = ?";
    queryService.addParam(value=myValue);
}
sql &= " ORDER BY    m.aColumn, l.thirdColumn";

queryService.setSql(sql);    // in CF10 this can be done inline in the statement below
records = queryService.execute().getResult();
</cfscript>

Update:
Ian pointed out a logic error I had in this code earlier in that I didn't have the addParam() call in the conditional block. I've fixed this. I still really don't think it actually detracts from the CFScript code too much, compared to the tags. Thanks for pointing the glitch out, Ian.

Compared to just having the string & logic inline with the SQL text, it's a bit more work to build the string piecemeal like that. Although - come on - not much more work. Some people seem to think that because the current CFScript approach isn't as straight forward as a <cfquery>, then somehow the approach needs to be changed so that the CFScript approach can handle a block of plain-text SQL & CFML logic somehow just like <cfquery> can.

To me this concern only makes sense if one is coming from the perspective of how tags in CFML work. Yes: the one benefit (and it's the only benefit, IMO) that tags have is that they work in a way that allows the mishmash of code and plain text. It's why Allaire came up with the notion of implementing tag-based syntax in CFML. Tags can work like this, and "working like this" is the differentiator between tags and script in CFML.

Unfortunately Allaire made a cock-up, historically, when they first came to add script-based syntax to CFML. I guess they added CFScript into CFML because they foresaw what a lot of CFML developers still don't see: tags mostly only make sense when the code is being mishmashed with mark-up or other text that's simply to be echoed, unaffected by any programming logic. Obviously this describes only a subset of the logic used to build the HTML document, so it makes sense to be able to write the logic-heavy-output-minimal code in a tidier syntax. Anyway, the motivations of CFScript aside, the cock-up wasn't CFScript itself, but the half-baked implementation of it Allaire initially made of it, and the perpetuation of this by Macromedia and - initially - Adobe. All three companies were madly adding tags to CFML, and neglecting CFScript. This was a poor approach, especially given some of the functionality being added really wasn't best-suited to be a tag anyhow. But it was a) implemented as a tag; and b) without a CFScript equivalent. The approach should generally be the opposite: add it to CFScript first, and then if there's any point in it being a tag, then create a tag wrapper for it too. But either way it was done, most functionality in CFML ought to have been done for both syntax choices.  It did actually make more sense to have more taggy stuff in the past, as there was a definite focus on speeding up the generation of mark-up, but that kind of peaked and started dropping off with... ooh, I dunno: CFMX? Most of what's gone into the language in the last decade was not mark-up-generation-oriented, I would think?

Anyway, during the development of CF9, Adobe finally seemed to have mostly twigged how CFML (tags and script, both) should have been implemented: CFScript needs to be able to perform any functionality that can be implemented via tags (within reason). Plus consideration need to be made add to whether new functionality even needs to have a tag implementation: this should only be true for functionality that needs to wrap other text or code. I think they're still getting the latter wrong. There's no way <cfimage> and <cfspreadsheet> really make sense as tags, after all. So the only time a new tag should be implemented in CFML is if that tag interacts with or formats vanilla text/mark-up. Which, given Rakshith had said (on Twitter, I can't give a reliable link as far as I know) Adobe are not going to do any more of this UI-widget stuff, should be "never". IE: there should never be any new tags going into CFML from now on. "Never" is a long time, so let's just say it should be a seldom occurrence.

So as Adobe have been playing this valiant game of catch-up getting CFScript up to snuff, we are left with this perception that when back-filling functionality into CFScript that has only thusfar existed in tags, it needs to somehow work the same as we're accustomed to it working in tags. This is a fallacious IMO. If back in CF-whatever (probably CF 1.0) when <cfquery> was added (and pretending for a sec CFScript existed at the same time), some querying functionality was added to CFScript at the same time, we'd never be having this "the CFScript version isn't the same as the tag version". We'd just be going "well obviously it doesn't work the same. They're not the same. But that tag syntax is cool though, innit?"

It boils down to there being one syntax when using tags... a syntax that fits in with a tag-based-coding-approach, another syntax that fits in with script-based-coding-approach. They're different. They're allowed to be different! By the very nature of the syntaxes being different, each coding approach will have strengths and weaknesses. And a weakness of CFScript is that it intrinsically doesn't deal with code that deals with swathes of unorganised plain text inline within the code. Live with it. On the other hand, it's better almost across the board in every other way. The only downside that CFScript has ever really had is that there was functionality missing from it; if the functionality gets covered, then that downside goes away.  Indeed given one can do this:

include "templateWithTagsInIt.cfm";

Most of the problems of tags and script interplaying is moot anyhow. I think if your code has a requirement to intermix the two syntaxes, you probably ought to be looking to refactor or re-engineer your code because you're more than likely doing something suboptimally.

Indeed - although I concede this might be retcon - when I started thinking about the specifics of this <cfquery> issue, I began to wonder... is it actually good to be able to mishmash CFML logic and SQL logic like that? I don't know that it is, actually. Any developer worth his salt these days will be using some sort of design pattern for separating out their M from their V from their C, and they've probably also got their DB calls separated out further still, into a DAO. What's part of the reason for this? To keep the various elements of the app decoupled from each other. We all see this as a good thing, yes? So it could be argued that the SQL statements could be abstracted away from the code that passes those SQL statements over to JDBC, eg:

<cfscript>
// CFScript
queryService = new Query(datasource="dsn", sql="/path/to/SQL.cfm");
queryService.addParam(value=myValue);
records = queryService.execute().getResult();
</cfscript>

I can see an argument for factoring that stuff well out of the way. I mean with the popularity of ORM tiers, people are really wanting to abstract the SQL as far the hell away from their code as possible. So I dunno why there's this fuss about need CFScript's approach to passing a string to another system. Because, at the end of the day, that's all that's going on.


Another factor that has possibly caused this anguish is that for the longest time CFScript was bloody useless for a lot of things, and one was always having to jump out of a script block to do something that could only be done in tags (like: a DB query!), so it became de facto to have tracts of code in which one was chopping and changing between coding styles. We've become used to being able to mishmash the syntaxes within close proximity to each other because we've had to. If we never had to have done this, I am certain people would not want to be starting to do it now.


If you prefer using a "text nested between delimiters (eg: tags)" approach... then use <cfquery>. If you just see it as a string & some params you need to pass to JDBC... I think the CFScript approach is already fine.

Personally - and I am speaking as a person not entirely convinced by the notion of ORM - I think I'd have both approaches. These days I would probably want to push as much data-processing logic right into the DB where it belongs, and then just call a proc to fish it out. For the odd occasion I need to do a quick "SELECT * FROM table" to have a shufti at what's in there, or the requirement is suitably one-off to not warrant a proc, I'd just use the CFScript approach, as it's the cleanest IMO. If some reason cropped up that I needed to dynamically generated the SQL string with a bunch of embedded CFML logic in it... I'll probably just line myself up against the nearest wall and shoot myself anyhow (because I'll be doing whatever it is wrong, in most situations). But if for some reason it's not a shooting offence, I'll probably put the logic in a separate file and then capture its output:

savecontent variable="sql" {
    include "theCodeToRenderTheSQL.cfm";
}

The pass that string to the query service. Or I'd just use string concatenation inline. or last... I'd resort to falling back to <cfquery>. I have nothing against <cfquery>, but it will never fit that well with the business logic I'm writing, which will all be in CFScript.

I think ColdFusion's approach to capturing the SQL is about right: it's a string, so expect a string. I think Railo's approach of being able to do writeOutput() calls within the query statement's "body" is godawful and should never have been developed. but each to their own, and obviously that's just my opinion. I don't expect anyone who likes that approach to change their opinion because there's is divergent from mine. And whilst I have the deepest respect for Justin Carter's abilities as a developer and his "out of the box" thinking with C4X, I think it's... it's... well I don't like it. I don't like it even more than I don't like Railo's writeOutput() option (to put things in perspective).

Righto.

--
Adam


1. that the solution to doing a query in CFScript has been implemented as a CFC (a poorly-written one) is an embarrassment to ColdFusion. Not your finest hour, Adobe. The solution should have been along the lines of this:

recordSet = query(
    sql            = "SELECT * etc"
    params        = arrayOfParams,    // as an array of structs with appropriate keys, or as created by a queryParam() function
    otherArgs    = etc
);

That would have fitted in with the rest of CFML.

As a CFC? You're joking.