Hi All,

Sorry that I don't know if SQLDB belongs to fpc or lazarus, so I posted to both lists.

I am confused about the use of transaction in SQLDB. I tried to use in in my non-gui app. Here is the code:

function TPortfolio.ConfirmTransaction(id: Integer): Boolean;
begin
  with qr do begin
    SQL.Text := Format('UPDATE records SET status=%d WHERE id=%d AND
status=%d',
      [TR_SUCCESS, id, TR_PENDING]);
    ExecSQL;
    WriteLn('Confirming: ', id, ', affected: ', RowsAffected);
    //tr.Commit;
    //WriteLn('Confirming: ', id, ', affected: ', RowsAffected);
    Result := RowsAffected > 0;
  end;
end;
It seems that I must assign a TSQLTransaction object to the TSQLQuery
object, otherwise it doesn't work. I also found that without call
tr.Commit, the ExecSQL will not take effect, at least for INSERT
statements.

In the above function, I found the first WriteLn will output rows-affected as 1, and the 2nd (after tr.Commit) will output rows-affected as -1! I then commented out the tr.Commit; statement, it STILL worked.

Now my questions are:

1. How to control the use of transactions in SQLDB? Sometime I do NOT want transaction, but want the DDL/DML statement to be executed immediately. Why sometime SQLDB's ExecSQL won't take effect if not followed by a Commit? BTW, I tried to set tr.Active to true/false before execute the sql, but it seems not making a difference?

2. What is the meaning of -1 as returned by RowsAffected?

Thanks!

Xiangrong
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20150411/9b3456ed/attachment-0001.html>

All SQL engines use transactions. Every command is always executed in the context of a transaction. Some of them just start and commit a transaction implicitly, around each command, if you don't do it explicitly yourself. Most people are perfectly happy with this, and for simple applications, this is probably enough.

SQLDB forces you to be aware of all this, by requiring you to start and commit each transaction explicitly. However, most commands will start a transaction behind the scenes. They will not commit it, however, requiring you to commit it, since you would not be able to rollback if you wanted this.

[Note to self: add this to documentation]

Now, we realize that this is some extra work, so, in trunk, there are 2 options to control this behaviour:

- An option to TSQLTransaction, called stoUseImplicit, which means the implicit transaction handling of the engine will be used. (SQLDB then simply doesn't start a transaction)

This has the effect that every statement will then be An option to TSQLQuery called sqoAutoCommit, which will commit each calls SQLTranaction.Commit after each command.

2. What is the meaning of -1 as returned by RowsAffected?

Open will normally not return RowsAffected (so it returns -1). ExecSQL should return the actual number of affected rows. If -1 is returned, then either the transaction was committed, or the SQL engine you use doesn't support it.

You must always examine RowsAffected before Committing. Commit resets all results and whatnot, and normally also closes any datasets connected to the transaction. This behaviour again can be avoided with some option, but this option has side effects.

Michael.

Le 11/04/2015 03:53, Xiangrong Fang a ?crit :

The transaction process depends upon the DBMS used. In a general way, all DBMS execute SQL statement under the control of a transaction. Some of the DBMS (MS SQL Server) use to have "automatic" transaction control as default and some others (Firebird) need explicit Start and Commits. Using Oracle definition a transaction "is a logical, atomic unit of work that contains one or more SQL statements". I'm used to always work with fine tuned transaction control. This way I know what the DBMS is doing with data.

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

Working with DBMS since a long time, I always use the same rules: Start a transaction, do whatever Sql statements have to be done all together, Commit the transaction. This way I control how data is goes to and from the databsase.

---
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.freepascal.org/pipermail/lazarus/attachments/20150411/cfe313a7/attachment.html>

2015-04-11 15:41 GMT+08:00 SPRL AFOR :

Michael's comments do not need further explanation

?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, the 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:

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.?

2) I am using SQLite3, on Ubuntu 14.10 64bit.?

3) I even thought that Commit is necessary when using INSERT, but is optional if you use UPDATE, but that logic seems weird...?

If you could explain why the behaviors are different that will be very helpful.

?Thank you very much!?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20150411/cc7eaaa9/attachment.html>

The rule is: You must *always* use commit.

I cannot explain the behaviour you observed without deeper investigation, but suspect some error in your or sqlite's logic somewhere. Happens to me also, I think I found a bug, turns out it was me being wrong all along :)

Michael.

Le 11/04/2015 16:29, Xiangrong Fang a ?crit :

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.

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;

(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.

---
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.freepascal.org/pipermail/lazarus/attachments/20150411/a35548ee/attachment-0001.html>

- An option to TSQLTransaction, called stoUseImplicit, which means the implicit transaction handling of the engine will be used. (SQLDB then simply doesn't start a transaction)

This has the effect that every statement will then be An option to TSQLQuery called sqoAutoCommit, which will commit each calls SQLTranaction.Commit after each command.

Sorry, I thought that was a mission to TSQLTransaction.Action as it accepts some values like caCommitRetaining or caRollback. So, how does this property works?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20150416/b6b0be3f/attachment.html>

Action determines what happens if you call EndTransaction, and that is called when the transaction instance is destroyed.

Michael.

So, after a TSQLTransaction.Commit should I calll a TSQLTransaction.EndTransaction?

I think should be friendly and usefull if TSQLTransaction.Action works as a default action for transaction managing. If you set it as <i>caNone</i> you * MUST * manage it manually. But, I know that this modification should break a lot of past projects.....

William de Oliveira Ferreira

Programador Web/Desktop

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20150416/904045b4/attachment.html>

William Ferreira wrote

So, after a TSQLTransaction.Commit should I calll a TSQLTransaction.EndTransaction?

No.

I will deprecate EndTransaction. It was introduced for Delphi compatibility. There should normally never be any need to call it.

Michael.

So it should deprecate TSQLTransaction.Action too, right?

William de Oliveira Ferreira

Programador Web/Desktop

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20150416/b8170043/attachment.html>

William Ferreira wrote

So it should deprecate TSQLTransaction.Action too, right?

Yes.

Michael.