SQL Scripts Questions (FireDAC)

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to the SQL Script execution.

Q1: Why can't I use semicolon at the end of a query? If I remove it from the query, it works

A: The ';' in many SQL script dialects is a delimiter of the SQL commands and is not a part of the SQL language. Some DBMSs allow ';' at the end of SQL commands, others do not. In other words, just remove ';'.

Q2: Some DBMSs (PG, Oracle, FB) allow me to execute only one SQL statement, but SQLite allows multiple statements to be executed at once. Why is that?

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

Q3: Even if I have more than one script, with ExecuteAll only the first one will be executed

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

with FDScript1.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 the next script command starting from TFDScript.Position position. ExecuteAll executes the script in full. There is also ValidateAll / ValidateStep. These methods process the script, but do not execute SQL commands. ValidateAll call assigns a value to TFDScript.TotalJobSize. So, the next ExecuteAll call will correctly update TFDScript.TotalPct10Done, which is 10% of processed script commands.

Q4: Is there a difference in performance between (1) executing FDConnection.ExecSQL for each SQL statement (command) and (2) executing FDScript.ExecuteAll?

A: The SQL execution code behind each of the methods is the same. They use the IFDPhysCommand.Execute method.

The TFDScript parser is highly optimized, it recognizes many SQL dialect aspects, like an Oracle PL/SQL (where ';' may be in the middle of a command), and allows precise and flexible control.

So, if you submit one command at a time to the FDConnection.ExecSQL and there is no need to extract these commands from a script or something else, then it will be the fastest method. If you have a SQL script in a file, then FDScript.ExecuteAll will be the fastest method.

Q5: How can I rollback a script work when it fails?

A: 1) Use FireDAC transaction control:

FDConnection1.StartTransaction;
try
  FDScript1.ExecuteAll;
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

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

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 TFDScript.OnError:

procedure TForm1.FDScript1Error(ASender: TObject;
  const AInitiator: IFDStanObject; var AException: Exception);
begin
  FDConnection1.Rollback;
end;

Q6: When I execute the script below on FB, I get “Unexpected end of command line 3”. How can I 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 ;#