I'm struggling with trying to learn the proper way to use TIBTransaction. I've always used Delphi and DevArt components so this was always taken care of in the background. I have 3 buttons on a form... Run, Commit, Rollback. I run an SQL statement using the IBXScript parser. I'm following the IBX Script example. I'm trying to make an "Execute SQL Statement" window just like what is in FlameRobin, which has a "play" (F4) button, a "commit"
F5 button, and a "rollback" F8 button. I cannot make this work because once the transaction has been opened and commited or rollbacked ONCE, it never again fires the OnStartTransaction event nor the AfterTranscactionend event. What am I doing wrong? I need to have these two events fire reliably each time a transaction is started or ended.
I have attached a sample project. Click the "Help" link in the project for more explanation.
Platform: Lazarus 1.8.1, FPC 3.0.5, IBX 2.2.0
The difference is that IBX doesn't autostart transactions by default at runtime - but it does in the IDE. And TIBScript doesn't have the AllowAutoTransactionActive property that is in datasets.True, but the example still exposes a bug.
TIBTransaction has worked the way it does since Borland Delphi.Yes, TIBTransaction works fine. But it has events that are not triggered correctly. I also use TIBTransaction massively in Delphi and I've never used the events of TIBTransaction. B.T.W. these events arent even in IBX for Delphi. But in IBX for Lazarus they are there (and they are faulty). I'm talking specifically about TIBTransaction.OnStartTransaction.
Ok, so the proper way to use transactions is to close and reopen the database connection? Seems like a lot of extra unnecessary overhead to be connecting and disconnecting to the database, perhaps 50,000 times a day for every mouse click of an app. Versus just closing and re-issuing an SQL statement in a TQuery (or descendant)No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes. Firebird is a transaction oriented database (same as Oracle for that matter) and transaction isolation is how you manage multi-user database updates. IBX gives you full control over Firebird transactions and allows you to have as many active transactions as you need: anything from one to some ridiculously large number. As with any transaction oriented database until you commit a transaction any updates you make are not visible to other users and they are locked out from that table/record. So you need to commit fairly regularly. Not committing also means that you could lose your updates if you (e.g.) lose your connection to the database.
No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes.Did you read my post? The problem is when you do a TIBTransaction.Active := true a second time (after you closed a transaction with a .Commit or .Rollback) the TIBTransaction.OnStartTransaction event isn't triggered anymore. That's the entire problem.
If you believe that transaction control is not for you and you never need to worry about multi-user access then maybe you should be using something like MySQL instead.RedOctober is trying to (re)create a Database manager (like IBExpert and FlameRobin) for Firebird. Therefore manual transaction control would be essential. In his example the enabling of the buttons Commit/Rollback are controlled by the new events from TIBTransaction (which don't work after the first time).
TIBTransaction has worked the way it does since Borland Delphi.Yes, TIBTransaction works fine. But it has events that are not triggered correctly. I also use TIBTransaction massively in Delphi and I've never used the events of TIBTransaction. B.T.W. these events arent even in IBX for Delphi. But in IBX for Lazarus they are there (and they are faulty). I'm talking specifically about TIBTransaction.OnStartTransaction.
So they either should be removed from TIBTransaction or fixed.
It has nothing to do with TIBSQL and the given example is somewhat misleading. You can look at it much simpler. With the following lines you would start and end a transaction. But with the second and following Active := true TIBTransaction.OnStartTransaction is NEVER executed again. And that doesn't happen.
IBTransaction1.Active:= true; // OnStartTransaction is executed IBTransaction1.Active:= false; // can also be .Commit IBTransaction1.Active:= true; // Now it doesn't execute anymore IBTransaction1.Active:= false; IBTransaction1.Active:= true; IBTransaction1.Active:= false;
Yes I did read your post and responded to it separately. This is a response to RedOctober's post and correcting his assertion about re-opening database connections.No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes.Did you read my post? The problem is when you do a TIBTransaction.Active := true a second time (after you closed a transaction with a .Commit or .Rollback) the TIBTransaction.OnStartTransaction event isn't triggered anymore. That's the entire problem.
I already diagnosed this problem and I am very familiar with Firebirds transaction system.
You could say you don't need TIBTransaction.OnStartTransaction at all (I myself don't use it because in Delphi these events don't even exist). But when they are there (and they are added in IBX for Linux) they should work correctly.QuoteIf you believe that transaction control is not for you and you never need to worry about multi-user access then maybe you should be using something like MySQL instead.RedOctober is trying to (re)create a Database manager (like IBExpert and FlameRobin) for Firebird. Therefore manual transaction control would be essential. In his example the enabling of the buttons Commit/Rollback are controlled by the new events from TIBTransaction (which don't work after the first time).
Thanks for pointing out the problem with the OnStartTransaction event. The problem was introduced with an optimisation in IBX 2 and also meant that the ISQL Monitor call was also lost. Already fixed in the development version.Ok, thanks.
I have added the required line to fix the bug and look forward to getting the next update of IBX from MWA.Be careful what you wish for, the next version will probably have a re-architectured version of the IBServices components -hopefully much easier to use a:d understand. The older version will continue to be available given that it is a radical change. If you want to see an early version then I can make one available.