Recent

Author Topic: [Solved] Sqlite Update on Joined Table - Parameter Not Found  (Read 442 times)

Wilko500

  • Full Member
  • ***
  • Posts: 101
[Solved] Sqlite Update on Joined Table - Parameter Not Found
« on: October 31, 2024, 01:35:49 am »
I have been struggling with this for a while, lots of googleing and testing but I cannot find a working example of recordset update on joined tables.  I need to update a field on a recordset based on joined tables, there may be 1 or more records to be updated on each call to this code.  I have read that update on joined tables is not supported in SQLite but can be achieved by using the dbQuery.UpdateSQL.Text method.  I have also read that the values in this method should/must be parameterised.

I have found the documentation very confusing in that (I think) the parameters are validated against the dbQuery.SQL.Text when the query is opened but also that dbQuery.UpdateSQL.Text values must be parameterised so I don't get how both can happen because the former is getting the recordset (without parameters) and the latter is updating the recordset (with parameters).  Please may I have guidance on what am I getting wrong 

In my code if I hard code  strUpDateSql:= line then the update works.  My attempt to use a parameter results in
Code: Pascal  [Select][+][-]
  1. 00:09 - 2024-10-31 In Proc PvUploadProcessing=>  EDatabaseError—Parameter “pTime” not found

Code: Pascal  [Select][+][-]
  1.    OpenDb(strDbFullPath);
  2.     strSQLStatus:= 'SELECT DAYS.DAYID AS DayId, DAYS.DATE,  TIMES.TIME,  TIMES.Uploaded,  TIMES.PAC,  TIMES.ENERGY ' +
  3.                'FROM Days Inner Join Times on Days.dayid = Times.DayId ' +
  4.                'WHERE  TIMES.UPLOADED = ''N'' AND DAYS.DATE  > date(''now'', ''-13 day'') ' +
  5.                'ORDER BY DAYS.DATE ,  TIMES.TIME  ' +
  6.                'LIMIT ' + IntToStr(g.MaxLiveRecs) + ';';
  7.  
  8.     strUpDateSql:='Update Times set Uploaded = "Y" where Dayid = "3181" and Time = :pTime';
  9. //    strUpDateSql:='Update Times set Uploaded = "Y" where Dayid = "3181" and Time = "12:38"';  //This works
  10.  
  11.          dbQuery.SQL.Text:=strSQLStatus;
  12.          dbQuery.UpdateSQL.Text:=strUpDateSql;
  13.          dBTrans.Active:=True;
  14.          dbQuery.Open;
  15.  
  16.     dbQuery.First;
  17.  
  18.     iUp:=0;
  19.     iNotUp:=0;
  20.     //For each record in recordset
  21.     For i:= 1 To iCount Do
  22.         Begin
  23.         //Check if date and time match
  24.         If (DelChars(dBQuery.FieldByName('Date').AsString, '-') = strArray[i-1,0]) AND
  25.                    (dBQuery.FieldByName('Time').AsString = strArray[i-1,1]) Then
  26.             //Date and Time match
  27.             Begin
  28.             //So test if web update successful
  29.             If strArray[i-1,2] = '1' Then
  30.                 //Successful, update database Uploaded field
  31.                 Begin
  32.                 dbQuery.Params.ParamByname('pTime').AsString:=strArray[i-1,1];
  33.                 dbQuery.Edit;
  34.                 dBQuery.FieldByName('Uploaded').AsString:= 'Y';
  35.                 dbQuery.ApplyUpdates;
  36.                 iUp:=iUp + 1
  37.                 End
  38.             Else
  39.                 //Not successful, do not update Uploaded field
  40.                 Begin
  41.                 iNotUp:=iNotUp + 1;
  42.             End;{If}
  43.             End
  44.         Else
  45.             //Data mismatch
  46.             Begin
  47.             //Report
  48.         End;{If}
  49.         dbQuery.Next;
  50.     End;{For}
  51.     dbTrans.Commit;
  52.     dbQuery.Close;

« Last Edit: November 01, 2024, 12:02:19 am by Wilko500 »
MacBook Pro mid 2015 with OS Monterey 12.7.6
FPC 3.3.1 Lazarus 3.99
FPC 3.2.2 Lazarus 3.4

