If TFDBatchmove suitable for one way syncing data between 2 sqlite databases?

[Jasonjac2 0]

I have to periodically push sync (Make destination the same as source) between 2 databases (Eventually FB to SQLite, but for now sqlite to sqlite).  I have sync'd datasets a number of times over my programming career and have some specific to the use case and some more generic solutions.

 

As the datasets are relatively small I decided to try using TFDBatchmove in the hope it might not be super efficient, but would be a case of hooking up some components and pressing the Go button :-).  A day later and I still haven't got it working.

I intended building from a very trivial example.  I also had a look at the samples for D12.1, which in a classic sample fashion wasn't super clear to read the code.

 

So Q1) is it suitable to us use the batchmove components in this way?

Current test: 2 identical tables in sqlite, run it once, it fills the table (that actually works)

Modify the destination table (e.g. blank a few field values)

Rerun and initially I get an exception "Not Implemented"

By trial and error I add in an event handler for OnFindDestRecord - which I thought I wouldn't need as I am using TFDTable, the sqlite table has a primary key and the field structures are identical.

Now I have hand coded the locate from source to destination it gives me: 

Project FDBatchMoveScratch.exe raised exception class EFDDBEngineException with message '[FireDAC][Phys][SQLite]-312. Exact update affected [0] rows, while [1] was requested'.

Project FDBatchMoveScratch.exe raised exception class EFDException with message '[FireDAC][DApt]-400. Update command updated [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user'.

Adding in event handlers for all of the batch move events, to try and work out what is going on - it is trying to update the 1st row, eventhough the row is identical.

 

So I am clearly missing something (or a lot of things).

 

For info - I have 2 FDConnections, an FDBatchMove, in fact to avoid ambiguity, I've pasted the objects below .

 

Q2) Can anyone point me to a decent walk through of setting up one way sync and resync.

 

I could have hand coded a compare, update, insert, delete routine by hand in this time :-(.

 

As always, thank you for your time with this. 

 

object FDBatchMove1: TFDBatchMove
  Reader = FDBatchMoveDataSetReader1
  Writer = FDBatchMoveDataSetWriter1
  Mode = dmAppendUpdate
  Options = [poIdentityInsert, poSkipUnmatchedDestFields, poUseTransactions]
  Mappings = <>
  LogFileAction = laAppend
  LogFileName = 'Data.log'
  OnProgress = FDBatchMove1Progress
  OnFindDestRecord = FDBatchMove1FindDestRecord
  OnWriteValue = FDBatchMove1WriteValue
  OnWriteRecord = FDBatchMove1WriteRecord
  Left = 384
  Top = 216
end
object FDBatchMoveDataSetReader1: TFDBatchMoveDataSetReader
  DataSet = tblsrcWebUsers
  Left = 520
  Top = 120
end
object FDBatchMoveDataSetWriter1: TFDBatchMoveDataSetWriter
  Direct = True
  DataSet = tbldestWebUsers
  Left = 512
  Top = 296
end
object tbldestWebUsers: TFDTable
  IndexFieldNames = 'ID'
  Connection = FDConnectionSQLiteDest
  ResourceOptions.AssignedValues = [rvEscapeExpand]
  TableName = 'WEB_USERS'
  Left = 376
  Top = 288
  object tbldestWebUsersID: TIntegerField
    FieldName = 'ID'
    Origin = 'ID'
    ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
    Required = True
  end
  object tbldestWebUsersUSERNAME: TStringField
    FieldName = 'USERNAME'
    Origin = 'USERNAME'
    Required = True
    Size = 50
  end
  object tbldestWebUsersROLE: TStringField
    FieldName = 'ROLE'
    Origin = 'ROLE'
    Size = 50
  end
  object tbldestWebUsersIS_ACTIVE: TIntegerField
    FieldName = 'IS_ACTIVE'
    Origin = 'IS_ACTIVE'
  end
  object tbldestWebUsersCOMPANY_NAME: TStringField
    FieldName = 'COMPANY_NAME'
    Origin = 'COMPANY_NAME'
    Size = 50
  end
end

 

[Jasonjac2 0]

OK, things move on. I needed to set the  FDBatchMoveDataSetWriter1.direct to false and it worked.

"FDBatchMoveDataSetWriter1.direct to false.

Looking at the help on TFDBatchMoveDataSetWriter.direct I can't see why it wouldn't work with sqlite.

 

Now I have it working for this use case, I stripped out some of the random changes I made to try and get it working (removed the FDBatchMove1FindDestRecord), removed the persistent fields etc.

 

I took another look at the sample and was definitely none the wiser about merging data.

So I inch further on, still asking for any decent blogs / walkthroughs / gems of knowledge.

Things I know that are coming my way if I continue down this path:

- It doesn't help me decide if there are any records that need removing.  Normally I use a "ladder" approach, organising the source and destination in such a way that if all rows are in both, then you can just .next on each dataset, but if there is a row in dest where the calculated PK is < the current calculated source PK, then it needs to be deleted.

-- anyone dealt with this in the TFDBatchMove world?

- Currently this is all sqlite to sqlite with identical field structures just to build the example, moving on, I it will need to cater for pulling a joined SQL from the src and pushing into a denormalised table in sqlite.

TIA 

Regards,

Jason