Is it right way work transaction like this?

[mrUlugbek]

Hi
I had two problems but how did that decide how right this decision is?
Database Firebird 3

I was so

Code: Select all

UniConnection  - AutoCommit=False
TransactionRead - ReadCommited
TransactionWrite - Snapshot

UniConnection - Transaction - TransactionRead 

Uniquery-CachedUpdate = True
Uniquery-UpdateTransaction-TransactionWrite 

OnSaveButton

Code: Select all

Uniquery.Post;
Uniquery. ApplyUpdates
Uniquery. CommitUpdates
try
 TransactionWrite .startTransaction
 TransactionWrite .Commit
except
  TransactionWrite .Rollback
  showmessage('Error')
end

In this code
When the second time I pressed the save button after rollback get error update data not found(0)
I find solution Uniquery options strictupdate=false

Now another problem after rollback
Can't perform operation on inactive transaction

I read in this forum i find solution but is it right way?

Code: Select all

UniConnection = AutoCommit=True
Uniquery.Post;
Uniquery. ApplyUpdates
Uniquery. CommitUpdates
try
 TransactionWrite .startTransaction
 TransactionWrite .CommitRetaining
except
  TransactionWrite .RollbackRetaining
  showmessage('Error')
end

Now do not go out of error after Rollback work properly
I want control manually transaction

[ViktorV]

The behavior of UniDAC components when using CachedUpdates mode is as follows:
- when calling the TUniQuery.ApplyUpdates UpdateTransaction method, the transaction is committed only when TUniQuery.AutoCommit is set to True;
- when calling the TUniQuery.ApplyUpdates UpdateTransaction method, the transaction is started only if this transaction was not explicitly started before;
- when TUniQuery.AutoCommit is set to False, the transaction is only committed when the Commit (CommitRetaining) UpdateTransaction method is explicitly invoked;
- the AutoCommit property has the True value by default that leads to automatically execution of CommitRetaining or RollbackRetaining when there is any data modification. By setting the property to False, you will get rid of this behavior, however, you will have to manage the transactions by yourself;
That means, if you want to manually control the work with transactions, you can use the following code:

Code: Select all

  UniConnection.AutoCommit := False;
  Uniquery.Post;
  if not TransactionWrite.Active then
    TransactionWrite.StartTransaction;
  try
    Uniquery.ApplyUpdates; //try to write the updates to the database
    TransactionWrite.Commit; //on success, commit the changes
    Uniquery.CommitUpdates; //on success, clear the cache
  except
    Uniquery.RestoreUpdates; //restore update result for applied records
    TransactionWrite.Rollback; //on failure, undo the changes
    ShowMessage('Error')
  end;

[mrUlugbek]

Big thanks you, for clearly answer

I have little question about use storedProcedure
is it right my step?

UnistoredProcedure - Transaction - TransactionWrite

Procedure job

take data from applied uniquery
and some make operation etc

Code: Select all

UniConnection.AutoCommit := True;
  Uniquery.Post;
  if not TransactionWrite.Active then
    TransactionWrite.StartTransaction;
  try
    Uniquery.ApplyUpdates; //try to write the updates to the database
     UnistoredProcedure.execute;
    TransactionWrite.Commit; //on success, commit the changes

    Uniquery.CommitUpdates; //on success, clear the cache
     UnistoredProcedure.close //
  except
    Uniquery.RestoreUpdates; //restore update result for applied records
    TransactionWrite.Rollback; //on failure, undo the changes
    ShowMessage('Error')
  end;

[ViktorV]

Since in your sample, the UnistoredProcedure.Transaction property is set to TransactionWrite, when calling TransactionWrite.Commit, UnistoredProcedure will be automatically closed.
Also note, since you set UniConnection.AutoCommit to True when calling Uniquery.ApplyUpdates, the TransactionWrite.CommitRetaining method is automatically called.
If you need manual transaction management, set UniConnection.AutoCommit to False. If your SP must use changes made by the UniQuery component, you should use the following code:

Code: Select all

  UniConnection.AutoCommit := False;
  Uniquery.Post;
  if not TransactionWrite.Active then
    TransactionWrite.StartTransaction;
  try
    Uniquery.ApplyUpdates; //try to write the updates to the database
    TransactionWrite.Commit; //on success, commit the changes

    if not TransactionWrite.Active then
      TransactionWrite.StartTransaction;
    UnistoredProcedure.Execute;
    TransactionWrite.Commit;

    Uniquery.CommitUpdates; //on success, clear the cache
  except
    Uniquery.RestoreUpdates; //restore update result for applied records
    TransactionWrite.Rollback; //on failure, undo the changes
    ShowMessage('Error')
  end;

[mrUlugbek]

Thank you very much. Good luck

[mrUlugbek]

Hi,ViktorV
I have little question,can you explain what i do wrong
when I use like this
I have master Detail
2 dataset .AutoCommit false - CachedUpdate =True
UniConnection.AutoCommit := False;
MasterDataset Generator on when Insert because this master dataset

Code: Select all

  MasterDataset .Post;
  DetailDataset .Post;
  
  if not TransactionWrite.Active then
    TransactionWrite.StartTransaction;
  try
       MasterDataset .ApplyUpdates;
       DetailDataset .ApplyUpdates;        //try to write the updates to the database
       TransactionWrite.Commit; //on success, commit the changes

    if not TransactionWrite.Active then
      TransactionWrite.StartTransaction;
    UnistoredProcedure.Execute;
    TransactionWrite.Commit;

    MasterDataset .CommitUpdates; //on success, clear the cache
    DetailDataset .CommitUpdates; 
  except
    MasterDataset .RestoreUpdates; //restore update result for applied records
    DetailDataset .RestoreUpdates;
    TransactionWrite.Rollback; //on failure, undo the changes
    ShowMessage('Error')
  end;

Now after rollback when i push button again
Get Error unique constraint on master

If I use .cancel instead .RestoreUpdates then ok no error.

[ViktorV]

To solve the issue, please try call the Cancel method before call the RestoreUpdates method.

[kneighbour]

When using UniQuery.CachedUpdates:=true, is it also necessary to also set UniConnection.AutoCommit:=false; ?

I ask as I have 1 UniConnection in an application, but I might have any number of UniQuery components. I might only want this one UniQuery component to run in CachedUpdate mode, but all of the others to run as default.

Is this possible?

[ViktorV]

The AutoCommit property has the True value by default that leads to automatically execution of CommitRetaining or RollbackRetaining when there is any data modification. By setting the property to False, you will get rid of this behavior, however, you will have to manage the transactions by yourself. This property does not depend on the CachedUpdates property.
The TUniConnection.AutoCommit property has a higher priority than the specific option "AutoCommit" of datasets (TUniQuery, TUniTable). If the TUniConnection.AutoCommit property is set to False, all transactions can be committed only explicitly (despite of the specific option "AutoCommit" value of a dataset).
If you want most datasets to automatically commit transactions, and for some of them to control transactions manually, you should set the TUniConnection.AutoCommit property to True, and only for datasets with manual transaction control, set the specific option "AutoCommit" value to False.