Having an Issue with too many parameters being passed to stored procedures in ColdFusion 2016

Question

I have several stored procedures in an application that were functioning perfectly (for years) until our recent upgrade from ColdFusion 2010 to ColdFusion 2016. Now, I am getting error messages of either too many parameters or a certain parameter is not a parameter is not contained in the procedure that is being called.

I have opted to upload some code so people can better understand what is actually happening. Still learning how to format code here so please forgive me if it is still lacking.

In both cases I have double checked the parameter lists in the stored procedure in the procedure calls and have found that they are all indeed correct. In fact, nothing has changed in this code for over 5 years. This behavior has only begun since the upgrade has taken place.

Below is the first example. I will list the procedure call (in cfscript) first then the parameter list from the stored procedure and then the error message it produced:

public query function readStorage(numeric group1=0,numeric group2=0) {  
     local.group1Value = arguments.group1?arguments.group1:"";  
     local.group2Value = arguments.group2?arguments.group2:"";  
     spService = new storedproc();   
     spService.setDatasource(variables.dsn);  
     spService.setUsername(variables.userName);  
     spService.setPassword(variables.password);   
     spService.setProcedure("usp_readCompatibilityStorage");
     spService.addParam(dbvarname="@group1Id",cfsqltype="cf_sql_integer"
           , type="in",value=local.group1Value,null=!arguments.group1);  
     spService.addParam(dbvarname="@group2Id",cfsqltype="cf_sql_integer"
          ,type="in",value=local.group2Value,null=!arguments.group2);  
     spService.addProcResult(name="rs1",resultset=1);  

     local.result = spService.execute();   

     return local.result.getProcResultSets().rs1;   
}

Below is the parameter list from the stored procedure:

 @groupId1  int = NULL        
,@groupId2  int = NULL

Below is the error message I get:

[Macromedia][SQLServer JDBC Driver][SQLServer]@group1Id is not a parameter for procedure usp_readCompatibilityStorage.

Second Example:

public query function read(string cribIdList="",  
                        numeric cribNumber=0,  
                        string isAnnex="",  
                        numeric siteId=0,  
                        string parentCribIdList="",  
                        numeric supervisorId=0,  
                        numeric statusId=0,  
                        string orderBy="cribNumber ASC") {  

    local.cribNumberValue = arguments.cribNumber?arguments.cribNumber:"";  
    local.siteIdValue = arguments.siteId?arguments.siteId:"";  
    local.superIdValue = arguments.supervisorId ? arguments.supervisorId:"";  
    local.statusIdValue = arguments.statusId ? arguments.statusId:"";  

    spService = new storedproc();   
    spService.setDatasource(variables.dsn);  
    spService.setUsername(variables.userName);  
    spService.setPassword(variables.password);   
    spService.setProcedure("usp_readCrib");   

    spService.addParam(dbvarname="@cribIdList",cfsqltype="cf_sql_varchar"
        ,type="in",value=arguments.cribIdList
        ,null=!len(arguments.cribIdList));  
    spService.addParam(dbvarname="@cribNumber",cfsqltype="cf_sql_integer"
         ,type="in",value=local.cribNumberValue
         ,null=!arguments.cribNumber);   
    spService.addParam(dbvarname="@isAnnex",cfsqltype="cf_sql_varchar"
        ,type="in",value=arguments.isAnnex,null=!len(arguments.isAnnex));  
    spService.addParam(dbvarname="@siteId",cfsqltype="cf_sql_integer"
         ,type="in",value=local.siteIdValue,null=!arguments.siteId);  
    spService.addParam(dbvarname="@parentCribIdList" 
         , cfsqltype="cf_sql_varchar", type="in"
         , value=arguments.parentCribIdList
         , null=!len(arguments.parentCribIdList));  
    spService.addParam(dbvarname="@supervisorId", 
         cfsqltype="cf_sql_integer", type="in",value=local.superIdValue
         , null=!arguments.supervisorId);  
    spService.addParam(dbvarname="@statusId"
         , cfsqltype="cf_sql_integer", type="in"
         , value=local.statusIdValue, null=!arguments.statusId);        
    spService.addParam(dbvarname="@orderBy",cfsqltype="cf_sql_varchar"
         , type="in",value=arguments.orderBy);  
    spService.addProcResult(name="rs1",resultset=1);  

    local.result = spService.execute();  

    return local.result.getProcResultSets().rs1;   
}

Below is the parameter list from the stored procedure:

@cribIdList         varchar(500) = NULL  
,@cribNumber        int = NULL  
,@isAnnex           varchar(3) = NULL  
,@siteId            int = NULL  
,@parentCribIdList  varchar(500) = NULL  
,@supervisorId      int = NULL  
,@statusId          int = NULL  
,@orderBy           varchar(50)

Below is the message returned from the server:

[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or function usp_readCrib has too many arguments specified.

In the case of both errors, they seem to be occurring at the following path:

Error Details - struct
COLUMN      0
ID          CFSTOREDPROC
LINE        489
RAW_TRACE   at cfbase2ecfc235349229$funcINVOKETAG.runFunction(E:\ColdFusion2016\cfusion\CustomTags\com\adobe\coldfusion\base.cfc:489)
TEMPLATE    E:    \ColdFusion2016\cfusion\CustomTags\com\adobe\coldfusion\base.cfc
TYPE    CFML````

Having an Issue with too many parameters being passed to stored procedures in ColdFusion 2016

Answers (1)

Related Questions