TFDQuery failing to update?

Question

I'm having a problem with a synchronisation issue... I have a source table (mtAllowanceCategory) which I want to update to a copy (qryAllowanceCategory) of it. To make sure records in the copy are deleted if they are no longer present in the source, the copy has a "StillHere" boolean field, which is set to on when the record is added or updated and otherwise stays off. Afterwards, all records with StillHere=false are deleted.

That's the idea, anyway... in practice, the flag fields isn't turned on when posting updates. When I trace the code, the statement is executed; when I look in Access, it stays off. Hence the delete SQL afterwards clears the entire table.

Been trying to figure this for hours now; what am I missing??

mtAllowanceCategory:TFDMemTable (filled from an API call, this works fine)
qryAllowanceCategory:TFDQuery
conn:TFDConnection to a local Access database (also used for qryAllowanceCategory)

  conn.ExecSQL('UPDATE AllowanceCategory SET StillHere=false;');

  while not mtAllowanceCategory.eof do
  begin
    if qryAllowanceCategory.locate('WLPid',mtAllowanceCategory.FieldByName('Id').AsString,[loCaseInsensitive]) then
    begin
      Updating:=true;
      qryAllowanceCategory.Edit;
    end
    else
    begin
      Updating:=false;
      qryAllowanceCategory.Insert;
    end;
    qryAllowanceCategory.fieldbyname('createdBy').AsString:=mtAllowanceCategory.FieldByName('createdBy').AsString;
    qryAllowanceCategory.fieldbyname('createdOn').AsString:=mtAllowanceCategory.FieldByName('createdOn').AsString;
    qryAllowanceCategory.fieldbyname('description').AsString:=mtAllowanceCategory.FieldByName('description').AsString;
    qryAllowanceCategory.fieldbyname('WLPid').AsString:=mtAllowanceCategory.FieldByName('id').AsString;
    qryAllowanceCategory.fieldbyname('isDeleted').Asboolean:=mtAllowanceCategory.FieldByName('isDeleted').Asboolean;
    qryAllowanceCategory.fieldbyname('isInUse').Asboolean:=mtAllowanceCategory.FieldByName('isInUse').Asboolean;
    qryAllowanceCategory.fieldbyname('modifiedBy').AsString:=mtAllowanceCategory.FieldByName('modifiedBy').AsString;
    qryAllowanceCategory.fieldbyname('modifiedOn').AsString:=mtAllowanceCategory.FieldByName('modifiedOn').AsString;
    qryAllowanceCategory.fieldbyname('WLPname').AsString:=mtAllowanceCategory.FieldByName('name').AsString;
    qryAllowanceCategory.fieldbyname('number').AsInteger:=mtAllowanceCategory.FieldByName('number').AsInteger;
    qryAllowanceCategory.fieldbyname('percentage').AsFloat:=mtAllowanceCategory.FieldByName('number').AsFloat;
    qryAllowanceCategory.fieldbyname('remark').AsString:=mtAllowanceCategory.FieldByName('remark').AsString;
    qryAllowanceCategory.fieldbyname('LocalEdited').AsBoolean:=false;
    qryAllowanceCategory.fieldbyname('LocalInserted').AsBoolean:=false;
    qryAllowanceCategory.fieldbyname('LocalDeleted').AsBoolean:=false;
    qryAllowanceCategory.fieldbyname('StillHere').AsBoolean:=true;
    qryAllowanceCategory.Post;
    mtAllowanceCategory.next;
  end;
  conn.commit;
  conn.ExecSQL('DELETE FROM AllowanceCategory WHERE StillHere=false;');

Answer 1

When I read your q, I was struck by two thoughts:

One was that I couldn't immediately see the cause of your problem and the other that you could probably avoid the problem anyway if you used Sql rather than table traversals in code.

It seemed to me that you might be able to do most if not all of what you need, in terms of synchronising the two tables, using Access Sql rather than traversing the qryAllowanceCategory table using a while not EOF loop. (btw, in the following I'm going to use 'mtAC' and qryAC to reduce typing & typos)

Using Access SQL

Initially, I did not have much luck, as Access rejected my attempts to refer to both tables in an Update statement against the qryAC one using a Join or Outer Join, but then I came across a reference that showed that Access does support an Inner Join syntax. These SQL statements execute successfully by calling ExecSQL on the FireDAC connection to the database:

update qryAC set qryAC.StillHere = True 
  where exists(select mtAC.* from mtAC inner join qryAC on mtAC.WLPid = qryAC.WLPid)

and

update qryAC inner join mtAC on mtAC.WLPid = qryAC.WLPid set qryAC.AValue = mtAC.AValue

This first of these obviously provides a way to update the StillHere field to set it to True, or False with a trivial modification.

The second shows a way to update a set of fields in qryAC from the matching rows in mtAC and this could, of course, be limited to a subset of rows with a suitable Where clause.

Access Sql also supports checking whether a row in one table exists in the other, as in

select * from qryAC q where exists (select * from mtac m where q.wlpid = m.wlpid)

and for deleting rows in one table which do not exist in the other

delete from qryAC q where not exists (select * from mtac m where q.wlpid = m.wlpid)

Using FireDAC's LocalSQL

I also mentioned LocalSQL in a comment. This supports a far broader range of Sql statements that native Access Sql and can operate on any TDataSet descendant, so if you find something that Access Sql syntax doesn't support, it is worth considering using LocalSQL instead. Its main downside is that it operates on the datasets using traversals, so in not quite as "instant" as native Sql. It can be a bit tricky to set up, so here are the settings from the DFM which show how the components need connecting up. You would use it by feeding what you want to FDQuery1.

object AccessConnection: TFDConnection Params.Strings = ( 'Database=D:\Delphi\Code\FireDAC\LocalSQL\Allowance.accdb' 'DriverID=MSAcc') Connected = True LoginPrompt = False end

object mtAC: TFDQuery AfterOpen = mtACAfterOpen Connection = AccessConnection SQL.Strings = ( 'select * from mtAC') end

object qryAC: TFDQuery Connection = AccessConnection end

object LocalSqlConnection: TFDConnection Params.Strings = ( 'DriverID=SQLite') Connected = True LoginPrompt = False end

object FDLocalSQL1: TFDLocalSQL Connection = LocalSqlConnection DataSets = < item DataSet = mtAC end item DataSet = qryAC end> end

object FDGUIxWaitCursor1: TFDGUIxWaitCursor Provider = 'Forms' end

object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink end

object FDQuery1: TFDQuery Connection = LocalSqlConnection end

Answer 2

If anyone is interested: The problem was in not refreshing qryAllowanceCategory after the initial SQL setting StillHere to false. The memory version (qryAllowanceCategory) of the record didn't get that update, so according to him, the flag was still on; after the field updates it appeared there were no changes (all the other fields were unchanged as well) so the post was ignored. In the actual table it was off though, so the final delete SQL removed it.

The problem was solved by adding a refresh after the first UPDATE SQL statement.