TFDScript.ExecuteALL does not seem to behave as expected

[Dustin T Waling 0]

Hello!

I am struggling a little with the TFDScript object, and it does not seem to behave the way I would expect.  I have tried multiple variations to the object properties in an attempt to make this work, but I expect I must be missing something.

I simply want to execute a set of scripts that work together - the purpose of these are for our DB upgrades, so something like creating, or altering a Table requires a series of steps.  So, this is what I chose the TFDScript object for.  And, I can successfully run Script.ExecuteAll without error, however the SQL changes are not committed in the database.  This does not matter if it is a DDL script or a data script, and I have tried using several different terminators, changing the timing of the commit (even including commit statements among the scripts), modified transaction handling, etc., etc....

 

So, I am still unable to simply run Script.ExecuteAll to get the intended result and have resorted to creating a function that loops through the scripts within a TFDScript object and executes each one individually.  This seems a bit much as my understanding is that this is exactly what ExecuteAll should be doing.

Below is the function I am using, and as you can see I am not doing any crazy fancy footwork to get the job done, it is very simple.  I have even tried keeping the function and replacing only the for loop with ExecuteAll (also tried calling ValidateAll before ExecuteAll).

 

- so, my question is why does this simple for loop work but ExecuteAll does not?

function TdmBaseDBUpgrade.ExecuteFbScriptAll(aScript: TFDScript): boolean;
var
  i: integer;
begin
  aScript.Connection := Database;
  aScript.Connection.StartTransaction;

  try
    for i := 0 to (aScript.SQLScripts.Count -1) do
    begin
      if (UpperCase(aScript.SQLScripts[i].Name) <> 'ROOT') and (aScript.SQLScripts[i].SQL.Text <> '') then
        aScript.ExecuteScript(aScript.SQLScripts[i].SQL);
    end;

  finally
    if aScript.TotalErrors > 0 then
    begin
      Result := False;
      aScript.Connection.Rollback;
    end
    else
    begin
      Result := True;
      aScript.Connection.Commit;
    end;
  end;
end;

 

Ideally if we can get the more "automated" script handling working, then this would allow us to simplify a great deal of code in other places as well.

[Uwe Raabe 2057]

Perhaps you are just expecting something the method isn't intended for. ExecuteAll doesn't execute all scripts in the SQLScripts collection. It only executes the first one (the main script), while the others are meant as subscripts to be called by name from the main script or other subscripts. This is an example from the docs:

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;

That said, TFDScript is not a container for scripts to be executed in sequence by calling ExecuteAll. That has to be done by your own code.

[Dustin T Waling 0]

Thank you for the quick response! 

 

I suspected I was not fully understanding the point of the object, and the documentation isn't completely clear on this specific point.  However, your answer is interesting to me because this approach with using a "root" subscript is in fact one of the very first I tried, and came back to a few time later.  But even this way I was not able to get the SQL changes to stick, so I feel I must still be overlooking something.  I have tried modifying the auto parse properties as well, which did not appear to make a difference.  If I recall, an exception will be raised when the SQL parser crosses @scriptname1 and @scriptname2 in the root subscript if the param/macro auto-parse is off.  So, my result was the ultimately the same - process runs without exception, but the resulting DB does not contain the changes from the scripts that were run.

 

The one difference from the example is that I am trying to populate the SQL strings directly in the subscripts within the collection - it seems this would ultimately be one of the more helpful points for using TFDScript.  Otherwise having to explicitly define each subscript in code would not be much different than the old way of using a generic (single script) SQL object with only a few extra lines to clear and execute in between defining each SQL statement.  Perhaps I am just being a bit obtuse, or maybe wishing for things I don't see because I'm just looking in the wrong place.

 

I say all this as I am trying to be clear that I've followed the basic recipes from documentation, as well as several other approaches and variations, yet am unable to get the expected result.  So, I am left with the only other variable I can think of at the moment - We are using Firebird (and there are a number of possible DB type that can be used).

I am unaware of any special considerations aside from making sure to use proper command separator, which is a familiar thing with Firebird anyway.  Yet, I know Firebird is not necessarily the most commonly used database, so could it be something related to using TFDScript with Firebird - e.g.: going up the chain into some connection or transaction properties that might need to be a little different in this use case?

 

 

P.S.: I must note that my main reasoning for digging into this is that I am attempting to simplify the maintenance of our database upgrade tool as much as possible so other devs on our team can assist with adding update steps in the tool without having the need for DBA level understanding of our database.  This includes the minimizing the risk of mistyping the SQL that is to be implemented when adding it via code - in this case they can take the new SQL directly from our DBAs and drop it into the FireDAC object (and in the sequence prescribed for the case of using the TFDScript object).

 

Or maybe I need to go an entirely different route to accomplish my goal.  I am more than happy to accept I am wrong if have other possibilities to explore 🙂  so any suggestions are welcomed.

Edited August 11, 2022 by Dustin T Waling

[weirdo12 19]

Are you setting the TFDScript.ScriptOptions.CommandSeparator property?

 

