Title: Transaction in MIDAS
Question: How can I use transaction in MIDAS?
Answer:
Transaction is very popular used in database programming. It's very easier used in Two-Tier application. How do it in Three-Tier?
I have try to use cache-update in remote data modules and use transaction, I test it successfully in Delphi 4. But to do it cost many code and low performance. When Delphi 5 coming, I try to use the same method, but failed. Maybe this is the bug of Delphi 5. I think that many person have the same situation with me. How can use transaction in MIDAS?
I try another method, just do it at BeforeUpdateRecord event. For example, when I edit table A, I want to insert a log to another table B. To do this, first:
1) create a remote data module , drop a query to select records from table A, drop a UpdateSQL for the query, specify the SQL statement of Table A. Drop a datasetprovider hook to the query. Drop another query, then in its SQL property write the insert SQL statement to table B.
2) In the BeforeUpdateRecord event, I write this code:
SetParams(UpdateSQL1, DeltaDS, UpdateKind);
{ for more detail about SetParams function, please see my another article "Use your own SQL to update table in MIDAS"}
UpdateSQL1. ExecSQL(UpdateKind);
if UpdateKind = ukUpdate then begin
{ Insert a log to Table B}
// set the params
Query2.ExecSQL;
end;
Applied := True;
3) In the client, you should also make some change. When you try to save the changes, use ApplyUpdates(0) instead of ApplyUpdates(-1). For example:
ClientDataSet1.ApplyUpdates(0)
This is OK. Delphi does it well. If a error occur when you try to insert a log to Table B, the transaction will rollback, then you can find that Table A's record is not changed at all.
I have tested it at Delphi 4, It also works very well.
Good Luck.