cfstoredproc not working in CF2018

[Jdsplicer]

We are going thru upgrade from CF10 to CF18. The same code used to call oracle stored procedure in CF10 fails in CF2018. I am not sure what may have changed with this tag but the error message doesn't make sense to me based on values I am passing. I can confirm the store procedure is working fine.

Code:

<cftry>

<cfstoredproc procedure="pps_owner.prodplan.sp_CalcProduction" datasource="#session.datasource#">

     <cfprocparam type="In" cfsqltype="cf_sql_numeric" dbvarname="STR_NUM" value="6005" null="No">

     <cfprocparam type="In" cfsqltype="cf_sql_numeric" dbvarname="CAT_ID" value="20" null="No">

     <cfprocparam type="In" cfsqltype="cf_sql_varchar"  dbvarname="PLN_RUN_TS" value="31-Jul-2019 17:20:37" null="No">

</cfstoredproc>

<cfcatch type="database"><cfdump var="#cfcatch#"><cfabort></cfcatch>

</cftry>

[Charlie Arehart · Accepted solution]

Gerald, I am nearly sure I can tell you “why”. It’s that an update in the middle of the life of CF11 (update 3 in 2015) changed the way CFSTOREDPROC and in particular the related CFPROCPARAM works.

And it may be a rather easy fix for you, if you don’t have too many such SP calls. Let me explain.

Explanation:

See that dbvarname attribute you use? Ever since CF5 it has been ignored, then all of a sudden in the middle of CF11 an update made CF start paying attention to it. That would be bad enough—if perhaps the values there did not match the proc arg names in your DB.

But worse, in all those years since, most DB vendors changed how they wanted calls to SPs to work, such that for Oracle they wanted a : in front o the varname (and SQL Server wanted an @).

Solution(s):

So first, if you will put that : in front of your dbvarnames, that may “fix it”. And if that does not, check to make sure that the arg names match in the SP and your cfprocpram’s. Again, for nearly 20 years any mismatch was IGNORED!

Now, some cried foul back then, complaining that they had to “change too much code”, and Adobe relented and offered a JVM argument that would override this behavior. But it only existed for CF11. It was NOT carried into CF2016 or 2018.

For more on all this, see:

https://coldfusion.adobe.com/2015/07/coldfusion-11-and-dbvarname-attribute/

/charlie

[Jdsplicer]

Charlie, you are the freakin MAN!!! Adding the : in front of my dbvarnames (because I am going against Oracle) did the trick. Luckily I only had about 5 SP calls to update. I searched all day for a solution but I was searching for cfstoredproc issues and not dbvarnames so I never came across that article. Thank you very much for your help. 

[Charlie Arehart]

Very glad to hear. Thanks for the kind regards. And yep, this was a rather lamentable problem—and not so easily discovered.

Thankfully we have these forums (and the portal and other places, like the slack channel and FB group) where people can tap into the “community mind”. As for me, I often feel like the CF “research librarian”, helping folks find dusty old tomes that unlock words of wisdom that I’ve seen shared over the years.

/charlie