Dustin T Waling
-
Content Count
3 -
Joined
-
Last visited
Posts posted by Dustin T Waling
-
-
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.
-
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.
TFDScript.ExecuteALL does not seem to behave as expected
in Databases
Posted · Edited by Dustin T Waling
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:
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.