[firebird-support] Exception handling inside BLOCK combined with Execute Statement.

[bimmer_r]

Hi all.
I'm having a hard time figuering out - How to run a lengthy maintenance script 
with builtin security (read abort option).

Inside an Execute Block I do an

for select do begin
  EXECUTE STATEMENT  
  ON EXTERNAL 
end

And I dont want the whole block to abort if it fails ...
Can anyone tell me how to accomplish this

My best solution so far is :

Execute Block  -- first block of many
as
Begin
  rdb$set_context('USER_SESSION', 'SCRIPT_STATUS', 1);
  .... -- insert statements here ...
  rdb$set_context('USER_SESSION', 'SCRIPT_STATUS', 2);
End
....
Execute Block -- Next block
as
declare variable CLIENTDB varchar(50);
declare variable EXIST_FLAG integer;
Begin
  -- test if block should by run
  if rdb$get_context('USER_SESSION', 'SCRIPT_STATUS') = 2 then
  begin
    -- here comes the real tricky part ...
    -- I need to connect to X other databases ...
    for
    select CLIENTDB
    from Clients into :CDB
    do
    begin
      -- test if update of remote table is needed
      EXECUTE STATEMENT 'select 1 from rdb$relation_fields rf where 
rf.rdb$relation_name = ''ClientTable'' and rf.rdb$field_name = ''NewField'';'
      ON EXTERNAL :CLIENTDB
      WITH AUTONOMOUS TRANSACTION --dont really know if I need this
      AS USER 'sysdba' PASSWORD 'masterkey'
      INTO :exist_flag;
      if (Exist_Flag is null) then
      begin
        Execute Statement 'ALTER TABLE ClientTable ADD NewField VARCHAR(50) 
CHARACTER SET ISO8859_1 COLLATE ISO8859_1;'
        ON EXTERNAL :CLIENTDB
        WITH AUTONOMOUS TRANSACTION -- again .. I dont know ..
        AS USER 'sysdba' PASSWORD 'masterkey';
      end
    end
    -- update session variable and let script continue to next part .
    rdb$set_context('USER_SESSION', 'SCRIPT_STATUS', 3); -- and so on
  end
End

Kind Regards
Bimmer_R