Lazarus

Programming => Databases => Topic started by: schand99 on February 10, 2020, 01:25:42 pm

Title: SQLite Query Post Commit
Post by: schand99 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?
Title: Re: SQLite Query Post Commit
Post by: Zvoni 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
Title: Re: SQLite Query Post Commit
Post by: mangakissa 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.  
Title: Re: SQLite Query Post Commit
Post by: egsuh on February 14, 2020, 02:27:14 am
Does ApplyUpdates take care of deleted records?
Title: Re: SQLite Query Post Commit
Post by: mangakissa on February 14, 2020, 08:23:53 am
Yes. The procedure updates the database what the container has registraded.
TinyPortal © 2005-2018