Recent

Author Topic: SQLite - multiple updates  (Read 5180 times)

dve83

  • New Member
  • *
  • Posts: 19
    • trueshift.za.net
SQLite - multiple updates
« on: November 24, 2012, 09:54:08 am »
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
Lazarus 1.0.6 32bit , FPC 2.6.0, OS: Windows 64bit, WidgetSet: LCL Win32

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple updates
« Reply #1 on: November 24, 2012, 10:43:42 am »
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...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

dve83

  • New Member
  • *
  • Posts: 19
    • trueshift.za.net
Re: SQLite - multiple updates
« Reply #2 on: November 24, 2012, 10:52:09 am »
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
Lazarus 1.0.6 32bit , FPC 2.6.0, OS: Windows 64bit, WidgetSet: LCL Win32

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple updates
« Reply #3 on: November 24, 2012, 11:08:11 am »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018