Recent

Author Topic: SQLite Query Post Commit  (Read 1608 times)

schand99

  • Newbie
  • Posts: 4
SQLite Query Post Commit
« on: February 10, 2020, 01:25:42 pm »
Hello,

on a existing project which uses SQLite db, I try to replace some database.executedirect by a simple post & commit. I like to do this, because for executedirect, exclusive access to the database is needed.

Unfortunately, I am not able to set the Software to work, after writing the data, post and commit, there is no data written.

For easier Code parsing, I have copied the following Code snippet and modified as needed, but it did not work. I don't get any error but the data is not saved :(

Code: Pascal  [Select][+][-]
  1.  
Program EditData;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
    db, sqldb, sqlite3conn;
var
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.DataBase := AConnection;
  Query.SQL.Text := 'select * from tblNames where ID = 2';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 2';
  Query.Post;
  Query.UpdateMode := upWhereAll;         // defined in db
  Query.ApplyUpdates;
  ATransaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

Code: Pascal  [Select][+][-]
  1.  

any suggestions?

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite Query Post Commit
« Reply #1 on: February 11, 2020, 08:41:58 am »
How curious.
That's the exact sample from the Wiki, and you say it doesn't work?
https://wiki.lazarus.freepascal.org/SqlDBHowto
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: SQLite Query Post Commit
« Reply #2 on: February 11, 2020, 09:05:27 am »
It works for me.

Why 'Query.UpdateMode := upWhereAll;  ' after post. This is a property to use before open the dataset. Also upWhereKey is better, because it uses primary key. UpWhereAll uses all oldvalues to find the right record to update.

reformat your code
Code: Pascal  [Select][+][-]
  1. AConnection := TSQLite3Connection.Create(nil);
  2.   ATransaction := TSQLTransaction.Create(AConnection);
  3. try
  4.   AConnection.Transaction := ATransaction;
  5.   AConnection.DatabaseName := 'test_dbase';
  6.   Query := TSQLQuery.Create(nil);
  7.   Query.DataBase := AConnection;
  8.   Query.SQL.Text := 'select * from tblNames where ID = 2';
  9.   Query.Open;
  10.   Query.Edit;
  11.   Query.FieldByName('NAME').AsString := 'Name Number 2';
  12.   Query.Post;
  13.   Query.UpdateMode := upWhereAll;         // defined in db
  14.   Query.ApplyUpdates;
  15.   ATransaction.Commit;
  16. finally
  17.  Query.Free;
  18.   AConnection.Free;
  19. end
  20.  
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: SQLite Query Post Commit
« Reply #3 on: February 14, 2020, 02:27:14 am »
Does ApplyUpdates take care of deleted records?

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: SQLite Query Post Commit
« Reply #4 on: February 14, 2020, 08:23:53 am »
Yes. The procedure updates the database what the container has registraded.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018