SET TERM ^^ ;
CREATE OR ALTER PROCEDURE P_2
returns (
O_CHAR VarChar(200) character set utf8)
SQL SECURITY INVOKER
AS
declare variable i integer;
declare variable a integer;
declare variable o integer;
declare variable outer_v varchar(200) character set utf8;
declare function m (v varchar(10)) returns varchar(10) as
begin
return v || outer_v;
end
declare function myfunc (a2 integer) returns integer as
declare variable inner_char varchar(200);
begin
i = i + 1;
select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v;
inner_char = m('test');
if (a2 = 4)
then a2 = myfunc(a2 + 1);
return a2 :a :i;
end
begin
/ i = 5;
i = package_test.MyFunction(2);
i = mult(4, 2);
o = myfunc(a);/
outer_v = current_user;
o_char = outer_v;
end ^^
SET TERM ; ^^
When executing, it returns “S” for output parameter O_CHAR.
Now, recreate the procedure, but comments out the “declare functions”:
SET TERM ^^ ;
CREATE OR ALTER PROCEDURE P_2
returns (
O_CHAR VarChar(200) character set utf8)
SQL SECURITY INVOKER
AS
declare variable i integer;
declare variable a integer;
declare variable o integer;
declare variable outer_v varchar(200) character set utf8;
/* declare function m (v varchar(10)) returns varchar(10) as
begin
return v || outer_v;
end
declare function myfunc (a2 integer) returns integer as
declare variable inner_char varchar(200);
begin
i = i + 1;
select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v;
inner_char = m('test');
if (a2 = 4)
then a2 = myfunc(a2 + 1);
return a2 :a :i;
end */
begin
/ i = 5;
i = package_test.MyFunction(2);
i = mult(4, 2);
o = myfunc(a);/
outer_v = current_user;
o_char = outer_v;
end ^^
SET TERM ; ^^
execute again: the routine returns “SYSDBA”.
The lines executed are no different, but the result is now correct. At first I thought it had something to do with the character set, so I added the UTF8 thing everywhere, even though thats the default character set.
Take this procedure, connect with SYSDBA.
SET TERM ^^ ; CREATE OR ALTER PROCEDURE P_2 returns ( O_CHAR VarChar(200) character set utf8) SQL SECURITY INVOKER AS declare variable i integer; declare variable a integer; declare variable o integer; declare variable outer_v varchar(200) character set utf8;
declare function m (v varchar(10)) returns varchar(10) as begin return v || outer_v; end
declare function myfunc (a2 integer) returns integer as declare variable inner_char varchar(200); begin
i = i + 1; select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v; inner_char = m('test'); if (a2 = 4) then a2 = myfunc(a2 + 1); return a2 :a :i; end
begin / i = 5; i = package_test.MyFunction(2); i = mult(4, 2);
o = myfunc(a);/ outer_v = current_user; o_char = outer_v; end ^^ SET TERM ; ^^
When executing, it returns “S” for output parameter O_CHAR.
Now, recreate the procedure, but comments out the “declare functions”:
SET TERM ^^ ; CREATE OR ALTER PROCEDURE P_2 returns ( O_CHAR VarChar(200) character set utf8) SQL SECURITY INVOKER AS declare variable i integer; declare variable a integer; declare variable o integer; declare variable outer_v varchar(200) character set utf8;
/* declare function m (v varchar(10)) returns varchar(10) as begin return v || outer_v; end
declare function myfunc (a2 integer) returns integer as declare variable inner_char varchar(200); begin
i = i + 1; select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v; inner_char = m('test'); if (a2 = 4) then a2 = myfunc(a2 + 1); return a2 :a :i; end */
begin / i = 5; i = package_test.MyFunction(2); i = mult(4, 2);
o = myfunc(a);/ outer_v = current_user; o_char = outer_v; end ^^ SET TERM ; ^^
execute again: the routine returns “SYSDBA”.
The lines executed are no different, but the result is now correct. At first I thought it had something to do with the character set, so I added the UTF8 thing everywhere, even though thats the default character set.