mysqlcoldfusioncoldfusion-7

Coldfusion Stored Procedure error

CF: Version 7.02

Platform: Windows

DB: MySQL 5.1.45-community

I'm getting an error when executing a simple Stored Procedure in CF.

<CFSTOREDPROC DATASOURCE="datasource" PROCEDURE="deletePosition"> 
     <CFPROCPARAM CFSQLTYPE="cf_sql_integer" DBVARNAME="varID" VALUE="#varID#"> 
</CFSTOREDPROC>

Stored Procedure:

   DROP PROCEDURE deletePosition 
   CREATE DEFINER= PROCEDURE deletePosition(in varID int) 
   BEGIN 
        UPDATE positions SET pos_active = 0 WHERE pos_id = varID; 
        UPDATE tour SET tour_active = 0 WHERE pos_id = varID; 
   END

VarID is being passed in and this is the error that it's generating:

Error Executing Database Query.

Parameter index of 2 is out of range (1, 1)
SQL   {call deletePosition( (param 1) , (param 2) )}
SQLSTATE      S1009

According to my network guy, the drives are close to being up to date.

Thanks in advance.

Solution

  • Looking at your SQL, it looks like you only have a single varID? Does your SP accept 1 parameter or 2? If just one, then this should work:

    call deletePosition (1)

    I haven't used Cold Fusion in a while, but this is one way I remember calling SPs:

    <CFQUERY dataSource="datasource">
deletePosition #varID#
</CFQUERY>

    Does that work?