[Lazarus] SQLDB: how to use TSQLTransaction

[Xiangrong Fang]

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-ide.org/pipermail/lazarus/attachments/20150411/9b3456ed/attachment-0002.html>