GAN

  • Sr. Member
  • ****
  • Posts: 376
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #1 on: October 31, 2024, 02:30:45 am »
Quote
I have read that update on joined tables is not supported in SQLite.
That is true, we all agree.
One option might be to create a temporary table from the joined tables, for example temptable1. Then do the update based on temptable1. Finally, empty or delete temptable1.
I hope it's useful, I haven't programmed in SQL for a couple of years.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

paweld

  • Hero Member
  • *****
  • Posts: 1249
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #2 on: October 31, 2024, 07:32:12 am »
Additional queries like, for example, UpdateSQL must not contain parameters that are not present in the main query (SQL property).

But you can use parameters with field names, then the value of the specified field from the current record will be automatically inserted in those places. you will get the original value of that field by prefixing the field name with OLD_.
Code: Pascal  [Select][+][-]
  1.   OpenDb(strDbFullPath);
  2.   strSQLStatus := 'SELECT DAYS.DAYID AS DayId, DAYS.DATE,  TIMES.TIME,  TIMES.Uploaded,  TIMES.PAC,  TIMES.ENERGY ' +
  3.     'FROM Days Inner Join Times on Days.dayid = Times.DayId ' +
  4.     'WHERE  TIMES.UPLOADED = ''N'' AND DAYS.DATE  > date(''now'', ''-13 day'') ' +
  5.     'ORDER BY DAYS.DATE ,  TIMES.TIME  ' + 'LIMIT ' + IntToStr(g.MaxLiveRecs) + ';';
  6.  
  7.   strUpDateSql := 'Update Times set Uploaded = :Uploaded where Dayid = :DayId and Time = :Time and Uploaded = :OLD_Uploaded';
  8.  
  9.   dbQuery.SQL.Text := strSQLStatus;
  10.   dbQuery.UpdateSQL.Text := strUpDateSql;
  11.   dBTrans.Active := True;
  12.   dbQuery.Open;
  13.  
  14.   dbQuery.First;
  15.  
  16.   iUp := 0;
  17.   iNotUp := 0;
  18.   //For each record in recordset
  19.   for i := 1 to iCount do
  20.   begin
  21.     //Check if date and time match
  22.     if (DelChars(dBQuery.FieldByName('Date').AsString, '-') = strArray[i - 1, 0]) and
  23.       (dBQuery.FieldByName('Time').AsString = strArray[i - 1, 1]) then
  24.       //Date and Time match
  25.     begin
  26.       //So test if web update successful
  27.       if strArray[i - 1, 2] = '1' then
  28.         //Successful, update database Uploaded field
  29.       begin
  30.         ////dbQuery.Params.ParamByname('pTime').AsString:=strArray[i-1,1];   //this line is not necessary!
  31.         dbQuery.Edit;
  32.         dBQuery.FieldByName('Uploaded').AsString := 'Y';
  33.         dbQuery.ApplyUpdates;
  34.         iUp := iUp + 1;
  35.       end
  36.       else
  37.         //Not successful, do not update Uploaded field
  38.       begin
  39.         iNotUp := iNotUp + 1;
  40.       end;{If}
  41.     end
  42.     else
  43.       //Data mismatch
  44.     begin
  45.       //Report
  46.     end;{If}
  47.     dbQuery.Next;
  48.   end;{For}
  49.   dbTrans.Commit;
  50.   dbQuery.Close;
  51.  
Best regards / Pozdrawiam
paweld

Zvoni

  • Hero Member
  • *****
  • Posts: 2715
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #3 on: October 31, 2024, 08:15:58 am »
What utter nonsense.

