Firebird脚本未产生预期结果

Question

我正在运行以下Firebird SQL脚本，但它在执行后不会产生预期结果。我想在我的Delphi应用程序中使用它，但首先要在EMS SQL Manager IDE中测试它。执行脚本后，所有表应该是空的，所有触发器都设置为非活动状态，所有发生器设置为0.但是，这一切都没有发生，我不知道为什么。

这里是脚本：

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', 
    '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 ;^

Answer 1

我不是100％肯定，在这里，但暂停，只需要你有一个存储过程返回值（称为一个select * fromsp()）。

在这种情况下，我认为暂停正在破坏您的代码。

Answer 2

什么Delphi组件，你用它来运行SQL脚本？你有任何错误信息？您可以通过编写一些Delphi代码来完成任务。

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; 

Answer 3

您应该删除行suspend;。