[Lazarus] SQLDB: how to use TSQLTransaction

[SPRL AFOR]

Le 11/04/2015 16:29, Xiangrong Fang a écrit :
> 2015-04-11 15:41 GMT+08:00 SPRL AFOR <aforsprl at gmail.com 
> <mailto:aforsprl at gmail.com>>:
>
>     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
> Lazarus at lists.lazarus.freepascal.org
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20150411/a35548ee/attachment-0003.html>