A: The ';' in many SQL script dialects is a delimiter of the SQL commands and is not a part of the SQL language. BTW, some of the DBMSs allows ';' at end of SQL command, others - does not. IOW, just remove ';'. 

A: This is by design – the ExecSQL method transfers a SQL command to DBMS API as is. If a DBMS supports the "batch" queries, then it will execute the query, otherwise it will fail. TADScript allows to execute SQL scripts with multiple SQL commands and script control commands. 

A: The first script is a "root" script. To execute other scripts, you should call then explicitly from "root" script. For example: 

with ADScript1.SQLScripts do begin
  with Add do begin
    Name := 'root';
    SQL.Add('@first');  // explicitly call 'first' script
    SQL.Add('@second'); // explicitly call 'second' script
  end;
  with Add do begin
    Name := 'first';
    SQL.Add('create table t1;');
    SQL.Add('create table t2;');
  end;
  with Add do begin
    Name := 'second';
    SQL.Add('create procedure p1;');
    SQL.Add('create procedure p2;');
  end;
end;

ExecuteStep executes next script command starting from TADScript.Position position. ExecuteAll executes script in full. There is also ValidateAll / ValidateStep. These methods process script, but does not execute SQL commands. At end ValidateAll call assigns value to TADScript.TotalJobSize. So, next ExecuteAll call will correctly update TotalPct10Done, which is 10*% of processed script commands. 

A: The SQL execution code behind each of the methods is the same. There is used IADPhysCommand.Execute method. 

The TADScripts parser is highly optimized, knows many SQL dialect aspects, like a Oracle PL/SQL (where ';' may be in the middle of a command), and allows very soft controll. 

So, if you submits command by command to the ADConnection.ExecSQL and there is no need to extract these commands from a script or how else, then it will be a fastest method. If you have a SQL script in a file, then ADScript.ExecuteAll will be the fastest method. 

A: 1) Use AnyDAC transaction control: 

ADConnection1.StartTransaction;
try
  ADScript1.ExecuteAll;
  ADConnection1.Commit;
except
  ADConnection1.Rollback;
  raise;
end;

2) Use PL/SQL block in case of Oracle or similar constructions for other DBMS's: 

begin
  insert into u_btk.t_test values (1, sysdate);
  insert into u_btk.t_test values (2, sysdate);
  insert into u_btk.t_test values (1, sysdate);
  commit;
exception
  when others then
    rollback;
    raise;
end;

3) Use TADScript.OnError: 

procedure TForm1.ADScript1Error(ASender: TObject;
  const AInitiator: IADStanObject; var AException: Exception);
begin
  ADConnection1.Rollback;
end;

QR6: When I am executing this script on FB I get “Unexpected end of command line 3” ? How to fix this ? 

EXECUTE BLOCK 

AS 

DECLARE VARIABLE MYVAR VARCHAR(250); 

BEGIN 

… 

END 

; 

A: To execute a script with blocks you should change the command separator before a block and optionally return it back after. For example: 

SET TERM #; 

EXECUTE BLOCK 

..... 

END; 

# 

SET TERM ;#