Running the script:
SET PATH = CURRENT PATH, IS3;
set schema is3;
call add_location(2,349,'HQ Calving Barn',675);
causes the following error:
SQL0440N No authorized routine named "ADD_LOCATION" of type "PROCEDURE"
having compatible arguments was found. SQLSTATE=42884
I have run the following, without error, to define add_location:
connect to animals@
set schema is3@
drop procedure add_location@
create procedure add_location(in ent integer,
in new_id integer,
in new_name varchar(50),
in under integer)
language sql
modifies sql data
begin
declare under_lft integer;
declare under_rgt integer;
set schema is3;
select leftx, rightx into under_lft,under_rgt
from locations
where loc_id = under;
insert into locations values(new_id,new_name,0,0,ent);
update locations
set leftx = case
when leftx >= under_rgt then leftx+2
when leftx = 0 then under_rgt
else leftx
end,
rightx = case
when rightx >= under_rgt then rightx+2
when rightx = 0 then under_rgt+1
else rightx
end;
end@
It results in the following entry in the procedure table reported by quest:
IS3; ADD_LOCATION; INTEGER,INTEGER,VARCHAR,INTEGER; SQL040715...; SQL
which appears all right to me. I am logged in with the same
userid/passwd that created the procedure. I don't understand. Any advice
or pointers to documents would be greatly appreciated.
