[Solved] Sqlite Update on Joined Table - Parameter Not Found

[Wilko500]

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;

[GAN]

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.

[paweld]

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.  

[Zvoni]

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

[Wilko500]

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.

[Wilko500]

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

[Wilko500]

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.