community.borland.com

Article #25848: Dropping a procedure generates invalid request BLR error

Problem:
When trying to drop a procedure the following error is generated:

invalid request BLR at offset 22
-parameter mismatch for procedure PROC1


Solution:
The information in this article applies to:
* InterBase v5.x
* InterBase v6.x

This error is produced when a stored procedure calls another stored procedure, 
but with the wrong number or type of parameters.   This situation is commonly 
encountered when you alter the procedure being called and change its parameter
list, thus leaving the calling procedure with an incorrect parameter list.

As an example look at the included script file at the end of this document.  Proc1 is created with
one integer parameter.  Proc2 is created and calls proc1 with the required one integer 
parameter.  Proc1 is then altered to have zero parameters.   The error will occur at this point
if an attempt to drop Proc2 is executed.

The workaround is to alter Proc2 to not rely on Proc1.  Once the relationship is severed Proc2 can
be dropped or re-altered to execute Proc1 with the correct number/type of parameters.

----------------------- begin script --------------------------------

create database "proc.mismatch.gdb";

create table foo (i1 integer);

set term !!;
create procedure proc1 (i1 integer)
as
begin
  insert into foo (i1) values (:i1);
end!!

create procedure proc2
as
begin
  execute procedure proc1(1);
end!!

commit!!

alter procedure proc1
as
begin
  insert into foo (i1) values (22);
end!!

commit!!
set term ;!!

connect proc.mismatch.gdb;

set echo;
execute procedure proc2;

drop procedure proc2;

connect proc.mismatch.gdb;

set term !!;
alter procedure proc2
as
begin
  insert into foo (i1) values (100);
end!!

set term ;!!
execute procedure proc2;

commit;


Last Modified: 19-OCT-00