TUniTransaction WAIT mode

[sir.wally.lewis]

According to a firebird expert I need to use WAIT trasactions when updating DML data.
see http://tracker.firebirdsql.org/browse/CORE-3805

However I have no Idea as to set TUniTransaction to run in WAIT mode.
Does anyone know?

[AndreyZ]

For the time being, UniDAC does not have the functionality that allows setting parameters of InterBase or Firebird transactions. We will add such functionality in one of the next UniDAC versions.

[sir.wally.lewis]

That is great!
How can I be aware of when this has been released?
I have to get this issue resolved ASAP.

[AndreyZ]

We make announcements at our forum, for example, http://forums.devart.com/viewtopic.php?f=28&t=25486. When we add support of setting parameters of InterBase and Firebird transactions to UniDAC, we will include this information to the new announcement. Because you are registered at our forum, you will receive e-mails informing about new releases.

[sir.wally.lewis]

OK,

Now I have updated to version 5 of UniDAC.
How do I set up a Transaction to have the WAIT parameter?

[AndreyZ]

To set the WAIT parameter of a transaction, you should use the following code:

Code: Select all

TUniConnection.DefaultTransaction.IsolationLevel := ilCustom;
TUniConnection.DefaultTransaction.SpecificOptions.Values['Params'] := 'wait';

Please note that to run this code, you should add the CRAccess unit to the USES clause of your unit.

[sir.wally.lewis]

To Ensure my default transaction commits all the time should i add

DefaultTransaction.IsolationLevel := ilCustom;
DefaultTransaction.SpecificOptions.Values['Params'] := 'wait'
DefaultTransaction.SpecificOptions.Values['Params'] := 'autocommit'
DefaultTransaction.SpecificOptions.Values['Params'] := 'read_committed'


To my custom transaction?

[AndreyZ]

For now, UniDAC does not support the autocommit transaction parameter. We will add it in the next UniDAC build.

[sir.wally.lewis]

OK, with default transaction when does it commit?
Do I have to revert to not using the default transaction if it is not committing after

TUniQuery.ExecSQL or TUniStoredProc.ExecProc ?

[AndreyZ]

If you start a transaction explicitly, you should commit or roll back it explicitly. Here is en example:

Code: Select all

begin
  UniConnection1.Open;
  UniConnection1.StartTransaction; // explicit start of a transaction
  try
    UniConnection1.ExecSQL('some modifications in database');
    UniConnection1.Commit; // explicit commit of a transaction
  except
    UniConnection1.Rollback; // explicit roll back of a transaction
    raise;
  end;
end;

If you do not start a transaction explicitly, UniDAC commits it automatically after an operation is complete. Here is en example:

Code: Select all

begin
  UniConnection1.Open;
  UniConnection1.ExecSQL('some modifications in database'); // these modifications are committed automatically by UniDAC 
end;

[sir.wally.lewis]

OK,

I think I figured out why the transactions weren't committing.
I needed to add this code:

IsolationLevel := ilCustom;
slParams := TStringList.Create;
slParams.Add('wait');
slParams.Add('read_committed');
slParams.Add('rec_version');
SpecificOptions.Values['Params'] := slParams.Text;
FreeAndNil(slParams);

It seems to be working after code was adjusted as above.

[AndreyZ]

You found the correct solution.