Firebird Commit and CommitRetaining

[bambamns]

Hi,

I have two server applications running 24/7 with Firebird database and 38 tables.
For posting data in tables I use something like :

Code: [Select]

with DM.IPStatusQ do
     begin
      Insert;
       FieldByName('THREAD').Value := _thread;
       FieldByName('IP').Value := _ip;
       FieldByName('CONNECTED').Value := now;
      Post;
      ApplyUpdates;
      DM.SQLTransaction.CommitRetaining;
     end;

It is working with no problem, but when I have to maintain database and to do sweep it runs for hours on 200MB database (too long - it is more efficient to make backup and restore).

As I read on Firebird forums, FAQ and help, this problem is present because I use CommitRetaining.
It is not recommended to use CommitRetaining - the right way is to use Commit, but when I use it, all open tables become closed and I have to open them again.

Is there an easier way to keep tables open after using Commit (beaside using CommitRetaining)?

Thx

[nomorelogic]

hi
you can read here, in the bottom of the page you can find some useful info about Commit Retaining.

http://www.firebirdsql.org/file/documentation/papers_presentations/html/paper-fbent-impacting.html

Maybe you can use 2 different transactions. The first one to mantain all tables-you-need-for-lookup opened (where you can use commit retaining). The last one connected to table you need for normal data management (and here you can use Commit / Open).

You also need to shut down periodically (weekly?) the database, this will release server side resources.

[exdatis]

Very useful link, thank you very much.

[mse]

Is there an easier way to keep tables open after using Commit (beaside using CommitRetaining)?

MSEgui git master version of SqlDB has the flag tsqltransaction.options tao_fakeretaining for that purpose. There is also a flag dso_offline in tsqlquery. If it is set the DB-connection and/or the transaction can be closed after fetching the data, the dataset stays active. It is possible to reconnect later in order to apply and commit the changes. It is also possible to write a journal on disk if the DB is not accessible.

[mangakissa]

Is it not usefull to combine forces with SQLdb and MSEgui?

[mse]

Is it not usefull to combine forces with SQLdb and MSEgui?

They are very different already. I suggest to backport useful pieces or take useful ideas from MSEgui. The tao_fake* flags definitely are candidates.
The MSEgui sources are here:
https://gitorious.org/mseide-msegui

[bambamns]

Maybe you can use 2 different transactions.

As I can see this look like best solution, for now.
One, for table views and second, for data management.

Thank You