tracker issue : CF-3865064

This was working correctly in all versions of 9, 10 & 11 up until update 3.

Comment by External U.

I have also upgraded to Update 3 and got the same error... Was working fine with Update 2.

Comment by External U.

I am also facing this error with Update 3.

Vote by External U.

Hello Guys, Can you tell us which database you are using with ColdFusion? If you are using Microsoft SQL Server, then you need to prepend '@' character for every value of dbvarname attribute. For example: dbvarname="@var1"

Comment by Nimit S.

Using both MS SQL Server and Oracle. The calls to the stored procedures have never required the '@' to be prepended in the past for these calls to work.

Comment by External U.

By default, we used to ignore dbvarname attribute in cfprocparam tag. In ColdFusion 11, you can use dbvarname, by enabling JVM flag "-Dcoldfusion.dbvarDependants={DSN}. But now with ColdFusion 11 Update 3, dbvarname attribute is enabled by default. We need not use JVM flag to enable it. For MS SQL Server, we need to prepend it with '@' and For Oracle we need to use ':' Can you please try and see if it works?

Comment by Nimit S.

My JVM flags have never included "-Dcoldfusion.dbvarDependants={DSN}". My JVM flags are as follows: -server -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005 -XX:MaxPermSize=192m -XX:NewRatio=4 -XX:SurvivorRatio=8 -XX:+UseCompressedOops -Xss256k -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+DisableExplicitGC -XX:+UseCMSInitiatingOccupancyOnly -XX:+CMSClassUnloadingEnabled -XX:+CMSScavengeBeforeRemark -XX:CMSInitiatingOccupancyFraction=68 -Xbatch -Dcoldfusion.home={application.home} -Dorg.eclipse.jetty.util.log.class=org.eclipse.jetty.util.log.JavaUtilLog -Duser.language=en -Dcoldfusion.rootDir={application.home} -Dcoldfusion.libPath={application.home}/lib -Dorg.apache.coyote.USE_CUSTOM_STATUS_MSG_IN_HEADER=true -Dcoldfusion.jsafe.defaultalgo=FIPS186Random I will attempt the workaround you have suggested as soon as I can get some of the immediate fires put out. What you are telling me, though, is that I am going to have to retrofit all of my existing (and, up until the installation of update 3, working) code with checks to see what type of database I am connecting to in order to properly name the parameters, and then add additional code to deal with the alternately named return variables. Across our entire product line and client base. All over something that was changed by Adobe without notice of deprecation.

Comment by External U.

Prepending the "@" to the dbvarnames returned the results expected. I still cannot recommend my clients install update 3 because it breaks our code.

Comment by External U.

Can you please explain, Nimit, why if SQL Server and Oracle NEED special prefixes, and you KNOW this, why the CF code doesn't prepend them!??! It's unacceptable you expect everyone to change their code here. My first reaction is "what were you thinking?" my second reaction is "and what about this backwards compatibility thing you always trot out when it's convenient *for you*"? However... an explanation might allay those initial reactions...?

Comment by External U.

Needs fixing on the CF end, not on the client code end.

Vote by External U.

We've basically retained the same behavior as was applicable earlier when this attribute was supported - so there is no backward compatibility issue per se. Since this attribute was being ignored in versions 7.0.2 and later, and has now been fixed, it might seem like a backward compat issue in cases where the code was using invalid value for the dbvarname attribute. FWIW, this attribute was supported till v5.0 and then later in 701 before it was again removed in 702 - in all cases simply because not all drivers were supporting it. The documentation examples clearly mention using @ (for MS SQL Server) or its equivalent for other databases where named parameters are supported.

Comment by Nimit S.

Ah, OK... is the issue not with <cfprocresult> (as reported) suddenly requiring a change, it's just that CF is doing the usual thing where DB errors are reported as happening on the second to last line of the code block that hits the DB? If it's with the <cfprocparam> calls, fair enough if those lines are invalid for the DB platform concerned (I've confirmed / reminded myself that SQL Server always used to need the "@" "back in the olden days" too. Basically, then, this is a case of the code in question was incorrect, but the user was getting away with it because the DBVARNAME was being ignored. Now that it's not being ignored, the code actually has to be legit ;-). Fair enough, In that case. Whilst I've got you: it sounds like it's a syntax error from the CF parser / compiler though. Shouldn't [whatever the value is] be passed to the driver, and simply let a runtime JDBC or DB error bubble back? This could be a vagary of how this ticket was raised, perhaps, and this is precisely what IS happening? I don't think CF should be validating these values though. Cheers Nimit.

Comment by External U.

Hi Guys, It seems CF has completely ignored the issue.! I would call this as a Bug Created in Updater 11.03 by CF and asking all developer to change their code to match. We have been using the cfProcParam since the beginning of the time with CF Version 5, Even at the time it was not with Adobe :) If CF is not going to FIX this...IT means they are expecting people like us to FIX their thousands of files.!!!! Can someone from CF Justify this baseless bizarre behaviour, Which they call it a FIX...where I am pretty sure any sensible developer community will call this a BUG.! Cheers

