[Solved] Sqlite Update on Joined Table - Parameter Not Found
lazarus / 16-Databases

----------------------------------------------------------------------
Wilko500 · 2024-10-31 00:35

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 00:09 -
2024-10-31 In Proc PvUploadProcessing=>  EDatabaseError—Parameter
“pTime” not found
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 OpenDb(strDbFullPath);    strSQLStatus:= 'SELECT DAYS.DAYID AS DayId,
DAYS.DATE,  TIMES.TIME,  TIMES.Uploaded,  TIMES.PAC,  TIMES.ENERGY '
+               'FROM Days Inner Join Times on Days.dayid =
Times.DayId ' +               'WHERE  TIMES.UPLOADED = ''N'' AND
DAYS.DATE  > date(''now'', ''-13 day'') ' +               'ORDER BY
DAYS.DATE ,  TIMES.TIME  ' +               'LIMIT ' +
IntToStr(g.MaxLiveRecs) + ';';     strUpDateSql:='Update Times set
Uploaded = "Y" where Dayid = "3181" and Time = :pTime';//  
 strUpDateSql:='Update Times set Uploaded = "Y" where Dayid = "3181"
and Time = "12:38"';  //This works        
 dbQuery.SQL.Text:=strSQLStatus;       
 dbQuery.UpdateSQL.Text:=strUpDateSql;         dBTrans.Active:=True; 
      dbQuery.Open;     dbQuery.First;     iUp:=0;    iNotUp:=0;   
//For each record in recordset    For i:= 1 To iCount Do        Begin 
      //Check if date and time match        If
(DelChars(dBQuery.FieldByName('Date').AsString, '-') =
strArray[i-1,0]) AND                 
 (dBQuery.FieldByName('Time').AsString = strArray[i-1,1]) Then       
   //Date and Time match            Begin            //So test if web
update successful            If strArray[i-1,2] = '1' Then           
   //Successful, update database Uploaded field                Begin 
            
dbQuery.Params.ParamByname('pTime').AsString:=strArray[i-1,1];       
       dbQuery.Edit;               
dBQuery.FieldByName('Uploaded').AsString:= 'Y';               
dbQuery.ApplyUpdates;                iUp:=iUp + 1                End 
         Else                //Not successful, do not update Uploaded
field                Begin                iNotUp:=iNotUp + 1;        
  End;{If}            End        Else            //Data mismatch     
     Begin            //Report        End;{If}        dbQuery.Next;  
End;{For}    dbTrans.Commit;    dbQuery.Close;

----------------------------------------------------------------------
GAN · 2024-10-31 01:30

QuoteI 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 · 2024-10-31 06:32

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
OpenDb(strDbFullPath);  strSQLStatus := 'SELECT DAYS.DAYID AS DayId,
DAYS.DATE,  TIMES.TIME,  TIMES.Uploaded,  TIMES.PAC,  TIMES.ENERGY '
+    'FROM Days Inner Join Times on Days.dayid = Times.DayId ' +   
'WHERE  TIMES.UPLOADED = ''N'' AND DAYS.DATE  > date(''now'', ''-13
day'') ' +    'ORDER BY DAYS.DATE ,  TIMES.TIME  ' + 'LIMIT ' +
IntToStr(g.MaxLiveRecs) + ';';   strUpDateSql := 'Update Times set
Uploaded = :Uploaded where Dayid = :DayId and Time = :Time and
Uploaded = :OLD_Uploaded';   dbQuery.SQL.Text := strSQLStatus; 
dbQuery.UpdateSQL.Text := strUpDateSql;  dBTrans.Active := True; 
dbQuery.Open;   dbQuery.First;   iUp := 0;  iNotUp := 0;  //For each
record in recordset  for i := 1 to iCount do  begin    //Check if date
and time match    if (DelChars(dBQuery.FieldByName('Date').AsString,
'-') = strArray[i - 1, 0]) and     
(dBQuery.FieldByName('Time').AsString = strArray[i - 1, 1]) then     
//Date and Time match    begin      //So test if web update
successful      if strArray[i - 1, 2] = '1' then        //Successful,
update database Uploaded field      begin       
////dbQuery.Params.ParamByname('pTime').AsString:=strArray[i-1,1];  
//this line is not necessary!        dbQuery.Edit;       
dBQuery.FieldByName('Uploaded').AsString := 'Y';       
dbQuery.ApplyUpdates;        iUp := iUp + 1;      end      else      
//Not successful, do not update Uploaded field      begin       
iNotUp := iNotUp + 1;      end;{If}    end    else      //Data
mismatch    begin      //Report    end;{If}    dbQuery.Next; 
end;{For}  dbTrans.Commit;  dbQuery.Close;

----------------------------------------------------------------------
Zvoni · 2024-10-31 07:15

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
strSQLStatus :='SELECT * FROM MyView LIMIT '+IntToStr(g.MaxLiveRecs) +
';'; dbQuery.SQL.Text := strSQLStatus;dbQuery.Open; //Do
Something.... dbQuery.Close; dbQuery.SQL.Text := 'UPDATE MyView SET
Uploaded = :Uploaded where Dayid = :DayId and Time = :Time and
Uploaded = :OLD_Uploaded;';dbQuery.ParamByName('Uploaded').AsString:='
Y';dbQuery.ParamByName('DayID').AsInteger:=3181;dbQuery.ParamByName('T
ime').AsString:='12:38';dbQuery.ParamByName('OLD_Uploaded').AsString:=
'N';dbQuery.ExecSQL;//Transaction.CommitRetaining;??? dbQuery.SQL.Text
:= strSQLStatus;dbQuery.Open; 
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 · 2024-10-31 13:02

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 · 2024-10-31 23:01

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.

Quote from: paweld on October 31, 2024, 07:32:12 amAdditional 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.

Quote from: Zvoni on October 31, 2024, 08:15:58 am
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 · 2024-11-01 21:35

Quote from: Wilko500 on November 01, 2024, 12:01:50 am
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.