Recent

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

schand99

  • Newbie
  • Posts: 1
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

  • Sr. Member
  • ****
  • Posts: 334
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

mangakissa

  • Hero Member
  • *****
  • Posts: 970
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 1.84 (32b) / FPC 3.0.4
Windows 10

egsuh

  • Sr. Member
  • ****
  • Posts: 271
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: 970
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 1.84 (32b) / FPC 3.0.4
Windows 10