Illustrates the principles of ACID compliance and data integrity in multi-step database operations. This example demonstrates managing atomic blocks with 'BeginTransaction', 'Commit', and 'Rollback', showcasing robust error handling within 'try..except' constructs to prevent data corruption during partial failures.
uses System.Data;
var db := DataBase.Create('SQLite', [':memory:']);
db.Exec('CREATE TABLE Inventory (Item TEXT, Qty INTEGER)');
PrintLn('<h3>Transaction Management</h3>');
// 1. Successful Transaction
PrintLn('Executing Transaction A...');
db.BeginTransaction;
try
db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Apples', 50]);
db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Bananas', 30]);
db.Commit;
PrintLn('Transaction A committed.');
except
on E: Exception do begin
db.Rollback;
PrintLn('Transaction A failed: ' + E.Message);
end;
end;
// 2. Failed Transaction (Rollback)
PrintLn('<br>Executing Transaction B (will fail)...');
db.BeginTransaction;
try
db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Oranges', 100]);
// Force an error (Table "NonExistent" doesn't exist)
db.Exec('INSERT INTO NonExistent VALUES (1)');
db.Commit;
PrintLn('Transaction B committed.');
except
on E: Exception do begin
db.Rollback;
PrintLn('Transaction B rolled back due to error.');
end;
end;
// Verify state
PrintLn('<br><h4>Final Inventory:</h4>');
var ds := db.Query('SELECT Item, Qty FROM Inventory');
while ds.Step do begin
PrintLn(ds.AsString(0) + ': ' + IntToStr(ds.AsInteger(1)));
end;
PrintLn('<i>Note: Oranges are missing because the transaction was rolled back.</i>');
<h3>Transaction Management</h3> Executing Transaction A... Transaction A committed. <br>Executing Transaction B (will fail)... Transaction B rolled back due to error. <br><h4>Final Inventory:</h4> Apples: 50 Bananas: 30 <i>Note: Oranges are missing because the transaction was rolled back.</i>