Recent

Author Topic: [SQLite3] Change data while in a Repeat Loop?  (Read 867 times)

QEnnay

  • Full Member
  • ***
  • Posts: 115
[SQLite3] Change data while in a Repeat Loop?
« on: October 27, 2020, 10:40:39 pm »
Hi, I have the following SQLite Database process.
Code: Pascal  [Select][+][-]
  1.   folders.First;
  2.   repeat
  3.     thisFolder:=folders.FieldByName('fldName').AsString;
  4.     thisID:=folders.FieldByName('fldID').AsInteger;
  5.     if (thisID) > 1200 then
  6.     begin
  7.       // display thisFolder Name etc
  8.     end;
  9.     // All good so far, but I'd now like to update a Field in this Folder to store the date.
  10.     folders.SQL.Text:='UPDATE folders SET fldDate = DATETIME(''now'',''localtime'') '+
  11.         ' WHERE fldID = '+IntToStr(thisID)+';';
  12.     folders.ExecSQL;
  13.     folders.Next;
  14.   until (folders.EOF);
  15.  

Is this going to work OK or will it mess up the repeat-until processing of the folders table?

How do I do this?

I have to do this with Live data and cannot afford to screw up the entire database, Table or Record.

Thanks
Linux-Mint 20.1 x64 + Cinnamon; Lenovo Flex 5 Ryzen 5 4500, 16GB memory
FPC: 3.2.0-1, Lazarus 2.0.12-0, all 64bit

PierceNg

  • Sr. Member
  • ****
  • Posts: 369
    • SamadhiWeb
Re: [SQLite3] Change data while in a Repeat Loop?
« Reply #1 on: October 28, 2020, 01:17:25 am »
Code: Pascal  [Select][+][-]
  1.   folders.First;
  2.   repeat
  3.     thisFolder:=folders.FieldByName('fldName').AsString;
  4.     thisID:=folders.FieldByName('fldID').AsInteger;
  5.     if (thisID) > 1200 then
  6.     begin
  7.       // display thisFolder Name etc
  8.     end;
  9.     // All good so far, but I'd now like to update a Field in this Folder to store the date.
  10.     folders.SQL.Text:='UPDATE folders SET fldDate = DATETIME(''now'',''localtime'') '+
  11.         ' WHERE fldID = '+IntToStr(thisID)+';';
  12.     folders.ExecSQL;
  13.     folders.Next;
  14.   until (folders.EOF);
  15.  

Is this going to work OK or will it mess up the repeat-until processing of the folders table?

Your UPDATE runs for every row within the loop. Why not run a single UPDATE outside the loop to update the entire set returned by your SELECT that fed the loop?

Also, if you are only displaying rows with ID > 1200, then just SELECT those rows with a WHERE.


egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: [SQLite3] Change data while in a Repeat Loop?
« Reply #2 on: October 28, 2020, 03:06:08 am »
No. That will not work.

First, folders is (assumed to be) a dataset descendant, possiblty TSQLQuery. If you'd like to change SQL text of it, and the content is lost.  So you have to use different query objects. So

Code: Pascal  [Select][+][-]
  1.   folders2.SQL.Text := 'UPDATE folders SET fldDate = DATETIME(''now'',''localtime'') '+
  2.         ' WHERE fldID = :fldid;';
  3.  
  4.   folders.first;
  5.   repeat
  6.        ................
  7.  
  8.        folders2.Close;
  9.        folders2.ParamByName('fldid').AsString := thisID;
  10.        folders2.ExecSQL;
  11.        
  12.        folders.Next;
  13.   end;
  14.  

Second, not sure whether tis is your intention, but even you may change the content of database within loop of an open dataset, then changes will not be reflected at the open dataset itself. You have to commit changes first and reopen it with a new transaction.

QEnnay

  • Full Member
  • ***
  • Posts: 115
Re: [SQLite3] Change data while in a Repeat Loop?
« Reply #3 on: October 28, 2020, 07:01:18 pm »

changes will not be reflected at the open dataset itself. You have to commit changes first and reopen it with a new transaction.

Ahhhh, OK.

Thank you, I had not thought about the Commit later. I had assumed that anything I did would mess with the current state.

I think I saw "Auto-Commit" set somewhere in the Query or Source. The SQL is not yet a strong suit for me to mess with. I think I need to practice with  subset of the Live data.

Linux-Mint 20.1 x64 + Cinnamon; Lenovo Flex 5 Ryzen 5 4500, 16GB memory
FPC: 3.2.0-1, Lazarus 2.0.12-0, all 64bit

QEnnay

  • Full Member
  • ***
  • Posts: 115
Re: [SQLite3] Change data while in a Repeat Loop?
« Reply #4 on: October 28, 2020, 07:04:49 pm »
if you are only displaying rows with ID > 1200, then just SELECT those rows with a WHERE.

Thanks, there are other conditions but I had to type that all from memory as the real thing is on a PC in a building half a mile away and no Internet, so I took the liberty of just showing the gist of the issue.
Linux-Mint 20.1 x64 + Cinnamon; Lenovo Flex 5 Ryzen 5 4500, 16GB memory
FPC: 3.2.0-1, Lazarus 2.0.12-0, all 64bit

 

TinyPortal © 2005-2018