CommitRetaining question

[stevel]

Using: Delphi XE2 Enterprise, Firebird 2.51, UniDAC 5.01 Pro, Windows 8 64-bit, VCL Forms application

What exactly does TUniConnection.CommitRetaining do?

Is this code a good way of doing INSERT stored procedure?

Code: Select all

  if datamod.UniConnection1.InTransaction then
    datamod.UniConnection1.CommitRetaining;

  try
    (* here execute a INSERT stored procedure *) 

    if datamod.UniConnection1.InTransaction then
     datamod.UniConnection1.CommitRetaining;
  except
    on E: Exception do
    begin
      if datamod.UniConnection1.InTransaction then
       datamod.UniConnection1.RollbackRetaining;
      raise Exception.Create(E.Message);
    end;
  end;

Will there be any performance problems with this code?

Update:
Is there any better way of doing things? I am getting table key violation errors when running the same code second time.

---------------------------
Debugger Exception Notification
---------------------------
Project xxxx.exe raised exception class EUniError with message '
attempt to store duplicate value (visible to active transactions) in unique index "IXINVTYIDBATCHNO"
At procedure 'INVTY_BATCH_I' line: 10, col: 3'.
---------------------------
Break Continue Help
---------------------------

The reason is that this Insert stored procedure is getting the value to be inserted from another SELECT stored procedure that is executed just before it is executed.

So, I am illustrating this by including the additional information in the first code example:

Code: Select all

  if datamod.UniConnection1.InTransaction then
    datamod.UniConnection1.CommitRetaining;

  try

    (* here execute a SELECT stored procedure
       getting a value from a table, and store 
       it to variable.
    *) 


    (* here execute a INSERT stored procedure
       using the value from the SELECT stored 
       procedure above.

       The insert is being made to a table with
       unique field constraint.

       Exception occurs here due to key violation
       in the table being inserted to.
       
       This means that the SELECT stored procedure 
       above is returning an old value from the table. 
        ... SELECT MAX(RowId) FROM MyTable ....
 
    *) 

    if datamod.UniConnection1.InTransaction then
     datamod.UniConnection1.CommitRetaining;
  except

    on E: Exception do
    begin

      if datamod.UniConnection1.InTransaction then
       datamod.UniConnection1.RollbackRetaining;
    
      raise Exception.Create(E.Message);

    end;

  end;

What changes need to be made to my code or transaction style to fix this error?

[AndreyZ]

The CommitRetaining and RollbackRetaining methods retain the transaction context. You can read about CommitRetaining here:
http://www.firebirdsql.org/file/documen ... cting.html
http://www.firebirdfaq.org/faq13/
As you can see, usage of CommitRetaining leads to performance problems and should be used when it is really needed.
For the task you described, you can use the following code:

Code: Select all

begin
  datamod.UniConnection1.Open;
  datamod.UniConnection1.StartTransaction;
  try
    (* here execute a SELECT stored procedure
       getting a value from a table, and store 
       it to variable.
    *) 

    (* here execute a INSERT stored procedure
       using the value from the SELECT stored 
       procedure above.
    *)
    datamod.UniConnection1.Commit;
  except
    datamod.UniConnection1.Rollback;
    raise;
  end;
end;

[stevel]

After the code executes, if I try to Refresh another dataset, I get this exception:

---------------------------
Debugger Exception Notification
---------------------------
Project xxxx.exe raised exception class Exception with message 'Can't perform operation on active transaction'.
---------------------------
Break Continue Help
---------------------------

Pls. note that the UniConnection is already open because it is already being used in my application.

[stevel]

Pls. give attention to my problem...

[AndreyZ]

This error means that you are trying to start a transaction that is already started. Please make sure that you commit or roll back a transaction before starting it.
Please note that InterBase and Firebird require an active transaction for any operation under data. When you open a dataset without transaction, UniDAC starts transaction internally to allow reading and writing of data.

[stevel]

Previously, if I commit a transaction, the existing open datasets (uniquery, unistoredproc) remained open. But now if I commit the connection's transaction the existing opened datasets are closed. How can I work around this?

[AndreyZ]

The point is that in UniDAC 5.0.1 we added the DefaultTransaction property in TUniConnection. From now, the TUniConnection component and all datasets that work through it, use DefaultTransaction for all operation under data. We added the DefaultTransaction transaction property because a lot of our users asked us to do it.
To avoid this problem, you should use different (from DefaultTransaction) transaction for your datasets. Here is a code example:

Code: Select all

begin
  UniConnection1.DefaultTransaction := UniTransaction1;
  UniQuery1.Transaction := UniTransaction2;
end;