Thursday 24 January 2013

Bring back named params for <cfprocparam>

G'day
I forgot I was annoyed about this, until I was just reminded about it via Twitter & StackOverflow (no, this is not another gripe about S/O ;-).

Back in the good-old pre-CFMX days, <cfprocparam> used to support named parameters via the dbvarname attribute, eg:


<cfstoredproc procedure="foo_proc" dataSource="MY_SYBASE_TEST" username="sa" password="" dbServer="scup" dbName="pubs2" returnCode="Yes" debug="Yes">
    <!--- cfprocresult tags --->
    <cfprocresult name=RS1>
    <cfprocresult name=RS3 resultSet=3>
    <!--- cfprocparam tags --->
    <cfprocparam type="IN" CFSQLType=CF_SQL_INTEGER value="1" dbVarName=@param1>
    <cfprocparam type="OUT" CFSQLType=CF_SQL_DATE variable=FOO dbVarName=@param2>
    <!--- Close the cfstoredproc tag --->
</cfstoredproc>

(this is courtesy of the CF5 docs... I like the way it has comments that state the bleeding obvious: "close the cfstoredproc tag" for goodness sake!!).

In CFMX, this very useful feature was dropped, as explained in the docs for <cfprocparam>:

Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion uses JDBC 2.2 and does not support named parameters. This is deprecated.

Aside:
Adobe, if something is deprecated then it's supposed to still function as it did before, you're simply flagging that it might be obsoleted and removed in future. What you mean here is that the functionality has been removed, but code which continues to try to call the functionality won't actually error. This is possibly the worst approach to backwards compatibility you could employ, btw.

So what this means is that for the last ten years, we've only been able to called procs with positional arguments. Neat. Actually they did reappear in CFMX 7.0.1, but were removed in CFMX7.0.2 (or something like that... it might have been 7.0 and 7.0.1... I dunno).

Now, whilst JDBC 2.2 indeed didn't support named params, JDBC 3.0 did support them, and JDBC 3.0 was released a year before CFMX was. That's interesting isn't it? Still: there's no point in dwelling in the past is there?

Let's roll forward to now. We're up to JDBC 4.1 (only 4.0 with CF10, but still: not 3.0 and certainly not 2.2).  So it's about time this functionality was added back in.

I had a conversation about this with an Adobe engineer a few years ago. At the time they said that the reason why they didn't put the functionality back in is because not all DBs supported named params via JDBC. This sounds a bit outlandish, but I took their word for it. It's still a bad rationale. I checked the three platforms I cared about at the time: SQL Server, Oracle and MySQL, and they all supported 'em fine. I would say that would cover 80-90% of all situations (that's a number I plucked out of thin air, but you get my drift). Wouldn't it be better to cater to all those situations - most people - and just advise the people using different DBs - the ones that don't happen to support named params - that it doesn't work for them. That'd be the best approach, yeah? I articulated this notion, but it didn't get any traction (this was during development of CF9, I think, so right when something could be done about it).

There are a number of tickets in the bugbase with people asking for this functionality. Here's a couple: 3114260, 3035825; the latter marked as a duplicate of 3035675, which doesn't exist. Oh, they're both closed. What pisses me off is that in the old system there's a ticket 72534 which covers the same ground and is still open, but this seemingly never migrated to the new system.

OK, so CF11 is in the pipeline. Time to get this revisited, I think. I'm going to raise yet another ticket to get this looked at. If you think it's a good idea, please go vote for it. This will help Adobe decide how important it is, during bug triage. Please don't vote for it "just because" (~ I mentioned it): only vote if you think the functionality should be added in. I'm not attempting to ballot-stuff here. The ticket is 3489160.

Oh... I haven't checked what Railo does with this... I'll go ask them shortly, and see what they think.

Cheers.

--
Adam