Jacob wrote:
I'm using IBO as a replacement to BDE in my delphi application.
In converting my code I've come to a problem where I need to understand FB transactions better.
In my code I need to update the metadata and data of an already running database on costumer sites.
What I would like to happen, is that the entire update and is contained within one single transaction.
As an example (From Helen Borries book):
ALTER TABLE PERSONNEL
ADD TEMP_COL VARCHAR(18);
COMMIT;
UPDATE PERSONNEL
SET TEMP_COL = CAST(TEL_NUMBER AS VARCHAR(18))
WHERE TEL_NUMBER IS NOT NULL;
COMMIT;
...
There are 2 commits, and it seems to be necessary? No way of using nested transactions here?
My problem isn't exactly with the above code, but something similar that involves altering a table, adding some data to the table, and then again altering tables, adding constraints etc in the end.
I hoped it would be possible to encapsulate it all in one transaction, so that if something goes wrong, I'll be able to do a ROLLBACK, and things will be as they were before.
What the best way to handle this kind of thing?
Ann W. Harrison answers:
What I would like to happen, is that the entire update and is contained within one single transaction.
Unfortunately, you can't. Firebird defers metadata changes to commit time, so you can't, for example, create a table and store records in it in a single transaction.
No way of using nested transactions here?
The concept of "nested transactions" is a little slippery. You can use savepoints so you can back out part of a transaction, but you can't commit part of a transaction before the whole transaction commits.
My problem isn't exactly with the above code, but something similar that involves altering a table, adding some data to the table, and then again altering tables, adding constraints etc in the end.
Unfortunately, you can't do that with Firebird. You'll need two separate transactions, one for the first alter table, then one for the data and the second alter table. Three transactions would be better - metadata, data, then metadata.
What the best way to handle this kind of thing?
Build a script or program that knows how to undo each of the steps.