8

I have several SQL Server 2008 stored procedures. I am calling them via cfstoredproc. After I run a CF server update on April 17th, CF does not get the results anymore. Here is one of the calls:

<cfstoredproc procedure="proc_xxxx" datasource="#application.dsn#" debug="yes" returnCode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_integer"  dbvarname="@myID" value="#arguments.myID#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"  dbvarname="@EmailAddr" value="#trim(arguments.emailAddress)#"  null="#not len(arguments.emailAddress)#">
<cfprocparam type="out" cfsqltype="cf_sql_integer" dbVarName="@NewUserID"  variable="newUserID" > 
<cfprocresult name="qryUser">   
</cfstoredproc>

<cfoutput>newUserID = #newUserID#</cfoutput>

I get a newUserID not defined error. Again, everything used to run fine prior to update 5.

If I call the stored procs in SSMS, everything works fine. The cfstoredproc works as I am seeing my updates and inserts on the database. However, it simply won't return any results back anymore. Return code is 0, as I mentioned everything runs without any errors.

I tried :

 <cfprocresult resultset="1" name="qryResult">

With no luck. Any ideas? Thanks in advance.

CFNinja
  • 3,571
  • 4
  • 38
  • 59
  • 2
    please file a report to http://bugbase.adobe.com and also add a comment to http://blogs.coldfusion.com/post.cfm/coldfusion-11-update-5-and-coldfusion-10-update-16-released to alert others. – Henry Apr 21 '15 at 17:11
  • (Edit) Is dbvarname still supported? It was deprecated at some point, resurrected in update 3 I think (?). Not sure about its current state. a) What is your procedure supposed to return? b) For grins, any change if you use positional notion instead of dbvarname? – Leigh Apr 21 '15 at 17:46
  • Does dumping out the `cfstoredproc` structure after the call return anything useful? – Miguel-F Apr 21 '15 at 17:52
  • Have you tried outputting #qryUser.myUserID#? – Adrian J. Moreno Apr 21 '15 at 17:56
  • 2
    Sounds like there's a coupla things broken with CFSTOREDPROC after this last update: https://bugbase.adobe.com/index.cfm?event=bug&id=3971083 – Adam Cameron Apr 21 '15 at 18:08
  • @Leigh I get the same problem with our without dbvarname. I did read on Adam Cameron's blog. It is supposed to return UserID which is an integer value. Same code base works on other environments : dev, stg, prod. I did the update on localhost only. – CFNinja Apr 21 '15 at 18:30
  • @AdrianJ.Moreno, newUserID is no where to found in the debug, cfdump of variables scope. It is just gone. – CFNinja Apr 21 '15 at 18:31
  • @Miguel-F cfdump of cfstoredproc shows ExecutionTime = 7ms and StatusCode=0 ( meaning it run fine). Once again, when I query the dB, I can see my results in there. – CFNinja Apr 21 '15 at 18:34
  • @CFNinja - Take a look at the bug Adam linked. Sounds promising.. – Leigh Apr 21 '15 at 18:37
  • The bug is what, describes the issue. What is the DB in question here - Oracle? – Anit Kumar Apr 21 '15 at 18:54
  • @AnitKumar SQL Server 2008. – CFNinja Apr 21 '15 at 19:04
  • @CFNinja, this becomes a duplicate bug. Please track the 3971083 and leave a comment there. – Anit Kumar Apr 21 '15 at 19:10
  • @AnitKumar I guess it is related. However, I cannot even get one result out. He is lucky to get the rest. – CFNinja Apr 21 '15 at 19:14
  • Can you try the sample example at https://wikidocs.adobe.com/wiki/display/coldfusionen/cfprocparam and see what happens? – Anit Kumar Apr 21 '15 at 19:16
  • @AnitKumar Hi Anit, I just moved my code upto dev server. I get: callResult - struct QRYRESULT callResult - struct [empty] newUID = 8 – CFNinja Apr 21 '15 at 19:20

1 Answers1

7

After quite a few back and forth emails with Adobe support, they told me to uncheck : Enable Request Debugging Output in CF admin.

Once done, the parameter comes back as expected:

 <cfprocparam type="out" cfsqltype="cf_sql_integer" dbvarname="@NewUserID" variable="newUserID"  >

Needless to say, we need the debug outputs on local and development servers. I don't believe this bug should be marked as fixed but I guess they are doing so.

Hope it helps someone else who runs into same issue.

EDIT on 1 May 2015 : There is a fix for this now.

  1. Download this hotfix file [ hf1100-3971083.jar ].
  2. Place this file at /cfusion/lib/updates folder.
  3. Restart ColdFusion application service.

Boom, it works!

Leigh
  • 28,765
  • 10
  • 55
  • 103
CFNinja
  • 3,571
  • 4
  • 38
  • 59
  • (Edit - fix link) (Reposting comment by [@Jacki](http://stackoverflow.com/users/4854994/jacki) *"Is there any way you can put a link to the hotfix .jar here. I am looking for it all over Adobe's site and can not find where to download it. Much appreciating this post as I just updated my CF and all MY STORED Procedures return values disappeared also."* – Leigh May 02 '15 at 03:47
  • 1
    @Jacki, here it is : https://www.dropbox.com/s/2le99rz5lauofbj/hf1100-3971083.abc?dl=0 – CFNinja May 02 '15 at 16:29