Are all SQL statements terminated?

 

sql_script->SQL->Add("ALTER TABLE \"dba\".truck RENAME dsptch15_truck;");

 

What does the code you a using look like?

[weirdo12 19]

My previous post was way off base. I'm still interested why you've had so much trouble getting TFDScript  to work as you expected. I use  it it with SQL Anywhere, SQLite, SQL Server, PostgreSQL and MySQL.

[Dustin T Waling 0]

No worries, Yep, proper terminators and all of that fun stuff. 

Also a note for anyone else reading this - we are currently in the process of migrating from D6 up to D10.4.  As such, we are in unfamiliar territory with all the fancy new and updated components - the new stuff is cool and fun, but there is certainly no shortage of chin-scratchers.

 

I've been banging my head on this for a while, and I have found a few things that make it behave.  The real hurtle was a mental one, in that I kept tripping up on the idea that "it must be" this way or that... it wasn't.

In the end, in a way it was my own hubris that got me.

 

Here is a summary:

• Based on the examples in documentation and what everyone else's experience seemed to be, the thing should just work. So, the number of properties I needed to tweak was much more than I expected - not a large number, but definitely more than expected. 
  • So, I was stuck because "This should just work with the default properties, so my scenario can't be far from default..." and I was not going beyond testing one or two property changes at a time.  (...after all, k.i.s.s., right!)
  • I was starting to feel like that office jockey asking IT for help and the tech on the line keeps saying "Did you try rebooting." - lol - so, this time around I tried "mashing the keypad," as it were, or the big hammer approach as a friend calls it.
• Another thing that was teased out, only after I started flipping all switches & turning all knobs, was that the SQL itself was the root of a fair bit of my troubles.  I'm not sure why there wasn't a clear indication before, but I'm guessing it must be one of the transaction behaviors I turned on - as it turns urns out, we have a decent amount of non-typical SQL/DDL (well, less typical than the out-of-the-box FireDAC components account for anyway)
  • We have a few very long strings in some of the data prep scripts and other places
  • We also establish a fairly lengthy list of constants which are consumed by a few platforms - so the is a lot going on in some of those varchars and blobs
  • It took a bit to discover what specifically was going on, but it boiled down to string length and odd characters that Delphi was trying to parse unexpectedly (well, unexpected because I didn't realize, or think to check, that the characters were in there).

One great thing about Firebird SQL is its ability to handle a very large number of attachments and transactions without getting held up by locks.  And it does so extremely efficiently, so it makes the perfect SQL engine for a DB driven solution.  My pitfall was that I'm so used to having so much business process built into the database that I didn't think about there being the relatively wider scope of hiding places for the "oddballs" to pop out of.  Nevertheless, it now seems to function as needed - it only took setting 9 additional properties, instead of the one or two I was assuming "it should just work" with. 

Essentially, all the examples I was seeing were super simple, so I fooled myself into thinking that my solution had to be super simple as well - which meant I kept stopping short of where I need to go.  So, I am posting this in the hope that, even if only a small chance, this will help someone else. 

 

These are the additional properties that I needed to get it to work consistently for our database maintenance tool:

I've marked the additional property changes with "//**" in the code snip below

As noted above I tired several combinations of 1,2 or even 3, of these and many others (during the pull my hair out phase), but it took all 9 of these for it to work reliably - so I'll be making this the default property set for all of our DDL changes just to be safe.

  object AuthSystem_PopDATA: TFDScript
    SQLScripts = <
      item
        Name = 'root'
        SQL.Strings = (
          '@PSQLBlock_PROVIDER_PopData'
          '@PSQLBlock_SYS_PopData'
          '@PSQLBlock_AuthSettings')
      end
      // My predefined SQL scripts with the names listed above are included here ...
      // ...
    Connection = Database
    ScriptOptions.MaxStringWidth = 2000
    ScriptOptions.CommandSeparator = '^'
    ScriptOptions.ParamArraySize = 0  //**  Not sure why, but it seemed to be more reliable when I explicitly set this to 0
    ScriptOptions.MacroExpand = False //**
    ScriptOptions.Verify = True  //**
    ScriptOptions.RaisePLSQLErrors = True //**
    Params = <>
    Macros = <>
    FetchOptions.AssignedValues = [evMode]
    FetchOptions.Mode = fmAll
    ResourceOptions.AssignedValues = [rvParamCreate, rvMacroCreate, rvMacroExpand, rvParamExpand, rvEscapeExpand, rvCmdExecMode, rvDirectExecute, rvDefaultParamType]
    ResourceOptions.ParamCreate = False //**
    ResourceOptions.MacroCreate = False //**
    ResourceOptions.ParamExpand = False //**
    ResourceOptions.MacroExpand = False //**
    ResourceOptions.EscapeExpand = False //**
    ResourceOptions.DefaultParamType = ptUnknown
    Left = 124
    Top = 156
  end

 

 

Edited August 17, 2022 by Dustin T Waling

[weirdo12 19]

Thanks for following up on the issue you were having.