Le 11/04/2015 16:29, Xiangrong Fang a écrit :
2015-04-11 15:41 GMT+08:00 SPRL AFOR <[email protected]
<mailto:[email protected]>>:
Michael's comments do not need further explanation
If you want an Sql statement be executed immediately attach an
TSqlTransaction to a DB component, attach the TSqlQuery to the
transaction and enclose it between a StartTransaction and a Commit.
TR.StartTransaction;
qr.ExecSql;
TR.Commit;
The Commit matters as well
As a matter of fact, I think yours and Michael's reply both answered
part of my questions but not all. I am now clear about the meaning of
RowsAffected returning value -1, but I am still not clear about WHY
*sometime* I need to use Commit, but other times it is *not* necessary?
If the rule with SQLDB is that I always need to use Commit, fine, I am
perfectly fine with this rule. But, in my function, th
Commit is not necessary, even without it, the UPDATE takes effect.
That's why I am confused: when must I use Commit, and when it is
optional? Please note that:
As Michael wrote, a transaction is ALWAYS nedded. Full stop. Whether you
manage it using components (like SqlDB) or the DBMS does the job the
result is the same.
Scheme is ALWAYS: StartTransaction, apply any Sql statements, Commit (or
Rollback). If you do not explicitely do it, the DBMS will do it for you.
1) I am using SQLDB without a form, so all properties are at their
default value, unless I explicitly change them -- I don't know there
are options like
sqoAutoCommit
, and I didn't set any properties for sqldb components.
DBMS options (set in params field in TSqlDB or TSqlTransaction). Check
with Sqlite 3 database and transaction params
2) I am using SQLite3, on Ubuntu 14.10 64bit.
Sqlite 3 is transaction compliant. Check ACID transaction params.
3) I even thought that Commit is necessary when using INSERT, but is
optional if you use UPDATE, but that logic seems weird...
Commit is not an option: it MUST be executed somewhere if you want your
data be fixed in the database after the last (either explicit or
implicit StartTransaction like in CommitRetaining). For all other
transactions outside yours, data could not be in the last stable state
(i.e. non visible) without a Commit. Depending on DBMS behaviour Sql
statement will be auto-committed (MS SQL server) or you should do the
job explicitely. DBMS ACID properties should help you to see what happens.
Just to see what happens, in your function, enclose all the with block
between a TR.StartTransaction and a TR.Commit, like this:
if not TR.InTransaction then
TR.StartTransaction;
with qr do begin
SQL.Text := Format('UPDATE records SET status=%d WHERE id=%d AND
status=%d', [TR_SUCCESS, id, TR_PENDING]);
ExecSQL;
Result := RowsAffected > 0;
WriteLn('Confirming: ', id, ', affected: ', RowsAffected);
end;
TR.Commit;
If you could explain why the behaviors are different that will be very
helpful.
(read here <https://www.sqlite.org/lang_transaction.html> and here
<https://www.sqlite.org/isolation.html>. This one maybe too here
<https://www.sqlite.org/transactional.html>)
For instance, using Firebird, Transaction params should be set to
Concurrecy, NoWait to use the SNAPSHOT transaction isolation.
Antonio.
Thank you very much!
--
_______________________________________________
Lazarus mailing list
[email protected]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel
antivirus Avast.
http://www.avast.com
--
_______________________________________________
Lazarus mailing list
[email protected]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
