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.
--
_______________________________________________
Lazarus mailing list
[email protected]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
