Recent

Author Topic: Sqlite - Write changes to file  (Read 522 times)

P.curtis

  • Jr. Member
  • **
  • Posts: 80
Sqlite - Write changes to file
« on: May 13, 2020, 12:20:24 pm »
When I execute the following code

Code: Pascal  [Select][+][-]
  1. var
  2.   sSQL : string;
  3.   MyQUERY : TSQLQuery;
  4. begin
  5.   MyQUERY := TSQLQuery.Create(nil);
  6.   MyQUERY.Transaction := SQLTransaction1;
  7.   sSQL := 'INSERT INTO tnDATA (fnVBITRATE) VALUES (1234)';
  8.   MyQUERY.SQL.Add(sSQL);
  9.   MyQUERY.ExecSQL;
  10.   MyQUERY.Free;
  11.  

The inmemory database is updated.

My question is "How to write the changes to disk?"

Thanks in advance.

Simple project attached
« Last Edit: May 13, 2020, 01:55:05 pm by P.curtis »

nouzi

  • Full Member
  • ***
  • Posts: 179
Re: Sqlite - Write changes to file
« Reply #1 on: May 13, 2020, 01:17:49 pm »
add this befor
Code: Pascal  [Select][+][-]
  1. MyQUERY.post
  2.  
my english is  bad
Lazarus 2.0.6 free pascal 3.0.4
Lazarus trunk  free pascal trunk 
System : linux mint 19.3 64bit  windows 7 64bit

P.curtis

  • Jr. Member
  • **
  • Posts: 80
Re: Sqlite - Write changes to file
« Reply #2 on: May 13, 2020, 01:49:20 pm »
Nope it don't work.

It throws an error "Dataset not in edit or insert state"

korba812

  • Full Member
  • ***
  • Posts: 133
Re: Sqlite - Write changes to file
« Reply #3 on: May 13, 2020, 01:59:29 pm »
You should commit transaction.
Code: Pascal  [Select][+][-]
  1. var
  2.   sSQL : string;
  3.   MyQUERY : TSQLQuery;
  4. begin
  5.   MyQUERY := TSQLQuery.Create(nil);
  6.   MyQUERY.Transaction := SQLTransaction1;
  7.   sSQL := 'INSERT INTO tnDATA (fnVBITRATE) VALUES (1234)';
  8.   MyQUERY.SQL.Add(sSQL);
  9.   MyQUERY.ExecSQL;
  10.   MyQUERY.Free;
  11.   SQLTransaction1.Commit;  // <- add this
  12.  

af0815

  • Hero Member
  • *****
  • Posts: 556
Re: Sqlite - Write changes to file
« Reply #4 on: May 13, 2020, 02:01:02 pm »
You can use ApplyUpdates on the query or use the (sqoAutoCommit +) sqoAutoApplyUpdates under Options of the query.

See https://wiki.freepascal.org/SqlDBHowto in the middle of the article
« Last Edit: May 13, 2020, 02:02:57 pm by af0815 »
regards
Andreas

P.curtis

  • Jr. Member
  • **
  • Posts: 80
Re: Sqlite - Write changes to file
« Reply #5 on: May 13, 2020, 02:14:56 pm »
OK, both options are good. Thanks.

Zoran

  • Hero Member
  • *****
  • Posts: 1574
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Sqlite - Write changes to file
« Reply #6 on: May 13, 2020, 02:22:05 pm »
add this befor
Code: Pascal  [Select][+][-]
  1. MyQUERY.post
  2.  

You can use ApplyUpdates on the query or use the (sqoAutoCommit +) sqoAutoApplyUpdates under Options of the query.

See https://wiki.freepascal.org/SqlDBHowto in the middle of the article

These posts are misleading, for this particular example you should not use neither Post nor ApplyUpdates, these are for updating previously opened (active) datasets, which this is not.

Korba812 gave the correct answer.

P.curtis

  • Jr. Member
  • **
  • Posts: 80
Re: Sqlite - Write changes to file
« Reply #7 on: May 13, 2020, 02:32:51 pm »
Noted. Thanks.

 

TinyPortal © 2005-2018