I was changing an app from last year to use TVirtualTable in a batch move (TCRBatchMove) instead of TUniQuery/TUniTable with SqlLite on Android (horrible performance I move 10k registers into a SqlLite database in Android, takes my S7 egde about 45 seconds). If I use a TVirtualTable, the time reduces to 2 seconds with saving it onto the SD card.
For queries I use TVirtualQuery. Those are awesome.
Now the problem:
I have a office table which is a sub detail of a customer table. Each customer can have N offices. So the key is CusterId + OfficeId. In the VirtualTable component I put those in the IndexFieldNames property.
When I sincronize from the server any change to the mobile app, I transfer only offices that had changes in certain fields since I last sincronized.
The BatchMove uses for source and destination a VirtualTable with IndexFieldNames property set (unlike the TDACustomDataset those have no keyfields property). The mode is bmAppendUpdate, which should update only if the register already exists. But it inserts doubles and with every time they syncronize they are more :(
As a workaround I now delete all offices that are syncronized and let them insert again, but it shouldn't be like that, should it ?
My Code is this :
Code: Select all
procedure TdmDatabase.UpdateTable(DataId: Integer; DS: TDataSet; LU: TDateTime);
var DestTable: TDataSet;
S : String;
BM : TCRBatchMove;
i: Integer;
Qry : TUniQuery;
ID_N: Int64;
SucCliente : Integer;
DoBreak: Boolean;
begin
If Not FpTableList.TryGetValue(DataId, DestTable) Then begin
ShowMessage('Cannot find table for DataId ' + DataId.ToString);
Exit;
end;
Qry := nil;
BM := TCRBatchMove.Create(Self);
Case DataId Of
DATABASE_ID_CUST, // Customers
DATABASE_ID_ITEM, // products
DATABASE_ID_SHIPTO : begin // shipto = offices
BM.Mode := bmAppendUpdate;
TVirtualTable(DS).IndexFieldNames := TVirtualTable(DestTable).IndexFieldNames;
// so if we're syncing offices...
If DataId = DATABASE_ID_SHIPTO Then begin
DS.First;
While not DS.EoF Do begin
ID_N := DS.FieldByName('ID_N').AsLargeInt;
SucCliente := DS.FieldByName('SucCliente').AsInteger;
DoBreak := False;
Repeat
If DestTable.Locate(TVirtualTable(DestTable).IndexFieldNames, VarArrayOf([ID_N, SucCliente]), []) Then
DestTable.Delete
Else
DoBreak := True;
Until DoBreak;
DS.Next;
end;
DS.First;
end;
end;
Else begin
OpenTable(DataId);
If DataId = DATABASE_ID_PEDIDOE_SERVER Then
BM.Mode := bmUpdate
Else begin
Qry := OpenQuery(DATABASE_ID_QR);
Qry.SQL.Text := 'SELECT Count(*) FROM ' + TUniTable(DestTable).TableName;
Qry.Open;
If Qry.RecordCount = 0 Then
BM.Mode := bmAppend
Else
BM.Mode := bmAppendUpdate;
end;
end;
end;
BM.OnBatchMoveProgress := CRBatchMove1BatchMoveProgress;
BM.FieldMappingMode := mmFieldName;
BM.Destination := DestTable;
BM.Source := DS;
try
BM.Execute;
Case DataId Of
DATABASE_ID_CUST,
DATABASE_ID_ITEM,
DATABASE_ID_SHIPTO : TriggerSaveDatabase(DataId); // save the VirtualTable content
Else Transaction.Commit; // any other table is in the sqllite database and get's a commit
end;
SetLastSync(DataId, LU);
If Assigned(Qry) Then
CloseTable(Qry);
except
on E: Exception do begin
If Assigned(Qry) Then
CloseTable(Qry);
Transaction.Rollback;
ShowMessage('Error: ' + E.Message);
end;
end;
FreeAndNil(BM);
DbConnection.Connected := False;
end;
Any ideas ? Suggestions ?
Thanks a lot
Helge