UNIDAC Firebird ans Autocommit

[smazaudi]

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

[AndreyZ]

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;

[smazaudi]

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

[AndreyZ]

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;

[smazaudi]

Thanks for your response.

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

Regards

[AndreyZ]

If any other questions come up, please contact us.