FireDAC TFDScript component - Bug or Feature?

When switching a project from using the Spring4D ORM to hcOPF I was forced to change the implementation of the CreateDatabase functionality that was so easy to implement using Spring4D.  Now I am using a TFDScript component with multiple scripts which naturally have to match the MetaData definitions used by hcOPF, but cannot be easily derived from that MetaData.

I dropped a FireDAC TFDScript component in my datamodule and added 3 named scripts:

TFDScript with 3 SQL Scripts

TFDScript with 3 SQL Scripts

Then in my CreateDatabase method I added code to execute each script like the following:

  Script := FDScript.SQLScripts.FindScript('CreateDatabaseContent');
  FDScript.ExecuteScript(Script.SQL);

Much to my surprise only the first script was executed followed by an AV, so I started tracing into the code only to find that the very first line of code in the ExecuteScript() method calls SQLScripts.Clear!  This means you cannot use the SQLScripts collection for any more than a single SQLScript at a time if you make this method call.  So why then would it be a public method?  Why would it even exist?  Why isn’t there an FDScript.ExecuteScriptByName(’CreateDatabaseContent’) method?

procedure TFDScript.ExecuteScript(const AScript: TStrings);
begin
  SQLScripts.Clear;
  SQLScripts.Add.SQL := AScript;
  SQLScriptFileName := '';
  ValidateAll;
  if Status = ssFinishSuccess then
    ExecuteAll;
end;

According to my interpretation of the documentation ExecuteAll should execute all scripts in the SQLScripts collection in the order in which they are defined. Using the FireDACMonitor and tracing through the code showed it only executed the first script (index 0).  As a result, my ugly hack to make the component work they way it should (correct me if I am wrong here) is:

  for I := FDScript.SQLScripts.Count -1 downto 0 do
  begin
    FDScript.ExecuteAll;
    FDScript.SQLScripts.Delete(0);
  end;

Either I am missing something fundamental, or the TFDScript component is badly broken and/or documented. I have created RSP-26131 in case I haven’t lost my mind, so please vote for it.

3 Responses to “FireDAC TFDScript component - Bug or Feature?”

  1. Uwe Raabe Says:

    The documentation of ExecuteAll describes exactly the seen behavior:
    “it allows you to execute a script from the SQL property of the item with a zero index from the SQLScripts collection.”

    Your expectation may be wrong. The first script (that with index 0) is the main script and that one is executed. The other entries in SQLScripts can be called by name from the main script.

    ExecuteAll executes the complete main script, in contrast to ExecuteStep, which executes just the next command inside the main script.

  2. Schokohase Says:

    There is no need to have a look at the code.

    It is documented

    http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Comp.Script.TFDScript.ExecuteScript

    “Use the ExecuteScript method to execute an SQL script contained in the string list.”
    The string list referenced the AScript argument of the method.

    Maybe they should have mentioned that the SQLScripts property will be erased, but in fact they did by showing the code in the docs.

  3. Larry Hengen Says:

    @Update,

    EMBT did accept the bug report and have fixed the component for the release of Rio 10.3.3. According to them, “The fix will preserve existing SQL scripts in SQLScripts collection when these methods are executed:

    procedure TFDScript.ExecuteFile(const AFileName: String);
    procedure TFDScript.ExecuteScript(const AScript: TStrings);”

Leave a Reply