Design issue: How to get always fresh data with Zeos TZConnection and TZQuery (in AutoCommit mode)?

[jonatan]

I have Delphi application with TZConnection in AutoCommit mode (connecting to the Firebird 3.0) in the global DataModule, this single connection serves all the queries in all the datamodules of the application.

But I have the problem, that the queries see only the data that were committed during the connection phase and queries do not the the new/fresh data that are coming either from the other application instances or from the other components (e.g. IBX datasetes) of the same application.

TZConnection.Reconnect solves this problem, but I am not sure what happend with the other open forms during such hard Reconnect.

Mostly it would be so nice to have TZonnection.CommitRetaining or RollbackRetaining procedures (similar to what IBX TIBTransaction have), but TZConnection do not have such functions and even TZConnection.Commit does not work in AutoCommit mode (the error is raised that Commit is incompatible with AutoCommit mode).

So - what is the best practice how to get fresh data using TZConnection. I would be happy to execute some function on the TZonnection upon opening new DataModule or upon client request for the new report.

This same question I have asked in SO https://stackoverflow.com/questions/538 ... tocommit-m but without responses there.

[marsupilami]

Hello Jonatan,

usually setting TZConnection TransactionIsolationLevel property to tiReadCommitted and AutoCommit to true should do what you want.

and even TZConnection.Commit does not work in AutoCommit mode (the error is raised that Commit is incompatible with AutoCommit mode)

This is by design - if you are in auto commit mode, Commit has no sense. It only makes sense if you disable the auto commit mode or explicitly start an transaction using TZConnection.StartTransaction. See this Wiki Page for more information on AutoCommit mode and StartTransaction, Commt and Rollback.

Mostly it would be so nice to have TZonnection.CommitRetaining or RollbackRetaining procedures (similar to what IBX TIBTransaction have), but TZConnection do not have such functions and even TZConnection.Commit does not work in AutoCommit mode (the error is raised that Commit is incompatible with AutoCommit mode).

On Firebird Zeos always does CommitRetaining or RollbackRetaining by default. It doesn't make sense to implement methods like CommitRetaining and RollbackRetaining because no other database supports retaining. There is an option to make the Firebird driver use hard commits and hard rollbacks though.

Best regards,

Jan

[jonatan]

Merci! Setting TZConnection TransactionIsolationLevel property to tiReadCommitted and AutoCommit to true solved my problem. Ineed, I am always using ReadCommited for my TIBTransaction as well and now I wonder why I didn't set it for TZConnection from the start as well.