[fpc-pascal] [Lazarus] SQLDB: how to use TSQLTransaction

[Michael Van Canneyt]

On Sat, 11 Apr 2015, Xiangrong Fang wrote:

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

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 committed at once.

- An option to TSQLQuery called sqoAutoCommit, which will commit each statement at once.
   i.e. it 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.