ColdFusion 2016 upgrade giving stored procedure error

Question

We are in the process of upgrading to ColdFusion 2016 to work with SqlServer 2017. Our current version (ColdFusion 10/SqlServer 2008) is working fine.

We are testing our code in a development environment to find any issues with it and I keep coming across the same error that is a little cryptic. I'm not sure if it's the combination of ColdFusion 2016 and SqlServer2008 or just an upgrade from CF10 to CF2016. It involves CFStoredProc commands.

The code is:

<CFSTOREDPROC DATASOURCE="MedScribeSQL" PROCEDURE="dbo.usp_ChartRestrictionCheck">
    <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Org" TYPE="In" VALUE="#Cookie.Org#">
    <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Chartnum" TYPE="In" VALUE="#TRIM(Variables.PTChartnum)#">
    <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Username" TYPE="In" VALUE="#Cookie.Username#">
    <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="ReturnChart" TYPE="Out" VARIABLE="RestrictionReturnChart">
    <CFPROCPARAM CFSQLTYPE="CF_SQL_DATE" DBVARNAME="ReturnExpiryDate" TYPE="Out" VARIABLE="RestrictionReturnExpiryDate">
</CFSTOREDPROC>

When the page runs I am getting the following error:

Error Executing Database Query. 
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.   

The error occurred in D:/Websites/AcumenEHR/health/CustomTags/checkMRAccess.cfm: line 78
Called from D:/Websites/AcumenEHR/health/HCNHeader.cfm: line 127
Called from D:/Websites/AcumenEHR/health/HCNHeader.cfm: line 1
Called from D:/Websites/AcumenEHR/health/records/Visit_summary/hpsummary2.cfm: line 23
Called from D:/Websites/AcumenEHR/health/CustomTags/checkMRAccess.cfm: line 78
Called from D:/Websites/AcumenEHR/health/HCNHeader.cfm: line 127
Called from D:/Websites/AcumenEHR/health/HCNHeader.cfm: line 1
Called from D:/Websites/AcumenEHR/health/records/Visit_summary/hpsummary2.cfm: line 23

76 : <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Username" TYPE="In" VALUE="#Cookie.Username#">
77 : <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="ReturnChart" TYPE="Out" VARIABLE="RestrictionReturnChart">
78 : <CFPROCPARAM CFSQLTYPE="CF_SQL_DATE" DBVARNAME="ReturnExpiryDate" TYPE="Out" VARIABLE="RestrictionReturnExpiryDate">
79 : </CFSTOREDPROC>
80 :    

I can't find any information in the documentation about why this would be throwing an error. I'm wondering if anyone has had a similar experience with this and can point me in the direction of a fix since this same error occurs in a number of places.

Thanks

Answer 1

There were some changes made in CF11 on how the DBVARNAME attributes are handled. In CF10 they were essentially ignored, and since CF11 update 3 that has been 'fixed'.

You will need to make sure the values in the DBVARNAME attributes match the names of the parameters in the stored procedures exactly, in this case with SQL Server that likely means that you will need to prefix them with an @.

Here's a blog post outlining this change: https://coldfusion.adobe.com/2015/07/coldfusion-11-and-dbvarname-attribute/