Devart Forums

Posted: **Thu 19 Sep 2013 09:26**

Hello,
I have installed newer version of UniDac 5.0.1 into Delphi 2009.
I'd like to know where can I find the AUTOCOMMIT property.

I only use a TUNIConnection and no TUniTransaction and on some clients we have a problem of progressively degrading performance. We don't really understand why !

On Firebird page we have the information :

Commit Retaining and “Autocommit”

Commit Retaining is a “feature” of Firebird that was inherited from the ancestor, InterBase. It was implemented as a means to retain server-side resources and keep them available for developers using Borland's rapid application development tools and the BDE, the generic data access layer that was used to connect Windows graphical applications to databases. Its purpose was to present a level playing field for RAD development, regardless of the database running at the back-end.

Autocommit—a client-side mechanism that rolls the statement-level Post and the transaction-level Commit phases into a single step—was provided to enable developers to avoid transactions altogether. In the RAD components, Commit Retaining and Autocommit were bound together. This fitted well with desktop databases like dBase and Paradox (whose engine is, in fact, the BDE!), which do not have transactions.

With Firebird (and InterBase), Commit Retaining causes transactions to remain interesting indefinitely. Garbage collection effectively ceases on the “standard” Borland RAD tools database application and any other applications that make use of Commit Retaining. Such systems are fraught with problems of progressively degrading performance that cannot be resolved except by shutting down the database and allowing these old transactions to die.

Autocommit and Commit Retaining are not restricted to the Borland tools, of course. They are supported by most data access interfaces and Commit Retaining is available in SQL, so it behoves the application developer to understand the effects and to use these features with extreme care and control.

ON DELPHI 5 with BDE there was the property SQLPASSTHRUMODE wich was set to "true".
I tried setting TUniConnection.Options.DisconnectedMode to true but no effect.

How can we force Firebird Server to AUTOCOMMIT ?

Thanks

Posted: **Thu 19 Sep 2013 10:33**

Hello,

InterBase and Firebird require an active transaction for any operation under data. When a transaction is committed or rolled back, dataset will be closed (because there will be no active transaction). To avoid this problem, UniDAC calls the CommitRetaining or RollbackRetaining methods which retain the transaction context. This allows dataset to remain active.
To prevent calling of the CommitRetaining method, you should use two different transactions for opening and modifying data.Here is a code example:

Code: Select all

begin
  UniQuery1.SQL.Text := 'select * from tablename';
  UniQuery1.Open;
  UniQuery1.Edit;
  UniQuery1.FieldByName('fieldname').AsString := 'test';
  UniQuery1.Post; // as only one transaction is used for opening and modifying data, here the CommitRetaining method is called to prevent UniQuery1 from closing
end;

Code: Select all

begin
  UniQuery1.Transaction := UniTransaction1;
  UniQuery1.UpdateTransaction := UniTransaction2;
  UniQuery1.SQL.Text := 'select * from tablename';
  UniQuery1.Open;
  UniQuery1.Edit;
  UniQuery1.FieldByName('fieldname').AsString := 'test';
  UniQuery1.Post; // as two transactions are used, here the Commit method is called (for the transaction specified in UpdateTransaction)
end;

Posted: **Thu 19 Sep 2013 13:20**

Thanks.

In my case, I use a TUNISQL because I just want to update one or several fields:

Code: Select all

var MyUNISQL :TUNISql
......
with MyUNISQL do
begin
 SQL.Clear;
 SQL.Add('UPDATE mytable set MYFIELD1=......');
 ParamByName('MyFiled1').As.... := 
 Execute;
end;

My component MyUNISQL is used with other components (UNIQuery) in the same application.
These components are connected on the same TUNIConnection placed in a datamodule.
It seams that data updates (with TUNISQL component) are not committed as long as I quit the application.

I'd like to COMMIT automatically when I execute the MyUNISQL.execute.

Regards

Posted: **Fri 20 Sep 2013 08:45**

The AutoCommit specific option is added in the latest UniDAC version 5.1.3. To use AutoCommit, you should upgrade UniDAC to this version. Here is code example:

Code: Select all

begin
  UniSQL1.SpecificOptions.Values['AutoCommit'] := 'True';
  UniSQL1.SQL.Text := 'update tablename set ...';
  UniSQL1.Execute;
end;

Posted: **Fri 20 Sep 2013 10:08**

Thanks for your response.

We are going to try this property, we hope it will soluce our problem.

Regards

Posted: **Fri 20 Sep 2013 10:22**

If any other questions come up, please contact us.

Devart Forums

UNIDAC Firebird ans Autocommit

UNIDAC Firebird ans Autocommit

Re: UNIDAC Firebird ans Autocommit

Re: UNIDAC Firebird ans Autocommit

Re: UNIDAC Firebird ans Autocommit

Re: UNIDAC Firebird ans Autocommit

Re: UNIDAC Firebird ans Autocommit