Firebird script not producing expected results

I am running the following Firebird SQL script but it is not producing the expected results after execution. I want to use it in my Delphi application, but am testing it in the EMS SQL Manager IDE first. After executing the script, all tables should be empty, all triggers set to inactive and all generators set to 0. However, all this is not happening and I don't know why.

here is the script:

SET TERM ^ ;

execute block
as

declare trig char(31);
declare genr char(31);
declare reln char(31);

-- all triggers
declare curt cursor for
(select rdb$trigger_name from rdb$triggers
 where rdb$system_flag=0);

-- all generators
declare curg cursor for
  (select rdb$generator_name 
   from rdb$generators 
   where rdb$system_flag = 0);

-- all tables
declare cur cursor for 
  (select rdb$relation_name from rdb$relations
   where rdb$system_flag = 0 and rdb$view_blr is null
   and rdb$relation_name not in ('tblyesno', 
   'tblpaymentmethod开发者_StackOverflow', 
   'tblresourcetype'));

begin

  -- deactivate all triggers
  open curt;
  while (1=1) do
  begin
   fetch curt into trig;
   if (row_count = 0) then leave;
   execute statement 'alter trigger ' || trig || ' inactive';
   suspend;
  end 
  close curt;

  -- zero all generators
  open curg;
  while (1=1) do
  begin
   fetch curg into genr;
   if (row_count = 0) then leave;
   execute statement 'set generator ' || genr || ' to 0';
   suspend;
  end
  close curg;

  -- truncate all tables
  open cur;
  while (1=1) do
  begin
    fetch cur into reln ;
    if (row_count = 0) then leave;
    execute statement 'delete * from ' || reln;
    suspend;
  end 
  close cur; 

  INSERT INTO 
  TBLCOUNTRY
 (
  CURRENCYABBR,
  COUNTRYNAME,
  CURRENCYNAME
 ) 
 VALUES (
  'A',
  'Aa',
  'Aaa' ); 


end^

SET TERM ; ^

I'm not 100% sure here but suspend is needed only if you have a stored procedure that returns values (called with a select * fromsp()).

In this case I think the suspend is breaking your code.

You should remove the lines suspend;.

What Delphi component do you use to run a SQL script? Do you get any error message? You can accomplish the task with writing a bit of Delphi code.

var
  q, qList: TIBSQL;
  Tr: TIBTransaction;
begin
  Tr := TIBTransaction.Create(nil);
  q := TIBSQL.Create(nil);
  qList := TIBSQL.Create(nil);
  try
    Tr.DefaultDatabase := <database>;
    Tr.StartTransaction;

    q.Transaction := Tr;
    qList.Transaction := Tr;

    qList.SQL.Text := 
      'select rdb$trigger_name from rdb$triggers ' +
      'where rdb$system_flag=0 ';
    qList.ExecQuery;
    while not qList.EOF do
    begin
      q.SQL.Text := 'ALTER TRIGGER ' + qList.Fields[0].AsTrimString + ' INACTIVE';
      q.ExecQuery;
      qList.Next;
    end;

    qList.Close;
    Tr.Commit;
    Tr.StartTransaction;

    // do a cycle for generators
    // then for tables

    Tr.Commit;
  finally
    qList.Free;
    q.Free;
    Tr.Free;
  end;
end;