In the process of finally banishing Adobe ColdFusion 8 from my application servers and completing the transition to CF 11, I came across what is apparently a little-known change in the operation of the CFPROCPARAM tag inside a CFSTOREDPROC tag.
After moving the website from CF 8 to CF 11, there were no major changes that had to be made. However, one particular action kept causing a dialog box to pop up:
I was able to trace the source of the dialog box to an error handler of sorts, but in this case, the error was quite nebulous. The error code “HANDLEDATABASEERROR” didn’t really tell me much. It appears that it may have come from Java itself.
After tracing back which function call seemed to be causing the problem, I discovered that the call to the SQL stored procedure was not using the correct parameter names – that is, names that matched the ones in the stored proc itself.
To determine if the database activity was actually the source of the error, I turned on Log Activity in the Data Source in the ColdFusion Administrator: Data Sources –> (specific Data Source Name) –> Show Advanced Settings –> Log Activity checkbox and text box showing where the log file will go. Example:
After reloading the web page and doing the same action, the an error like the one below showed up in the dblogs.txt file:
spy(ajp-bio-8014-exec-6)(2018/09/26 12:42:30.326)>> java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or function 'sp_getNameSuggestion' expects parameter '@GoodParamName', which was not supplied. ErrorCode=201 SQLState=HY000
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or function 'sp_storedProcName' expects parameter '@GoodParamName', which was not supplied.
at macromedia.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at macromedia.jdbc.sqlserver.tds.ddr.c(Unknown Source)
at macromedia.jdbc.sqlserver.dda3.m(Unknown Source)
at macromedia.jdbc.sqlserverbase.dde7.e(Unknown Source)
at macromedia.jdbc.sqlserverbase.dde7.a(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddd2.a(Unknown Source)
at macromedia.jdbc.sqlserverbase.dde7.x(Unknown Source)
at macromedia.jdbc.sqlserverbase.dde7.t(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddd2.execute(Unknown Source)
at macromedia.jdbc.sqlserverbase.ddd6.execute(Unknown Source)
at macromedia.jdbcspysqlserver.SpyPreparedStatement.execute(Unknown Source)
at com.intergral.fusionreactor.jdbc.jdbc42.PreparedStatementSurrogate2.execute(PreparedStatementSurrogate2.java:200)
at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101)
at coldfusion.sql.Executive.executeCall(Executive.java:1087)
at coldfusion.sql.Executive.executeCall(Executive.java:960)
at coldfusion.sql.Executive.executeCall(Executive.java:910)
at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:528)
at coldfusion.tagext.sql.StoredProcTag.executeQuery(StoredProcTag.java:341)
at coldfusion.tagext.sql.StoredProcTag.doEndTag(StoredProcTag.java:287)
...
The CFML source code calling this stored procedure was revealing:
<cfstoredproc procedure="dbo.sp_storedProcName" datasource="#getDSN()#">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" dbvarname="@BadParamName" value="#arguments.GoodParamData#" null="no" />
<cfprocparam cfsqltype="cf_sql_integer" type="in" dbvarname="@OtherParam" value="#arguments.OtherParamData#" null="no" />
<cfprocresult name="qResultSet" />
</cfstoredproc>
In some older versions of ColdFusion (at least as recently as version 8, perhaps all the way through 10), if the name of the parameter stored in the dbvarname attribute of the cfprocparam tag wasn’t correct, no error was thrown as long as the data types of the parameters matched in the correct order for both the cfstoredproc tag and the database stored procedure. This dbvarname attribute was essentially ignored in those versions. Version 11 changed all that.
By changing the source code to reflect the correct name, the bug was fixed.
<cfstoredproc procedure="dbo.sp_storedProcName" datasource="#getDSN()#">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" dbvarname="@GoodParamName" value="#arguments.GoodParamData#" null="no" />
<cfprocparam cfsqltype="cf_sql_integer" type="in" dbvarname="@OtherParam" value="#arguments.OtherParamData#" null="no" />
<cfprocresult name="qResultSet" />
</cfstoredproc>