A SQL script is a set of separated SQL, execution control and logging commands. The SQL scripts are useful for the backend maintenance tasks, such as backend SQL objects creation, dropping, upgrading, initial data loading and so on. 

Many DBMS's allow to execute few SQL commands in a single TADQuery.ExecSQL call as a batch, but with limitations. The differences between SQL script and SQL command batch are:

• script allows to use all possible SQL commands in single script. The batch may have limitation, depending on a DBMS. For example, Oracle anonymous PL/SQL block cannot contain a DDL commands.
• script may be split into few transactions. The batch must be performed in a single transaction.
• script allows to use non-SQL and custom commands. The batch may include only commands understanding by a DBMS.
• script may be split into subscripts. The batch may call stored procedures as a separated code blocks.
• script execution is fully controlled by the client. The batch execution is controlled only by the DBMS.
• script execution may be logged. The batch execution does not.
• script provides execution progress feedback. The batch execution does not.

TADScript has many advantages over standard utilities, like the ability to be completely integrated into the AnyDAC application and the ability to extend the set of commands by the custom script commands. The TADScript component is aware of several industry standard SQL script syntaxes, including:

• Oracle SQL*Plus;
• Microsoft ISQL / OSQL;
• MySQL mysql.exe / mysqldump.exe;
• Firebird / Interbase ISQL.

For example, the following Firebird script creates a database, and may be executed using TADScript: 

SET SQL DIALECT 3;
SET NAMES UTF8;
SET CLIENTLIB 'C:\fb25\bin\fbclient.dll';
CREATE DATABASE 'E:\Test2.ib'
  USER 'sysdba' PASSWORD 'masterkey'
  PAGE_SIZE 16384
  DEFAULT CHARACTER SET NONE;

SET TERM ^ ;

CREATE PROCEDURE MY_PROC RETURNS (aParam INTEGER) AS
BEGIN
  aParam = 10;
END^

TADScript allows to execute a script from a file pointed by SQLScriptFileName, if it specified. Otherwise from a script with a zero index from the SQLScripts collection. Note, a file with SQL script may be executed also using the ADExecutor utility. For example, to execute a script file: 

with ADScript1 do begin
  SQLScriptFileName := 'c:\create.sql';
  ValidateAll;
  ExecuteAll;
end;

To execute a script in a memory: 

with ADScript1 do begin
  SQLScripts.Clear;
  SQLScripts.Add;
  with SQLScripts[0].SQL do begin
    Add('INSERT INTO Brands VALUES (1, ''Audi'')');
    Add('INSERT INTO Brands VALUES (2, ''BMW'')');
  end;
  ValidateAll;
  ExecuteAll;
end;

Also there are few other methods, simplifying SQL script execution. You can control many other script execution aspects as from a Delphi code using ScriptOptions, as using corresponding script control commands. 

The script may also call other scripts, like a subroutines, using @ <script>, @@ <script>, START <script> or INPUT <script> commands. There <script> is either the item name from the SQLScripts collection, either the external file name. For example, the 'root' script executes 'first' and 'second' subscripts: 

with ADScript1.SQLScripts do begin
  Clear;
  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;
ADScript1.ValidateAll;
ADScript1.ExecuteAll;

A SQL script may be executed as in full with a subscripts, using ExecuteAll method. As in step-by-step mode, using ExecuteStep method. The last method is useful for the GUI applications, allowing to execute add-hoc queries. The next command will be extracted and executed from the Position position in the script. To abort the script execution call the AbortJob method. 

Each SQL command must be terminated by a command separator. The separator default value is a ';' and for MS SQL Server it is 'GO'. A control command does not need to be terminated by a command separator. The separator may be changed as from a Delphi code using CommandSeparator option, as from a SQL script using SET CMDSEP <sep> or DELIMiter <sep> commands. For example for SQL Server: 

INSERT INTO Brands VALUES (1, 'Audi')
GO
INSERT INTO Brands VALUES (2, 'BMW')
GO

For Oracle: 

INSERT INTO Brands VALUES (1, 'Audi');
INSERT INTO Brands VALUES (2, 'BMW');

Using a custom separator: 

SET CMDSEP #
INSERT INTO Brands VALUES (1, 'Audi')#
INSERT INTO Brands VALUES (2, 'BMW')#

When a script contains a DB programming language commands or blocks, then additional consideration must be made:

For example with Firebird: 

SET CMDSEP #;
EXECUTE BLOCK ... #
SET CMDSEP ;#
INSERT INTO Brands VALUES (3, 'Mercedes');

Otherwise application may raise an error likes this: 

[AnyDAC][IB] Unexpected end of command line 3

A SQL script may reference to:

• parameters from the TADScript.Params collection. To define a parameter in script use VARiable <name><type>=<value> command. To define a parameter in the code add a parameter to the TADScript.Params before script execution.
• macros from the TADScript.Macros collection. To define a macro in script use DEFine <name>=<value> command. To define a macro in the code add a macro to the TADScript.Macros before script execution. To enable macro processing set MacroExpand to True (default) or execute SET DEFINE ON or SET SCAN ON.
• arguments using &<argument number> syntax. The arguments may be specified in the TADScript.Arguments property, as an argument to the TADScript.ExecuteFile or ExecuteScript methods, or as part of the @ / @@ commands.

For example to define and use parameters: 

with ADScript1.SQLScripts[0].SQL do begin
  Add('VARIABLE name CHAR IN = ''aaa''');
  Add('VARIABLE id NUMBER INOUT');
  Add('INSERT INTO master (name) VALUES (:name) RETURNING id {INTO :id};');
  Add('INSERT INTO detail (fk_id, name) VALUES (:id, ''bbb'');');
end;

To define and reference a macro: 

DEF tab=Brands
INSERT INTO !tab VALUES (1, 'Audi');
INSERT INTO !tab VALUES (2, 'BMW');

To define and use arguments: 

ADScript1.Arguments.Add('Brands');
...
with ADScript1.SQLScripts[0].SQL do begin
  Add('INSERT INTO &1 VALUES (1, ''Audi'')');
  Add('INSERT INTO &1 VALUES (2, ''BMW'')');
end;

To produce an execution log, you can enable spooling as from Delphi code using SpoolOutput and SpoolFileName options, as from a SQL script using SPOol <name> or OUTput <name> commands. The content of the spool output is controlled by the EchoCommands, FeedbackCommands, AutoPrintParams, FeedbackScript, IgnoreError, Timing, ColumnHeadings, PageSize, ServerOutput options. 

You can use TADGUIxScriptDialog component to allow to a script execution engine to communicate with an end-user using a dialog. To interact with the end-user TADScript uses events, like the OnConsoleGet, OnConsolePut, OnGetText, OnPause, etc. And this dialog provides a standard implementation for these events. To show an execution progress, TADScript needs to know the total length of all scripts to execute. For that call the ValidateAll method before starting a script execution. 

The list of known TADScript incompatibilities with the original scripting utilities:

• Firebird ISQL works in non auto commit mode. TADScript / TADConnection by default has auto commit mode turned on. For better compatibility set ADConnection.TxOptions.AutoCommit to False before script execution. Or execute SET AUTOCOMMIT OFF script command.
• Microsoft ISQL outputs the PRINT command result. TADScript / TADConnection by default does not. To enable PRINT output set ResourceOptions.ServerOutput to True. Or execute SET SERVEROUTPUT ON script command.

TADScript, ADExecutor, SQL Script Control Commands, Developing Custom Commands