ColdFusion 10 error with Stored Procedures

Question

In a .CFC file, within a CFfunction and with CFargument tags.

    <cfscript>
        var sp=new storedproc();
        sp.setDatasource(variables.datasource);
        sp.setProcedure("storedProcedure_INSERT");
        sp.addParam(cfsqltype="cf_sql_integer",type="in",value=arguments.one);
        sp.addParam(cfsqltype="cf_sql_integer",type="in",value=arguments.two);
        sp.addParam(cfsqltype="cf_sql_integer",type="in",value=arguments.three);
        sp.addParam(cfsqltype="cf_sql_integer",type="in",value=arguments.four);
        sp.addProcResult(name="results",resultset=1);
        //writeDump(sp);break;  //This dump is reached
        var spObj=sp.execute(); //blows up here; this is never reached
        writeDump(spObj);break; //This is never reached, either.
        var spResults=spObj.getProcResultSets().results;

A shiny nickle to anyone who can tell me why the sp.execute() is blowing up with message

"Cannot find results key in structure.

The specified key, results, does not exist in the structure."

I've used this psuedo-code many, may times in the past, and never had it do this. I'm connected to a MSSQL Server 2012 DB, everything's cricket in CF Admin, and other SPs are working properly. The stack trace doesn't even include any of MY code at all o_O

The error occurred in C:/ColdFusion10/cfusion/CustomTags/com/adobe/coldfusion/base.cfc: line 491 Called from C:/ColdFusion10/cfusion/CustomTags/com/adobe/coldfusion/storedproc.cfc: line 142 Called from //hq-devfs/development$/websites/myProject/cfc/mySOAPWSDLs.cfc: line 123

And SO is blowing up if I try and paste anymore of that. Google has...not been helpful ._.

Answer 1

Short answer: The error means you are trying to retrieve a resultset from the stored procedure, when it does not actually return one. A simple solution is to add a SELECT to the end of your procedure, so it returns a resultset containing the data you need. Then your original code will work:

   SELECT @@ROWCOUNT AS NumOfRowsAffected;

Longer answer: The method you are using, addProcResult(), is the equivalent of <cfprocresult>. It is intended to capture a resultset returned from a stored procedure. (Due to CF's poor choice of attribute names, a lot of people think "resultset" means the storedproc "result" structure, but they are two totally different things). A "resultset" is a query object", in CF parlance.

While all four (4) of the primary sql statements return some result, not all of them return a "query object"

• Only SELECT statements generate a "query object"
• INSERT/UPDATE/DELETE statements simply return the number of rows affected. They do not generate a "query object".

Since your stored procedure performs an INSERT, it does not generate a "query object". Hence the error when you try and grab the non-existent query here:

  sp.addProcResult(name="results",resultset=1);

The simple solution is to add a SELECT statement to the end of your stored procedure, so that it does return a query object. Then your code will work as expected.

---

As an aside, I suspect you were actually trying to grab the "result" structure, but used the wrong method. The equivalent of <cfstoredproc result=".."> is getPrefix(). Though that would not work here anyway. According to the docs, it does not contain the number of rows affected. Probably because stored procedures can execute multiple statements, each one potentially returning a row count, so there is not just a single value to return.