Executing SQL Scripts (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)

Topics

Topic Description
SQL Script Control Commands TFDScript and the FDExecutor utility support extended list of the SQL script execution control commands.
Developing Custom Commands TFDScript allows extending the set of SQL script control execution commands.

General

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

Many DBMSs allow execute several SQL commands in a single TFDQuery.ExecSQL call as a batch, but with limitations. These are the differences between the SQL script and the SQL command batch:

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

TFDScript has many advantages over standard utilities, like the ability to be completely integrated into the FireDAC application and to extend the set of commands by the custom script commands. The TFDScript 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 can be executed using TFDScript:

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^

Executing the Script

TFDScript allows you to execute a script from a file pointed by SQLScriptFileName, if it specified. Otherwise, you can execute it from a zero-index script from the SQLScripts collection.

Note: Alternatively, execute a file with SQL script by using the FDExecutor utility.

For example, to execute a script file, use the following code snippet:

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

To execute a script in a memory, use the following:

with FDScript1 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 some other methods that simplify the SQL script execution. You can control many other script execution aspects as from a Delphi code by using ScriptOptions as using corresponding script control commands.

The script can also call other scripts, such as a subroutine, through the @ <script>, @@ <script>, START <script>, or INPUT <script> commands. In that case, <script> is either the item name from the SQLScripts collection, or the external file name. For example, the 'root' script executes the 'first' and 'second' subscripts:

with FDScript1.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;
FDScript1.ValidateAll;
FDScript1.ExecuteAll;

A SQL script can be executed as in full with a subscripts, using ExecuteAll method, as in step-by-step mode using the ExecuteStep method. The last method is useful for the GUI applications, allowing executing adhoc 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.

Separating Commands

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

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

For Oracle, use:

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

Use a custom separator in the following way:

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

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

DBMS SQL commands Description
Firebird
  • CREATE FUNCTION
  • EXECUTE BLOCK
Must be terminated by '/', or a separator different from ';' must be set.
Oracle
  • CREATE PROCEDURE / FUNCTION / PACKAGE / etc
  • BEGIN END
Must be terminated by '/', or a separator different from ';' must be set.
PostgreSQL
  • CREATE FUNCTION
  • DO
No action is required.

For example, with Firebird, use the following:

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

Otherwise, the application could raise an error, such as:

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

Using Parameters

A SQL script can reference to:

  • Parameters from the TFDScript.Params collection. To define a parameter in script, use the <code>VARiable <name><type>=<value></code> command. To define a parameter in the code, add a parameter to the TFDScript.Params before the script execution.
  • Macros from the TFDScript.Macros collection. To define a macro in script, the use <code>DEFine <name>=<value></code> command. To define a macro in the code, add a macro to the TFDScript.Macros before the 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 can be specified in the TFDScript.Arguments property, as arguments to the TFDScript.ExecuteFile or ExecuteScript methods, or as part of the @ / @@ commands.

For example, to define and use parameters, use the following code:

with FDScript1.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, use the following:

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

To define and use arguments, use the following:

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

Getting Feedback

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

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


Resolving Incompatibilities

Find below a list of known TFDScript incompatibilities with the original scripting utilities:

  • Firebird ISQL works in non-autocommit mode. By default, the autocommit mode for TFDScript/TFDConnection is turned on. For better compatibility, set FDConnection.TxOptions.AutoCommit to False before the script execution. Or execute the SET AUTOCOMMIT OFF script command.
  • Microsoft ISQL outputs the PRINT command result. By default, TFDScript/TFDConnection does not do that. To enable the PRINT output, set ResourceOptions.ServerOutput to True or execute the SET SERVEROUTPUT ON script command.