More tests with EXECUTE BLOCK in Firebird
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2024-03-12 01:49

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET term #;
EXECUTE BLOCKAS   DECLARE i INT;   DECLARE lowend INT=10111;   DECLARE
highend INT=10120;BEGIN   i = lowend;   while (i <= highend) do BEGIN 
    UPDATE TEST SET Description= 'changed ' || CAST(:i AS
VARCHAR(10))      WHERE Code=:i;      i = :i + 1;   ENDEND#SET term ;#
But, when moved to Pascal, it does not run. Following codes raise
DataBase exception. This is strange.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    with
SQLQuery1 do begin      SQL.Clear;       // SQL.Add('set term #;');  
  SQL.Add('EXECUTE BLOCK');      SQL.Add('AS');         
SQL.Add('declare i int;');          SQL.Add('declare lowend
int=10111;');          SQL.Add('declare highend int=10120;');     
SQL.Add('BEGIN');         SQL.Add('i = lowend;');       
 SQL.Add('while (i <= highend) do begin');            SQL.Add('update
TEST set Description= ''changed '' || cast(:i as varchar(10))');     
     SQL.Add('where Code=:i;');            SQL.Add('i = :i + 1;');   
    SQL.Add('end');      SQL.Add('END');      // SQL.Add('#');      //
SQL.Add('set term ; #');       Transaction.Active := True;       try 
     ExecSQL;       (Transaction as TSQLTransaction).Commit;     
 showmessage('succes');       except        (Transaction as
TSQLTransaction).Rollback;      end;      showmessage('fail');   end;
Next, it says Firebird supports using parameters in following forms.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i EXECUTE
BLOCK (lowend INT=?, highend INT=?)AS   DECLARE i INT;BEGIN   i =
lowend;   while (i <= highend) do BEGIN      UPDATE TEST SET
Description= 'changed ' || CAST(:i AS VARCHAR(10))      WHERE
Code=:i;      i = :i + 1;   ENDEND 
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.