SQLite - multiple updates
lazarus / 16-Databases

----------------------------------------------------------------------
dve83 · 2012-11-24 08:54

Hi,

any reason why only the first of the update statements would execute?

With Query Do
Begin
  SQL.Clear;
  SQL.Add('Update MyTable Set MyField = 2;');
  SQL.Add('Update MyTable Set MyField = 1;');
  ExecSQL
End;

Kind Regards
Danie

----------------------------------------------------------------------
BigChimp · 2012-11-24 09:43

Either SQLite doesn't support multiple statements in one go or the FPC
SQLDB just passes on the first statements.

A solution: use a parameterized query (e.g. Code: [Select]Update
MyTable set MyField=:MyFieldPar;), then run the update statements in a
loop (e.g. something like, untested, Code:
[Select]Query.ParamByName('MyFieldPar').AsInteger:=2;
Query.ExecSQL;
Query.ParamByName('MyFieldPar').AsInteger:=1;
Query.ExecSQL;
Of course, you don't especially need to use parameterized queries when
inserting integers...

----------------------------------------------------------------------
dve83 · 2012-11-24 09:52

Thanks for the reply. just needed to confirm. I would assume that
performing multiple ExecSQL and then only committing the transaction
would be better than perform a commit for each update statement.

Dannie

----------------------------------------------------------------------
BigChimp · 2012-11-24 10:08

Hi Dannie,

Yes that might help.... or may not make much difference. I'd indeed go
with a commit at the end as well if only because it seems neater from
a logical perspective : either perform all data updates or rollback
the entire thing.