Comment by External U.

I think there's a chance it's you who's missing the point, Johnny. As far as I can tell, the code example above is invalid, and always *would have been* invalid. The dbvarname values did, indeed, always needed to be prefixed appropriately (eg: with an @). So that code would not have worked even back on CF5. dbvarname was obsoleted and ignored for versions 6->11 (except for a brief reappearance on 7.0.1), so if one had invalid code, it would not have mattered. But the code was still invalid throughout that time. Now dbvarname works again. So now the invalidity of the code above is causing a problem. You can't really legitimately blame Adobe for invalid code. And, indeed, the tag is working exactly as one would expect when processing invalid inputs. Garbage in: garbage out. If you can demonstrate that code WORKING on CF5, then you have a very small point. But even then, on all versions of CF between CF5 and this updater, that is actually invalid code, so I think that pretty much negates the very small point. -- Adam

Comment by External U.

Dear Adam, Do you work for Adobe? If yes, Probably the best person we should be communicating with. I believe this BUG would really created a massive setback for lot more people also. If we analyse the whole matter technically, <cfprocparam> allowed a attribute called "dbVarName" and it existed since the beginning of CF (Even ver 4.5 and all along with Alliare and Macromedia). Its been used all across our Application and million more across the Globe. Now, Fact is that CF was not checking and processing this attribute at all at the backend...as very clearly mentioned in all previous documentation states that "It is deprecated". But... with Update 3 of CF11...(I repeat....Only in Update 3), It has been added again, which was deprecated....started validating this with the prefix char of ":" for Oracle Database. (How would a developer expect this????) This means CF11 is not backward compatible with any previous version at all... And most importantly, No documentation in the past has mentioned...how this attribute should be used as it is changed now...!! Is there anyone from Adobe can explain....What should people do.... SHOULD THEY CHANGE ALL THOSE THOUSANDS OF FILES? or IDEALLY CF SHOULD ALLOW THIS BACKWARD COMPATIBILTY I really would prefer to hear from you Adam on this question. Thanks...

Comment by External U.

No. I don't work for Adobe. I cannot vouch for Oracle, but for SQL Server dbvarname - as far as I can tell - has *always* required the variable prefix. This is not new. That it has been *ignored* for a few versions of CF is neither here nor there: this is not licence to go ahead and write invalid code. Now I'm assuming Oracle is that same... when dbvarname was a supported attribute, then the variable names needed the prefix. If your code was valid, then we'd not be having this conversation. The issue is your code *isn't* valid. So it's a case of "more fool you", in my books. That said: SOMEONE FROM ADOBE needs to verify that what they have implemented here is merely a restoration of the same behaviour that dbvarname had in CF5. If it is: you have no case whatsoever. You've just been caught out with bung code. Even if not... I still think the fault lies mostly with you for having a reliance on obsolete code being ignored in a specific way. Having shoddy code is more likely to give you compatibility issues than not having shoddy code. I think any reasonable person here would be going "dammit... shouldn't've done that". Also bear in mind it's been FOURTEEN YEARS since dbvarname was last a valid attribute on <cfprocparam>. You've had plenty of time to remove it. I also think that's a reasonable length of time for Adobe to conclude "no-one can reasonably still expect for us to support the pre-obsolescence implementation of that attribute".

Comment by External U.

Hi Adam, It pretty simple, ORACLE Never has any prefix at all. Even no documentation has ever mentioned about this prefix also.! Even CF11 Documentation, does not mention this that Oracle would need a separate Prefix.! CF11 already has messed up heavily with Charting, And this one more mess...If they really keep doing like this..What is the trustworthiness for backward compatibility. I believe you have voted to be fixed to be done from CF and now its very different :) I am really waiting for someone from CF to look in to this...And suddenly why updater 3...This new massive change was done...!!! Cheers, Johnny

Comment by External U.

This is a CF issue, Suddenly Update3 has screwed up this. What is Adobe Fixing this????

Vote by External U.

We are also having the same problem. CF needs to fix this, it has been working fine till the update 3 comes along. And I agree, auto prepend of DB specific characters will solve the issue.

Vote by External U.

I agree with Johnny here. we are having the same issue of changing huge number of files because of this update 3. it has been working for so long without any issue, As far as CF documentation is concerned, there is no such clear explanation / example of using dbvarname attribute with prepending different characters (i.e. :) for different databases (i.e Oracle) and some versions of CF has no such atribute at all. And now all of a sudden CF wakes up and adds and validates dbvarname attribute which breaks all of the existing code. This needs to be taken care from CF side, atleast, should take care of extra character that needs to be added. Thanks

Comment by External U.

Well, my two cents here. We had CFSTOREDPROC code against MS SQL that had: <cfstoredproc procedure="sp_addlogin" <cfprocparam type="in" dbvarname = "@loginname" ... In CF9 all fine. In CF11 update 3, throws an SQL exception because sp_addlogin happens to only have a single 'n' in the paramater name, so something has changed to make CF care.

Comment by External U.

