Clientdatasets and Transaction Processing....

[Dean]

I have noticed that when I use a TClientDataSet and provider connection to the DOA components, every time I do a post and applyupdates the TOracleDataSet does a commit. In many cases this is fine, but in several places I take control of the transaction processing and do not wish this to occur. I've tracked it down to the PSInTransaction() function which is used by the TDataSet to ensure that if you're already in a transaction, another one is not started.

My question is: Is there something I'm not setting correctly that would remove this commit? I've turned OFF the TOracleDataSet.CommitOnPost, and a commit still occurs. Just for fun I set the PSInTransaction function to return the Session.InTransaction function and that seemed to work ok.

 

[Marco Kalter]

Each call to TClientDataSet.ApplyUpdates will implicitly lead to a single transaction. If this call to ApplyUpdates leads to 10 posted records, then these 10 records will be committed or rolled back as 1 transaction.

This is how it currently works, and I don't think your suggested modification by using Session.InTransaction works 100% correct. We will have to look into this.

------------------
Marco Kalter
Allround Automations

 

[Dean]

I think that the way that you have set it up is logical because you can use the caching mechanism of the TClientDataSet as a way to handle transactions. Unfortunately, it also makes it impossible to handle transactions yourself, because a commit will occur after every ApplyUpdates(). Surely there must be a way to set up this functionality so that you can use this automatic feature without disabling the ability to explicitly control the transactions.

BTW, the Session.InTransaction seems to work for my special case, because I can not support nested transactions (we also support Interbase which could not have them).

[This message has been edited by Dean (edited 09 October 2000).]

 

[Dean]

Marco,

I've done some reading on the MIDAS architecture and it seems that they want the behavior to be like you described above. This sort of baffles me because it effectively makes it impossible to use transaction processing across multiple tables. Often, I have 3 different tables that I would like to update in a single transaction, and rollback if any of them fail. This common task is essentually impossible using cached updates the way that they describe them.

An interesting aside is the fact that the BDE, when used with MIDAS will not automatically commit items if there is already a transaction in progress (no nested transactions). Interbase express on the other hand, they say that you need a transaction object for each TIBQuery (in the help). Frankly, I'm a bit confused (as usual).

 

[Marco Kalter]

How exactly would the code in your client application look that would apply the updates of several client datasets in a single transaction?

------------------
Marco Kalter
Allround Automations

 

[Dean]

Well, an example would be something like this...

procedure SaveToDatabase;
begin
Server.StartTransaction;
try
//Each of these functions uses a different
//TClientDataset...
InsertIntoMainTable;
InsertIntoCrossReferenceTable1;
InsertIntoCrossReferenceTable2;

//Now Apply
MainTable.ApplyUpdates;
CrossReference.ApplyUpdates;
Crossreference2.ApplyUpdates;

{The problem is that with each of the above apply updates a commit occurs, making it impossible to rollback all 3 of the table updates if one of them fail.}
Server.Commit;
except
Server.Rollback;
end

end;

I've seen some code where Dan Miser (a MIDAS guru), shows you how to hack into the provider and use the transaction processing (nested tables example). Unfortunately, I have some fairly complex (and recursive) copy algorithms that are dynamically setting SQL and the example does not work for them. Dan makes a good argument to why you wouldn't want a 3-Tiered client controlling database transactions, which I agree with. However, I need to find a way to apply updates to multiple tables in a single transaction then. This may already be possible, perhaps, but I have yet to come across a way to do it.