ColdFusion 11: calling a stored procedure from script. And can we please stop wearing out our "c" and "f" keys?

G'day:
I'm not sure what's got into me today... I'm supposed to be completing a Backbone.js course @ Code School, but I keep finding things to look at in Railo / ColdFusion instead. This is the fourth article I've written this afternoon. And it's only 3pm.

I'm looking at two things in this article. Calling a stored procedure from CFScript, and how I really don't think our code needs to scream "cf" "cf" "cf" at us the whole time. We get it. It's a CFM file. It's got CFML in it. We don't also need every single line of code in it reminding us of this. But this is what we will have if Adobe get their way.


Firstly... stored procs. Here's a tag-based proc call:

<cfstoredproc procedure="uspGetColours" datasource="scratch_mssql" result="tags.result" clientinfo="true" returncode="true">
    <cfprocparam value="#URL.low#" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam value="#URL.high#" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="out" variable="tags.inclusiveCount" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="out" variable="tags.exclusiveCount" cfsqltype="CF_SQL_INTEGER">
    <cfprocresult resultset="1" name="tags.inclusive">
    <cfprocresult resultset="2" name="tags.exclusive">
</cfstoredproc>

And to contextualise this, this is the proc's code (this is like the second T-SQL proc I've ever written, and I needed to google the entire thing, so don't take this as a way to achieve anything sensible in T-SQL; it's simply good enough to fulfil my requirements on the CFML side of things):

ALTER PROCEDURE [dbo].[uspGetColours] 
    @low int, 
    @high int,
    @countInclusive int OUTPUT,
    @countExclusive int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT    id, en AS english, mi AS maori
    FROM    colours
    WHERE id BETWEEN @low AND @high
    
    SELECT @countInclusive = @@ROWCOUNT
    
    SELECT    id, en AS english, mi AS maori
    FROM    colours
    WHERE id NOT BETWEEN @low AND @high

    SELECT @countExclusive = @@ROWCOUNT
END

And the underlying data:

query
	EN	ID	MI
1	red	1	whero
2	orange	2	karaka
3	yellow	3	kowhai
4	green	4	karariki
5	blue	5	kikorangi
6	purple	6	tawatawa
7	pink	7	mawhero

So the proc takes a low/high ID value and returns the following:

• the records within that range;
• how many of those there are;
• the records outwith that range;
• how many of those records there are.

It's very contrived, but it returns multiple recordsets and has multiple input and output parameters. It's about as complex as a proc call can get in CFML (barring adding more of the same).

