SQLDB - working without SQLTransaction
lazarus / 16-Databases

----------------------------------------------------------------------
Dibo · 2012-12-28 06:09

Hi,

I'm trying to understand how lazarus SQLDB work. Seems that it is
working little different than delphi db or ZEOS. For example, it looks
like database is always in transaction (even if I change
SQLTransaction.Active to false). So to apply changes on DB I must call
SQLTransaction.Commit and this is problematic because Commit close all
assigned datasets. Is any way to work in "old delphi school" mode? I
want start transaction when it is really needed and simple
SQLQuery.Post should save to database immediately.

Regards

----------------------------------------------------------------------
Zoran · 2012-12-28 07:13

No. For some strange reason, autocommit mode (when it is up to
programmer to explicitely start the transaction) is not implemented in
SQLDb. You have to commit yourself. Furthermore, you must not forget
to apply updates before you do it, as you cannot turn off cashing of
updates. 

Anyway, you can avoid closing of associated datasets, by using "soft"
commits (SQLTransaction.CommitRetaining).

This is why people who come from Delphi's BDE or ADO DB components
will find Zeos much easier to migrate.

----------------------------------------------------------------------
Dibo · 2012-12-28 07:23

Quote from: Zoran on December 28, 2012, 08:13:53 amAnyway, you can
avoid closing of associated datasets, by using "soft" commits
(SQLTransaction.CommitRetaining).
This looks like solution for me. Thanks

----------------------------------------------------------------------
Dibo · 2012-12-28 07:31

So StartTransaction is unnecessary? SQLDB management this?
StartTransaction occur on first SQLQuery.Post or on ApplyUpdates?

----------------------------------------------------------------------
Zoran · 2012-12-28 08:38

Quote from: Dibo on December 28, 2012, 08:31:44 amSo StartTransaction
is unnecessary? SQLDB management this? StartTransaction occur on first
SQLQuery.Post or on ApplyUpdates?

In SQLdb, the transaction is represented by the SQLTransaction
component. And the transaction starts when you set your
SQLTransaction.Active to true (and if you don't do it yourself, then
it will be activated automatically when you open a dataset) and
remains active until you explicitely commit or rollback (and setting
SQLTransaction.Active to False is actually the same as
SQLTransaction.Rollback).

When you execute SQLTransaction.Commit, then the new transaction will
start automatically when you open one dataset which is associated to
this transaction component.
If I understand well, if you do SQLTransaction.CommitRetaining, the
new transaction is started immidiately, so your datasets do not have
to be closed.

----------------------------------------------------------------------
nateloaf · 2013-06-24 18:14

This is an old post, but I've been wrestling with the same issue for a
while now.  I stumbled onto this one because I happened to do a search
including the term "CommitRetaining", and it was the missing piece of
the puzzle.  I too have a Delphi background, and was not expecting to
have to explicity call ApplyUpdates() and Commit() (nor was I
expecting my dataset to close on me when I did).

Thanks very much for taking the time to answer the original question
and may many others find this solution!