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