CODEWITHSUNDEEP
mysqldelphi
Mel
Mel

Reputation: 101

My ClientDataSet.ApplyUpdates is not posting to my MySQL Database table?

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.

  1. DB1.CustomerTable1: No issue, primary key at 'ListID' —> CDS.Append —> CDS.ApplyUpdates

  2. DB2.CustomerTable2: With issue, primary key at 'Guid' —> CDS.Edit —> CDS.ApplyUpdates.

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);
var
  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;
      end;

      cdsCustomer2.Next;
    end;

    //ApplyUpdates to mysql Customer1.Table and Customer2.Table
    cdsCustomer1.ApplyUpdates(-1);
    cdsCustomer2.ApplyUpdates(-1);

  end;
end;

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.

P.S. Here's the database structure by the way: UniConnection -> MySQLUniProvider -> UniQuery -> DataSetProvider -> ClientDataSet -> DataSource -> DBGrid

Upvotes: 1

Views: 580

Answers (3)

Mel
Mel

Reputation: 101

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.

Upvotes: 0

RichardS
RichardS

Reputation: 536

Add a raise exception to the OnUpdateError of the DataSetProvider - by default exceptions are silent, which I've never quite understood. So something like Raise Exception (E.Message) - you may find you're trying to null a field which has to be non-null, or similar.

Upvotes: 0

MartynA
MartynA

Reputation: 30715

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.

Also

  • 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.

Upvotes: 1

Related Questions