TIBCConnection.InTransaction

[bendaniel]

We're evaluating IBDAC at the moment as a replacement for the BDE in our application suite spanning hundreds of thousands/millions of lines of code. Mostly going really well until we noticed this big difference in behaviour:

With BDE, if you haven't started a transaction (TDatabase.StartTransaction) and you open a dataset (like TQuery.Open), an implicit transaction is used, not the explicit one referenced by TDatabase. That is, TDatabase.InTransaction is False.

In IBC however, opening a query seems to automatically flick on TIBCConnection.InTransaction! This is a major problem for us as our code assumes that TIBCConnection.InTransaction is only True if we've explicitly started a transaction using TIBCConnection.StartTransaction.

Is there anyway to make it behave like the BDE does? Perhaps something like TIBCConnection.InExplicitTransaction or similiar?

[bendaniel]

We're also evaluating SQL Direct but honestly IBDAC has more features and I'd prefer to move to it if the conversion is smooth enough.

Unfortunately if we don't have a way around this difference in behaviour between BDE and IBDAC we won't be able to justify rewriting all our application logic and have to go to SQLDirect instead. But if there's a way we could change IBDAC, either using an option I don't yet know about or by having a layer in between our application code and IBDAC, so that IBDAC appears to behave more like the BDE with regard to transactions then that would be fine.

I already wrote our own Migration Tool and set of TIBCConnection, TIBCQuery, TIBCTable and TIBCStoredProc descendants that our Tool converts our BDE components to. (Unrelated: These descendants implement DatabaseName properties as this was something we needed, just trust me). So I don't know if it's possible to extend these somehow to get TIBCConnection.InTransaction to behave like the TDatabase version does? I thought of maybe overriding the Open, Close, Execute, etc methods on these descendants and controlling which transactions they use that way but I don't think they were virtual methods able to be overriden. :/

Unfortunately the time I'm able to spend on evaluating this product is coming to an end and sadly we may just have to go to SQLDirect if I can't get IBDAC to behave like BDE, either Today or Tomorrow.

[AndreyZ]

Hello,

To avoid the problem, you can create your own connection and transaction classes inherited from IBDAC ones and implement the InExplicitTransaction property on your own. Here is en example:

Code: Select all

TMyIBCConnection = class(TIBCConnection)
private
  function GetInExplicitTransaction: boolean;
public
  property InExplicitTransaction: boolean read GetInExplicitTransaction;
end;
TMyIBCTransaction = class(TIBCTransaction)
end;

function TMyIBCConnection.GetInExplicitTransaction: boolean;
var
  i: integer;
begin
  for i := 0 to TransactionCount - 1 do begin
    Result := TMyIBCTransaction(Transactions[i]).FExplicitlyStarted and
      TMyIBCTransaction(Transactions[i]).DetectInTransaction;
    if Result then
      exit;
  end;
  Result := False;
end;

[bendaniel]

Thanks though I'm not sure how in my TMyIBCTransaction I would know when the transaction is being started implicitly or explicitly? I.e. How would I know when to set TMyIBCTransaction.FExplicityStarted to True or False?

[AndreyZ]

You don't need to set the FExplicitlyStarted variable, it is set automatically by IBDAC when the StartTransaction method is called.

[bendaniel]

That's excellent and promising. Thanks for your help.

Unfortunately I've quickly run up to the next problem which is that while InTransaction in my custom TIBCConnection descendant now behaves like TDatabase, TIBCConnection.StartTransaction does not behave like TDatabase.StartTransaction.

TDatabase.StartTransaction will start the one global explicit transaction, even if implicit transactions have started, but in TIBCConnection, StartTransaction errors if implicit an transaction has already been started.

If there's a similar way I can mirror the BDE's StartTransaction behaviour in my TIBCConnection descendant I'd love to know.

[AndreyZ]

To avoid this problem, you should use different transaction components for connection and datasets. Here is an example:

Code: Select all

IBCConnection.DefaultTransaction := IBCTransaction1;
IBCConnection.Open;
IBCQuery.Transaction := IBCTransaction2;
IBCQuery.Open;
IBCConnection.StartTransaction; // here you will not have an error

[bendaniel]

Please remember that what we're trying to achieve here is the same behaviour that we have with the BDE with no change to our application code. We have literally thousands of forms that access our database so we can't afford our db suite to behave differently to the BDE requiring a rewrite of the logic in our forms.

To be specific and define the BDE-like behaviour we need: If a query, table or stored procedure is prepared, opened, executed etc while the connection is not in an explicit transaction, then we want it to use it's own implicit transaction.

But if a query, table or stored procedure is prepared, opened, executed etc while the connection is in an explicit transaction, then we want it to use that explicit transaction.

So if the only way to achieve this is by manually swapping which transaction to use on the query, table or stored proc descendant depending on whether or not an explicit transaction has started, then we need to be able to hook into the prepare, open, exec, etc methods on our dataset. I'm pretty sure not all these methods are able to be overridden. :/

The plan is tomorrow we'll end up buying the source for a single license so I can have a proper look for myself what can be done to getting this to work like an actual BDE replacement. If I succeed or you can help us out and IBDAC continues to prove itself a good replacement, we would end up buying licenses for our entire team. If I can't get it to behave the same, then I'm afraid we'll have to go the SQL Direct route, which so far is proving less feature rich but more BDE compatible than IBDAC.

