Firebird 1.5 "execute if" procedure

Question

In my application I run scripts to extend and change the database with every update. I still have to support Firebird 1.5 as some users just wouldnt upgrade.

Some times scripts fail and I have to supply updates correcting the errors. As they do not always happen, i need things like:

1. drop if exists (for views as there is no alter view in FB 1.5) add
2. column if it doesnt exist (add table columns)
3. execute sql if a val is not found in a col of a table

The first two work well but the last one does not:

set term ^ ;

create or alter procedure addif(tab_name varchar(31), col_name varchar(31),data_type varchar(100)) as
BEGIN
  if (not exists(select 1 from rdb$relation_fields  where upper(rdb$relation_name) = upper(:tab_name) and upper(rdb$field_name) = upper(:col_name))) then
    execute statement 'alter table '||tab_name||' add '||:col_name||' '||:data_type;
END
^

create or alter procedure dropif(object_name varchar(31)) as
begin
  if (exists(select 1 from rdb$relations where rdb$view_blr is not null and
       (rdb$system_flag is null or rdb$system_flag = 0) and upper(rdb$relation_name) = upper(:object_name))) then
    execute statement 'drop view '||object_name;
end
^

create or alter procedure execif(tab_name varchar(31), col_name varchar(31), val varchar(100), sql varchar(8192)) as
declare s varchar(500);
declare i integer;
begin
  s = 'select 1 from ' || :tab_name || ' where ' || :col_name || ' = ' || :val;
  execute statement s into i;
  if (i=0) then
    execute statement sql;
end
^
set term ; ^

If I execute

 execute procedure execif('prs','id','0','insert into ini(aval,akey) values (''555555'',''555555'');');

nothing happens. Debugging the procedure showed that (given the condition is met) the line

execute statement sql;

is executed but nothing happens. Even if sql contains invalid sql nothing happens.

I am sure I am missing something important here and would be grateful if nyone could help!

EDIT: I changed exceute statement sql to execute statement :sql and also changed sql to asql to no avail.

Answer 1

After some testing I found out that at least firebird 1.5 cannot process two "execute statement" statements in one procedure. So I split the whole thing into two procedures and it works!

The first function returns 1 of the value exists in the column of the table, the second uses this function and executes the statement if the first function does not return 1.

Here's the code:

create or alter procedure valexists(tab_name varchar(31), col_name varchar(31), val varchar(100)) returns (result integer)  as
begin
  execute statement ('select 1 from ' || :tab_name || ' where ' || :col_name || ' = ' || :val) into result;
  suspend;
end
^

create or alter procedure execif(tab_name varchar(31), col_name varchar(31), val varchar(100), sql varchar(8192)) as
begin
if (not exists (select 1 from valexists(:tab_name,:col_name,:val) where result=1)) then
    execute statement :sql;
end
^