Create a View out of your inital Select-Statement (Without the LIMIT-Clause), use an INSTEAD OF-Trigger (https://www.sqlitetutorial.net/sqlite-instead-of-triggers/),
and just fire off your Update as if it were a single table.

I wouldn't touch those UpdateSQL, InsertSQL-Statements (and whatever else) with a long poker

Your code would change to (UNTESTED!!)
Code: Pascal  [Select][+][-]
  1. strSQLStatus :='SELECT * FROM MyView LIMIT '+IntToStr(g.MaxLiveRecs) + ';';
  2.  
  3. dbQuery.SQL.Text := strSQLStatus;
  4. dbQuery.Open;
  5.  
  6. //Do Something....
  7.  
  8. dbQuery.Close;
  9.  
  10. dbQuery.SQL.Text := 'UPDATE MyView SET Uploaded = :Uploaded where Dayid = :DayId and Time = :Time and Uploaded = :OLD_Uploaded;';
  11. dbQuery.ParamByName('Uploaded').AsString:='Y';
  12. dbQuery.ParamByName('DayID').AsInteger:=3181;
  13. dbQuery.ParamByName('Time').AsString:='12:38';
  14. dbQuery.ParamByName('OLD_Uploaded').AsString:='N';
  15. dbQuery.ExecSQL;
  16. //Transaction.CommitRetaining;???
  17.  
  18. dbQuery.SQL.Text := strSQLStatus;
  19. dbQuery.Open;
  20.  

And for all that is holy: Don't separate Date from Time

....and be careful with SQLite's "Date"-Function, especially using "now" --> it returns UTC. Not your local Date/Time
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

Wilko500

  • Full Member
  • ***
  • Posts: 101
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #4 on: October 31, 2024, 02:02:13 pm »
Thank you for your comments and suggestions. In them I see potential solutions and maybe the reason why my code cannot work. I am out on site all day so I will respond more fully tonight.
MacBook Pro mid 2015 with OS Monterey 12.7.6
FPC 3.3.1 Lazarus 3.99
FPC 3.2.2 Lazarus 3.4

Wilko500

  • Full Member
  • ***
  • Posts: 101
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #5 on: November 01, 2024, 12:01:50 am »
So there are two aspects to my question.  Firstly, why try to use UpDateSQL in the first place.  I am converting an existing VB6 program that uses Access 2003 database. Since that program has been working for many years I did not wish to do a full re-design and re-coding unless it became absolutely necessary.  In Access it is an easy task to open a recordset then edit records as one moves through that recordset forwards (or backwards).  When done commit those changes or roll back.  I was trying to follow that logic and UpDateSQL seemed like a way to do that.

Additional queries like, for example, UpdateSQL must not contain parameters that are not present in the main query (SQL property).

But you can use parameters with field names, then the value of the specified field from the current record will be automatically inserted in those places. you will get the original value of that field by prefixing the field name with OLD_.

Thank you for this it might be the missing explanation and perhaps is the reason why I can't use UpDateSQL in the way I hoped to.  However, it seems to me that if the parameters can only apply values from the original query result then the process is largely useless. Perhaps I mis-understand, but I thought the whole point of parameters was to put a different value into the SQL, that is different to what is already there in the resultant dataset.


And for all that is holy: Don't separate Date from Time

....and be careful with SQLite's "Date"-Function, especially using "now" --> it returns UTC. Not your local Date/Time

Both point noted.  I was copying my previous database structure and since Sqlite can only store date/time as text I figured that keeping them separate makes tests and comparisons easier?

Moving to the second aspect, alternative ways to achieve the desired updates, I am grateful for your suggestions.

@Zvoni I had not thought of using a view and I can see how it would work.

@Gan temporary table, yes indeed.  I did use this approach when I was converting the MA Access database to SQLite because there was a data mis match between Access and SQLite. It worked well.

I have thought about how to move forward and I will probably use the recordset from my joined query to populate a TStringArray and then I can run a simple Update query  on table Times rather like the suggested code by @Zvoni.

Since I now have a better idea of what caused the Parameter Not Found and ideas of alternative approaches I will mark this thread as solved.

Thank you all
MacBook Pro mid 2015 with OS Monterey 12.7.6
FPC 3.3.1 Lazarus 3.99
FPC 3.2.2 Lazarus 3.4

Wilko500

  • Full Member
  • ***
  • Posts: 101
Re: Sqlite Update on Joined Table - Parameter Not Found
« Reply #6 on: November 01, 2024, 10:35:17 pm »

Thank you for this it might be the missing explanation and perhaps is the reason why I can't use UpDateSQL in the way I hoped to.  However, it seems to me that if the parameters can only apply values from the original query result then the process is largely useless. Perhaps I mis-understand, but I thought the whole point of parameters was to put a different value into the SQL, that is different to what is already there in the resultant dataset.

@paweld,  yes I did mis-understand.  I pondered over your post for some time trying to get my head round how the parameters work.  YES, I get it, and now that part of my code is working :)  What's more I can use this approach in another procedure where I faced a similar need and had a somewhat clumsy workaround. 

Thank you so much.
MacBook Pro mid 2015 with OS Monterey 12.7.6
FPC 3.3.1 Lazarus 3.99
FPC 3.2.2 Lazarus 3.4

 

TinyPortal © 2005-2018