Stored Procedures stop working after Coldfusion 11 install (OS X 10.10.1)

[SeemusDesignAndWeb]

I recently installed a fresh install of CF 11 on Mac OS X Yosemite. All works fine except my stored procedures now no longer work. I am using SQL Server 2008 on Win XP using VMWare Fusion 7.

The Stored procedure executes perfectly on the database and, when testing, all the paramaters are outputting OK on my CF page. But I get an error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.

The SP code is below:

<cfstoredproc datasource="#dsn#" procedure="clw_logThisPage">

  <cfprocparam cfsqltype="CF_SQL_NUMERIC" dbvarname="logid" value="#session.logid#">

  <cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="memberid" value="#session.member_id#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="pagename" value="#launch#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="clwref" value="#ref#" >

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="casesletter" value="#letter#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="statinstview" value="#view#">

  <cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="year" value="#year#">

  <cfprocparam cfsqltype="CF_SQL_NUMERIC" dbvarname="id" value="#id#">

  </cfstoredproc>

It looks as though the values are not being passed correctly to MS SQL. But I cannot find why this is happening. Any help would be much appreciated.

With thanks

John

[Carl Von Stetten]

By chance, did you download the CF11 installers within the past few week or so?  If that is the case, it has update 3 already integrated.  Update 3 changed the way the dbvarname attribute works for stored procedures - it restored some earlier functionality removed from CF back in version 7.  Prior to Update 2, the dbvarname attribute was ignored entirely and parameters were passed in the order they were coded in your cfstoredproc tag.  With Update 2, a JVM flag was added to enable CF to honor the dbvarname attribute.  In Update 3, the attribute is honored no matter what.  There is some debate right now in the bugbase as to whether this functionality was implemented well to support backward compatibility.

Anyway, you need to make sure the values in the dbvarname attributes match **exactly** the names of the parameters in the stored procedures, and they must be prefixed with the appropriate Oracle parameter symbol (I believe it is the colon ":"). 

-Carl V.

[SeemusDesignAndWeb]

Thanks for you reply. Yes I downloaded it yesterday and I imagined it was something to do with Update 3. However, I am accessing SQL Server. So I have checked the Stored Procedure on MS SQL and the variables are the same.  As I am using SQL Server should I add a prefix, if so what is it? As the colon causes an error too.

Many thanks

John

[Carl Von Stetten · Accepted solution]

Sorry, somehow I got Oracle stuck in my head.  The symbol for SQL Server is "@".

-Carl V.

[SeemusDesignAndWeb]

Thanks Carl, that works fine now. The only problem that remains for me is updating all my code!

Thanks again for your help

John

[Carl Von Stetten]

I applaud the fact that you are willing to update your code.  There are some on the bugbase who are refusing to.  Basically, they blame Adobe for breaking their faulty code.  In their cases, they not only didn't have the prefix attribute (which isn't necessarily faulty code IMHO), but had completely incorrect parameter names in the dbvarname attribute (definitely faulty).  You can't blame Adobe for that.

By the way, wouldn't it be nice if ColdFusion just put the appropriate prefix in there for you, since it already knows which database type you are connecting to (via the datasource configuration)?  If you agree, please vote up this bug report: Feature#3916950 - [ANeff] ER for: auto-prepend dbvarname prefix if omitted

-Carl V.

[SeemusDesignAndWeb]

I have cast my vote. Do you know if any changes I would make are backward compatible. I.e. Adding @ before the dbvarname, would that work on CF10?

John

[Carl Von Stetten]

Since (prior to CF11 Update 2) ColdFusion was completely ignoring the dbvarname attribute, you could put whatever you want in that attribute in CF10 without any issues.

That is actually the problem that some are complaining about now - they had put values in the dbvarname attributes that didn't at all match what the parameters were named on the DBMS side (which was just poor coding practice), and now those mismatched values result in errors.

-Carl V.