Adobe have claimed they've finally got 100% of CFML's functionality directly usable from CFScript. They already have a StoredProc.cfc lying around which was added in CF9, but that was a shit way about going about adding functionality to script (if you read this blog with any regularity... of even just today... you'll know my opinion of these CFCs).

For completeness, here's an example of it in action (this is the same proc call as above):

result =  new StoredProc(
    procedure        = "uspGetColours",
    datasource        = "scratch_mssql",
    result            = "anyOldShitYouLike", // seriously. It doesn't matter what value this has
    fetchclientinfo    = true,
    returncode        = true,
    parameters        = [
        {value=URL.low, cfsqltype="CF_SQL_INTEGER"},
        {value=URL.high, cfsqltype="CF_SQL_INTEGER"},
        {type="out", variable="inclusiveCount", cfsqltype="CF_SQL_INTEGER"},
        {type="out", variable="exclusiveCount", cfsqltype="CF_SQL_INTEGER"}
    ],
    procResults        = [
        {resultset=1, name="inclusive"},
        {resultset=2, name="exclusive"}
    ]
).execute();


writeDump(var=[
        result.getPrefix(),
        result.getProcResultSets(),
        result.getProcOutVariables()
]);

This results in:

array
1	struct CACHED false CLIENTINFO struct AccountingInfo [empty string] ApplicationName [empty string] ClientHostName [empty string] ClientUser [empty string] ProgramID [empty string] EXECUTIONTIME 2 STATUSCODE 0
2	struct exclusive query ENGLISH ID MAORI 1 red 1 whero 2 orange 2 karaka 3 purple 6 tawatawa 4 pink 7 mawhero inclusive query ENGLISH ID MAORI 1 yellow 3 kowhai 2 green 4 karariki 3 blue 5 kikorangi
3	struct exclusiveCount 4 inclusiveCount 3

That's all right I guess. It is a bit odd that I have to pass the proc results in to the call, but hey.

So as to make less work for themselves at the expense of CFML and the CFML developers, instead of taking a case-by-case approach to implementing previously tag-only functionality in a scriptable fashion, Adobe have just taken a generic approach to solving the problem. And it's awful.

Here's the new generic CFScript approach to a stored proc call:

cfstoredproc(procedure="uspGetColours", datasource="scratch_mssql",result="script.result", fetchclientinfo=true, returncode=true) {
    cfprocparam(value=URL.low, cfsqltype="CF_SQL_INTEGER");
    cfprocparam(value=URL.high, cfsqltype="CF_SQL_INTEGER");
    cfprocparam(type="out", variable="script.inclusiveCount", cfsqltype="CF_SQL_INTEGER");
    cfprocparam(type="out", variable="script.exclusiveCount", cfsqltype="CF_SQL_INTEGER");

    cfprocresult(resultset=1, name="script.inclusive");
    cfprocresult(resultset=2, name="script.exclusive");
}

That is just repugnant. What awful bloody code. All Adobe have done is to go "right... the CFScript version of a tag is just the tag without the angle brackets. And if it's a nestable tag set (like with proc params and proc results), we'll put some curly braces around it. Oh, and make it kinda like a function, with parentheses and commas instead of space-delimited attributes".

What's worse (yes, it gets worse), is that this is contrary to the way Railo already set out to do the same thing. Their approach also lopped off the "cf" bit too. And doesn't have the parentheses or commas (I think).

But here's the thing. Simply chopping the angle brackets (and/or the "cf" tag prefix) does not result in a well-thought-out implementation of script functionality. The two approaches to coding syntax are quite different. So simply doing what amounts to be a global search and replace isn't an adequate solution.

It's just a lazy cop out.

And, FFS Adobe: do we need to start everything with "CF"? It makes sense in tags, but it makes no sense whatsoever in the context of script code.

Doing it properly

What Adobe should have done is to sit down with a given tag and go "right... what functionality does this tag fulfil? Right, now that we know that, let's forget about tag syntax, and come up with a way to implement that functionality in script". It's not an exercise in syntax translation, it's an exercise in functionality provision.

So what does a stored proc call do? It takes some inputs:

• the procedure name
• some parameters
• some options (datasource, etc)

And what does it do?

• returns recordset(s)
• returns metadata
• returns values in some of the parameters

Takes inputs and returns results? Sounds like a function to me.

options = {
    datasource        = "scratch_mssql",
    fetchclientinfo    = true,
    returncode        = true
};
params = [
    {value=URL.low, type="INTEGER"},
    {value=URL.high, type="INTEGER"},
    {type="out", variable="inclusiveCount", type="INTEGER"},
    {type="out", variable="exclusiveCount", type="INTEGER"}
];

result = executeProcedure("uspGetColours", params, options);

Where result would yield this:

struct
METADATA	struct CACHED false CLIENTINFO struct AccountingInfo [empty string] ApplicationName [empty string] ClientHostName [empty string] ClientUser [empty string] ProgramID [empty string] EXECUTIONTIME 3 STATUSCODE 0
OUTPARAMETERS	struct exclusiveCount 4 inclusiveCount 3
RECORDSETS	array 1 query ENGLISH ID MAORI 1 yellow 3 kowhai 2 green 4 karariki 3 blue 5 kikorangi 2 query ENGLISH ID MAORI 1 red 1 whero 2 orange 2 karaka 3 purple 6 tawatawa 4 pink 7 mawhero

That is a hell of a lot clearer than what Adobe have offered, and gets rid of an awful lot of boilerplate code that makes sense in tags, but is unnecessary in script code. It just gets to the point and does the work.

Adobe ought to shit-can all this generic CFScript stuff they've done, and revisit each piece of functionality that is currently implemented as tag-only, and then implement the functionality in a way sensible to be implemented in script. Which will generally be a function. They mustn't tackle this as a syntax issue, they need to tackle it as a functionality issue.

And, seriously, the current approach in CF11 beta needs to be binned and started again. Bad luck. You should have focused on doing the job properly, not quickly.

Bug raised: 3733001. (update: Adobe have closed that one, with "never fix". I've raised another one to just deal with executeProcedure(): 3791737).

Believe it or not... the idea for my fifth article of the day just popped into my head...

--
Adam