More tests with EXECUTE BLOCK in Firebird

[egsuh]

I tried a few more tests with Execute Block with Flamerobin and Lazarus.

What I tested is following codes. This works when run in FlameRobin ISQL  (DSQL?) window.

Code: SQL  [Select][+][-]

1. SET term #;
2. EXECUTE BLOCK
3. AS
4.    DECLARE i INT;
5.    DECLARE lowend INT=10111;
6.    DECLARE highend INT=10120;
7. BEGIN
8.    i = lowend;
9.    while (i <= highend) do BEGIN
10.       UPDATE TEST SET Description= 'changed ' || CAST(:i AS VARCHAR(10))
11.       WHERE Code=:i;
12.       i = :i + 1;
13.    END
14. END
15. #
16. SET term ;#

But, when moved to Pascal, it does not run. Following codes raise DataBase exception. This is strange.

Code: Pascal  [Select][+][-]

1.    with SQLQuery1 do begin
2.       SQL.Clear;
3.  
4.       // SQL.Add('set term #;');
5.       SQL.Add('EXECUTE BLOCK');
6.       SQL.Add('AS');
7.           SQL.Add('declare i int;');
8.           SQL.Add('declare lowend int=10111;');
9.           SQL.Add('declare highend int=10120;');
10.       SQL.Add('BEGIN');
11.          SQL.Add('i = lowend;');
12.          SQL.Add('while (i <= highend) do begin');
13.             SQL.Add('update TEST set Description= ''changed '' || cast(:i as varchar(10))');
14.             SQL.Add('where Code=:i;');
15.             SQL.Add('i = :i + 1;');
16.          SQL.Add('end');
17.       SQL.Add('END');
18.       // SQL.Add('#');
19.       // SQL.Add('set term ; #');
20.  
21.       Transaction.Active := True;
22.  
23.       try
24.         ExecSQL;
25.        (Transaction as TSQLTransaction).Commit;
26.        showmessage('succes');
27.  
28.       except
29.         (Transaction as TSQLTransaction).Rollback;
30.       end;
31.       showmessage('fail');
32.    end;

Next, it says Firebird supports using parameters in following forms.

Code: SQL  [Select][+][-]

1. EXECUTE BLOCK (lowend INT=?, highend INT=?)
2. AS
3.    DECLARE i INT;
4. BEGIN
5.    i = lowend;
6.    while (i <= highend) do BEGIN
7.       UPDATE TEST SET Description= 'changed ' || CAST(:i AS VARCHAR(10))
8.       WHERE Code=:i;
9.       i = :i + 1;
10.    END
11. END
12.  

In Lazarus, there are no way to set parameters here. Neither Params[0] nor parambyname work. It raises Lazarus exception.

Not sure of the usefulness of Execute Block in these forms, as regular operations may be defined as stored procedures.