Sigh. Yes. Because you were using a tag attribute which was meaningless to CF, and had been meaningless since (now confirmed) *PRIOR* to CF5, over a decade ago. Basically your code was wrong, but this did not cause an issue because CF itself was not using that attribute (this is completely legit behaviour in CF: to ignore cruft). Now Adobe have decided to use that attribute, and your bung code is... well... BUNG. So it errors. I really don't see what's hard to understand about that. If one writes shit code, one can't blame the vendor if eventually you get caught out. [despair]

Comment by External U.

Maybe reusing the previous attribute name "dbvarname" was a little too risky from a backward compatibility perspective (since it seems so many people were using it either without realizing it was being ignored or just flat out incorrectly). I'm not saying I agree with them, but their opinions have to be considered. That said, what if "dbvarname" was left dead and buried, and a new "sqlvarname" attribute was introduced for this purpose?

Comment by External U.

Somethig meaningless for a decade should remain meaningless even after a decade. you cant make it meaningful all of a sudden. It would be better to add a new attribute instead of dbvarname. And keep it meaningless as it used be. There was no mention of this attribute in previous versions. CF would have thrown error then instead of ignoring it that way forcing legitimate code. People might have been using it for references for their sql parametes.. CF should have caught that by then not now.

Comment by External U.

What Carl has mentioned perfect sense.!!! Changing the behaviour of the deprecated attribute "dbVarName" is the main reason. I would expect Adobe realizes the impact and pain people has to go through and act on it immediately for a FIX, Which would never cause any issue.! Lets hope new Year brings some good news, At least we have a hope of using CF going ahead.!

Comment by External U.

Hi Nimit, I do realize Update 3 simply restored the deprecated behavior, but I also would like to echo Adam's statement/question: "why if SQL Server and Oracle NEED special prefixes, and you KNOW this, why the CF code doesn't prepend them". This reminds me of when cfquery, for INSERT queries, returned a db-specific variable name containing the automatically-generated ID. Later, CF simplified this and supported all databases w/ a "result_name.GENERATEDKEY" variable. Can't CF offer the same simplicity for these parameter prefixes (i.e. simply auto-prepend the db-specific prefix if it was omitted)? Thanks!, -Aaron

Comment by External U.

+1 - I realize Update 3 simply reinstated the deprecated behavior. But CF should keep-things-simple here and simply auto-prepend the db-specific prefix if it was omitted. Can this ticket please be re-opened and treated as an Enhancement Request to auto-prepend the prefix?

Vote by External U.

Yeah, I agree with automatically adding the appropriate prefix!

Vote by External U.

@Adi86 & @Johnny-007, Again, to echo Adam's sentiments, it was documented back in CF5 days. It was apparently deprecated during the switchover to Java-based ColdFusion (CF 6). Macromedia didn't carry that documentation forward for subsequent versions. I'm not sure you can fault Adobe for not having intimate knowledge of the behavior of an old version of a product they essentially rewrote from scratch. That said, relying on undocumented behavior (or obsolescence) is nobody's fault but the person who writes that code, namely you guys (or if you inherited that code, the previous guy). I don't think it's reasonable to expect Adobe to know (or guess) that you are using stuff that has been obsolete for over a decade. That they finally brought the previous functionality back (and it is sorely needed functionality for many enterprise applications) is excellent; that they chose to reuse the previous attribute name is only unfortunate for you (and the likely few who were subsequently using that attribute incorrectly). If Macromedia/Adobe had written CF6+ to throw errors if the obsolete variable was being used (rather than just ignoring it), your code would have had to have been fixed a long time ago. Might you have to rewrite your code now to fix this? Yes. Is it a PITA for you? Yes. Is it worth bashing one's head against when it is needed functionality for many other people? No.

Comment by External U.

@itsdesign, Can you submit the E/R to have CF automatically prefix the parameters as a separate bug report? If you do, I'll vote for it (and Adam probably will too)...

Comment by External U.

I sure will. That's the one good thing that came out of this drama.

Comment by External U.

Well, That make a solution out of this. I will surely Vote for this..."Prefix the parameter by CF, rather than programmer" Do we have new Number for this? I will surely Vote for it :)

Comment by External U.

Hi all, Filed the auto-prepend omitted dbvarname prefix request as #CF-3916950. Vote up! Thanks!, -Aaron

Comment by External U.

ALso seen this, accidentally filed duplicate ticket CF-4001377

Vote by External U.

Using a prefix is a very acceptable solution if there is an effort to make dbVarName work as it should work. It's an easy fix, just search/replace dbvarname=" with dbvarname="@ in your code, test in staging, and done. I do think it would be AWESOME if Adobe saw what kind of DSN it was, and appended the proper prefix by default. If left(var,1) NEQ "@", prepend. Perhaps a setting to enable this extra step, since it might introduce some overhead. For those that don't care, the JVM argument to ignore dbVarName works, too. So everyone can be happy. I don't see the importance of "fixing" something that breaks due to a mispelled variable name, (e.g. previously mentioned @loginname). No point in going back to bury your head in the sand.

Comment by External U.