[AndreyZ]

You should use one transaction for connection and for the update, insert, and delete operations of your datasets, and another transaction for the read operation of your datasets. Here is an example:

Code: Select all

IBCConnection.DefaultTransaction := IBCTransaction1;
IBCConnection.Open;
IBCQuery.Transaction := IBCTransaction2;
IBCQuery.UpdateTransaction := IBCTransaction1;
IBCQuery.Open;
IBCConnection.StartTransaction;
IBCQuery.Edit;
IBCQuery.FieldByName('fieldname').AsString := 'test';
IBCQuery.Post;
IBCConnection.Commit;

In this case dataset uses its own implicit transaction for reading data from a server, but it uses explicit connection transaction for all other data operations. This behaviour is similar to BDE's.

[bendaniel]

Thanks for your help Andrey, but it's still not the desired BDE behaviour I've been describing.

I did get close to achieving BDE behaviour by overring the StartTransaction, InTransaction, Commit and Rollback of TIBCConnection.

In StartTransaction I would iterate through all the datasets and set their transaction property to my own TIBCPConnection.FExplicitTransaction private TIBCTransaction field and then start it. This way all queries were forced to use the explicit transaction when one was started explicitly. Then in the Commit and Rollback methods I would iterate through all the datasets again and change their transaction properties back to TIBCPConnection.DefaultTransaction. InTransaction simply returned whether FExplicitTransaction was active or not.

Doing this comes very close to replicating the BDE's transactional control, but there are some minor differences which I couldn't get around. For instance, when swapping transactions for a dataset, or commiting or rolling back a transaction, the dataset using that transaction is closed down, so in my StartTransaction, Commit and Rollback methods I also need to remember which record the dataset was on by bookmarking it, then letting it close down, then manually reopening it and going to the bookmark. But I can't get around the fact that dataset is closing and reopening, as the BeforeClose, AfterClose, BeforeOpen & AfterOpen events are firing and if the query is a slow one to run, then it'll run slow again. Perhaps the BDE has to do the same thing but it does it internally and hides the fact well (not firing off open/close events for example).

Because we have hundreds of data-aware screens and millions of lines of code I feel I can't risk having transactions and datasets behave any differently than they did in the BDE as there may be subtle but potentially harmful side-effects.

So as great a framework as it is and as much as I wish we could move to it, IBDAC probably isn't right for us. Thanks for all your help.

[AndreyZ]

InterBase and Firebird require an active transaction for any operation under data. When you explicitly start transaction and rollback or commit it, dataset will be closed (because there will be no active transaction). To avoid this problem, you can use the CommitRetaining and RollbackRetaining methods instead of the Commit and Rollback methods. The CommitRetaining and RollbackRetaining methods retain the transaction context that allows dataset to remain active.

[RadekCervinka]

You should use one transaction for connection and for the update, insert, and delete operations of your datasets, and another transaction for the read operation of your datasets. Here is an example:

Code: Select all

IBCConnection.DefaultTransaction := IBCTransaction1;
IBCConnection.Open;
IBCQuery.Transaction := IBCTransaction2;
IBCQuery.UpdateTransaction := IBCTransaction1;
IBCQuery.Open;
IBCConnection.StartTransaction;
IBCQuery.Edit;
IBCQuery.FieldByName('fieldname').AsString := 'test';
IBCQuery.Post;
IBCConnection.Commit;

In this case dataset uses its own implicit transaction for reading data from a server, but it uses explicit connection transaction for all other data operations. This behaviour is similar to BDE's.

I follow this code and there is a problem with rollback, rollback don't work.

Code: Select all

var
  tranUpdate, tranRead: TIBCTransaction;
  IBCQuery: TIBCQuery;
  foConnection: TIBCConnection;

procedure Connect(const sDB, sUser, sPwd: string);
begin
  foConnection := TIBCConnection.Create(nil);
//  ... cut
end;

begin
  try
    Connect('c:\temp\testdac\test.FDB', 'SYSDBA', 'MASTERKEY');
    tranUpdate := TIBCTransaction.Create(foConnection);
    tranRead := TIBCTransaction.Create(foConnection);
    IBCQuery := TIBCQuery.Create(nil);
    IBCQuery.SQL.Text := 'SELECT idTable, dlMax FROM tTable WHERE idTable = 1';

    tranRead.DefaultConnection := foConnection;

    foConnection.DefaultTransaction := tranUpdate;
    foConnection.Open;

    IBCQuery.Transaction := tranRead;
    IBCQuery.UpdateTransaction := tranUpdate;
    IBCQuery.Open;
    foConnection.StartTransaction;
    IBCQuery.Edit;
    // value in DB  -3
    IBCQuery.FieldByName('dlMax').AsInteger := -3;
    IBCQuery.Post;
    foConnection.Rollback;
    // value in DB = -3 !
    foConnection.Free;

Rollback don't reverse changes. This is bad for me. I want only similar behaviour to ADO - only manual transaction else autocommit.

I tested UniDAC and there are transaction OK, but at the end I bought only IBDAC and discovered this behaviour

emabedded FB 2.5.1

[AndreyZ]

To solve the problem, you should set the IBCQuery.AutoCommit property to False.