Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am wondering why the data posted on my clientdataset are not updating to MySQL Database table using the ApplyUpdates.

I am working on 2 databases.

I added the primary key on DB2.CustomerTable2 with 'ListID' and still not working.

Below the codes, I am working with.

procedure TfrmMain.spbExportClick(Sender: TObject);
  Guid , VarAccountId, VarListSasId, VarListDspId : Variant;
  Status : String;
begin
  with dm.dmForm do
  begin
    cdsCustomer2.first;
    while not cdsCustomer2.eof do
    begin
      //variable data for CDS.Customer1.AllFields
      Guid := cdsCustomer2.FieldByName ('Guid').AsString;
      VarAccountId := cdsCustomer2.FieldByName('ListID').AsString;
      VarListSasId := cdsCustomer2.FieldByName('FullName').AsString;
      VarListDspId := cdsCustomer2.FieldByName('Name').AsString;
      Status := 'Out';
      //posting to CDS.Customer1.AllFields
      cdsCustomer1.DisableControls;
      cdsCustomer1.Append;
      cdsCustomer1.FieldByName('GUID').AsString := Guid;
      cdsCustomer1.FieldByName('AccountId').AsString := VarAccountId;
      cdsCustomer1.FieldByName('ListSasID').AsString := VarListSasId;
      cdsCustomer1.FieldByName('ListDspID').AsString := VarListDspId;
      cdsCustomer1.FieldByName('Status').AsString := Status;
      cdsCustomer1.EnableControls;
      cdsCustomer1.Fields[1].ProviderFlags := [pfInKey];
      cdsCustomer1.Post;
      //posting Guid value back to CDS.Customer2
      if cdsCustomer2.locate('ListID', VarAccountId, [])  then
      begin
        cdsCustomer2.DisableControls;
        cdsCustomer2.Edit;
        cdsCustomer2.FieldByName('ExternalGUID').AsString := Guid;
        cdsCustomer2.EnableControls;
        cdsCustomer2.Fields[0].ProviderFlags := [pfInKey];
        cdsCustomer2.Post;
      cdsCustomer2.Next;
    //ApplyUpdates to mysql Customer1.Table and Customer2.Table
    cdsCustomer1.ApplyUpdates(-1);
    cdsCustomer2.ApplyUpdates(-1);
I expect these codes will simply post to my MySQL database2 just like the database 1. Everything works up to posting to both ClientDataSets except posting of cdsCustomer2 of DB2.
Let me know if I missed some information here that you might need.
Here's the database structure by the way:
UniConnection -> MySQLUniProvider -> UniQuery -> DataSetProvider -> ClientDataSet -> DataSource -> DBGrid

                The cdsCustomer2.locate call looks superfluous given that the key value is taken from the current record before. Also the comment before the DisableControls call looks not related to the actual code.
– Uwe Raabe
                May 13, 2019 at 7:55
Diagnosing ApplyUpdates problems can be a bit tedious because sometimes it's a matter of grinding through a number of possibilities unti you find the one which applies to your situation,  so I can't tell you "just do this ..." and it will fix your problem.
However, before you start looking at the possibilities, there are a couple of problems with your code which need fixing, otherwise you are unlikely to get anywhere.
Your calls to ApplyUpdates
cdsCustomer1.ApplyUpdates(-1);
cdsCustomer2.ApplyUpdates(-1);
Change this to
  var Count : Integer;
  [...]
  Count := cdsCustomer1.ApplyUpdates(0);
  Assert(Count = 0);
  Count := cdsCustomer2.ApplyUpdates(0);
  Assert(Count = 0);
The point is, specifying -1 as the argument to ApplyUpdates does the
exact opposite to what you want, i.e. it allows any number of errors
to be generated in the ApplyUpdates process.  What you want is for it to
stop on any error, which is what 0 will do, so you can find out
what error is being returned during ApplyUpdates.
When you re updating cdsCustomer2, by mistake you are calling
cdsCustomer1.DisableControls instead of cdsCustumer2.DisableControls.  As it stands, this mistake will prevent the correct display of cdsCustomer1 afterwards.
Make those changes, compile and run your app and maybe one of the two
Count := [...] willl give you an exception mesage which identifies the
cause of your problem.  If it doesn't:
Check that on your server, both tables have a primary key defined, and
then check that the correct fields of your CDSs have the pfInkey provider
flag set.  If that checks out, try setting them to pfInWhere.
In the VCL source file Provider.Pas, find the procedure
procedure TSQLResolver.InternalDoUpdate(Tree: TUpdateTree; UpdateKind: TUpdateKind);
Its final line should be
DoExecSQL(FSQL, FParams);
Put a breakpoint on it, run your app, and when it stops on the BP, evaluate
FSQL and see if it looks correct.  If it does, try executing the same SQL from whatever MySql utility you use for working with your server.
                As usual, I appreciate your very detailed response. I have a corrected the error cdsCustomer2.DisableControls to cdsCustomer1.DisableControls. On the other hand, I was able to figure out the issue. I mentioned about 2 databases that I am working with. On my data module, I only have one database TUniConnection and that is for cdsCustomer1. I do not have TUniConnection for the other database intended for cdsCustomer2.
– Mel
                May 13, 2019 at 11:49
                But what is more surprising was, I was able to read data of the table for cdsCustomer2 in the absence of its database TUniConnection. Except, on edit level, which was my main issue. After creating a separate TUniConnection, I was able to push data to DB2.CustomerTable2.
– Mel
                May 13, 2019 at 11:50
                Regarding the ApplyUpdates, I just want to know if this is the standard coding that I will be using moving forward or in this case only. Right now, I have separated the 2 ApplyUpdates and attached right after every post?
– Mel
                May 13, 2019 at 11:55
                It is up to you whether you call .ApplyUpdates after each .Post, depending on the data-integrity requirements of your application - postponing them risks that and also severely annoying your users if they don't find out immediately if the updates cannot be applied.
– MartynA
                May 13, 2019 at 15:15
                By the way @MartynA, I have changed to 0 instead of -1 already. I am just a bit confused if after separated those, will I still do these Count := cdsCustomer1.ApplyUpdates(0);   Assert(Count = 0);?
– Mel
                May 13, 2019 at 20:59
After reproducing this case to isolate where exactly the error is coming from. I realized that I haven't created a connection for the other database. So, the solution for this case is to create connections for each database in order to communicate seamlessly with 2 databases.
To illustrate further, here's the connection I have now and I don't get any errors so far in synchronizing 2 databases:
Database1 —> TUniConnection1 —> MySQLUniProvider1 —> TUniQuery1 —> TDataSetProvider1 —> TClientDataSet1 —> TDataSource1 —> TDBGrid1
Database2 —> TUniConnection2 —> MySQLUniProvider2 —> TUniQuery2 —> TDataSetProvider2 —> TClientDataSet2 —> TDataSource2 —> TDBGrid2
I am not sure if there is a better way to manage several database connections at the same time in a project. But, this works for me.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.