Updates to backend database via RemoteDB

Hi,


I am very interested in RemoteDB and the very limited testing I have done with select statements to a backend SQL Server database have worked perfectly but I have to admit (I apologise in advance for what may seem a stupid question) that performing updates has got me rather confused. In the documentation it does say that TXDataset does not update records automatically and then goes on to say that you must manually provide the code to do this using the OnRecordUpdate, OnRecordInsert, OnRecordDelete events but I'm not clear how.

On a normal update to a SQL database I would have an ADOTable definition, would call "Open", "Insert", "Post" etc to insert the records and the job would be done but how do I do the same sort of thing using TXDataset and RemoteDB?

Is there any chance you can provide a small example to point me in the right direction?

Many thanks & Regards,

Graham

Hello Graham,


basically what happens is that when you call "Post", the even OnRecordUpdate (or OnRecordInsert, if it's a new record) is fired. In that event it's totally up to you to grab the field values, build the SQL (Update <table> Set <field1> = :param1, <field2> = :param2 Where <keyfield> = :keyparam) and fill in the param values based on the TField values available in the dataset

Hi Wagner,


I've been doing some "playing around" and I can do an insert via TXDataset using a straight forward SQL insert into statement and that works as expected however in your response you say "when you call 'Post' the OnRecordUpdate event is fired" which of course doesn't happen if I am doing the SQL statement directly via TXDataset so it seems to me (and maybe I'm reading what you have said incorrectly) that one could have some sort of table where one would use post e.g.

atable.insert;
atable.fieldbyname('field1').asstring := 'String1';
atable.fieldbyname('field2').asstring := 'String2';
atable.post;
atable.close

and then when post is called the OnRecordUpdate event would fire and then (I assume) within the event I would create and execute a SQL update statement (or insert into for a new record). However I suspect I am not understanding something here because I don't see how one could have a table component because there is no way to link the table to the backend database unless of course I directly create the connection string.

As you can tell I'm still a little confused as to how things "fit together" when using RemoteDB, I have no problems with the actual SQL statements themselves, I know how to create those, what I don't understand exactly how the OnRecordUpdate/OnRecordInsert events get called and what causes them to be called, apologies for being a bit "stupid" on this.

Thanks,

Graham

Hi Graham, 

TXDataset is not linked to a specific table, but to an SQL statement, yes (a SELECT statement since you are opening the dataset to retrieve data).
But the dataset is editable. After the data is retrieved from the server, you can edit the records in dataset, so you can call Edit/Insert, and then Post. But calling Post does nothing other than calling the mentioned events (OnRecordUpdate if you are editing a record, or OnRecordInsert if you are inserting/appending a record). All data is kept by TXDataset in memory only, no further server connection is done after initial data is retrieved.
Thus, in the OnRecordUpdate event, you need to build your UPDATE SQL statement manually, fill the desired param values with the current field values of the dataset (since they contain the data that user has just filled) and then you need indeed to user another TXDataset to execute that SQL statement in the server. Then you get your server database updated and in sync with the changes that user has made in memory.
Is the clearer now? No need to apologize, please. Just keep asking if it's not clear, I'm here for that.

Ah ha, I think the smoke is starting to clear, many thanks :-)

If anyone is interested, I created 3 "generic" Insert/Update/Delete procedures you could attach to any TXDatatSet that you would like to automatically create SQL and submit in a 2nd query automatically. All that you need beyond the standard TXDataSet properties is 2 new strings - UpdatingTable and KeyFields - you can SubClass TXDataSet or change the source to do this. Here are the 3 events that create the SQL for you. 

procedure TfmClientMain.XDataset1RecordDelete(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal : TStringList;
  xUpdateDataset : TXDataset;
begin
  If (((Sender as TXDataset).UpdatingTable = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg('Primary Key and UpdatingTable Required for Live Updates',mtWarning,[mbOK],0);
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'DELETE FROM ' + (Sender as TXDataset).UpdatingTable + ' ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + slKeyVal[i1] else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + slKeyVal[i1] else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    sSQL := sSQL + sWhereClause;
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := sSQL;
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
    //ShowMessage(sSQL);
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

procedure TfmClientMain.XDataset1RecordInsert(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey : TStringList;
  xUpdateDataset : TXDataset;
begin
  If (((Sender as TXDataset).UpdatingTable = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg('Primary Key and UpdatingTable Required for Live Updates',mtWarning,[mbOK],0);
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
    end;

    bFirstField := True;
    sSQL := 'INSERT INTO ' + (Sender as TXDataset).UpdatingTable + ' (';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If bFirstField then
      begin
        sSQL := sSQL + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
        bFirstField := False;
      end else  sSQL := sSQL + ',' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
    end;
    sSQL := sSQL + ') VALUES (';
    bFirstField := True;
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''' + ',';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + FormatFloat('#########0.0############',dNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime('mm/dd/yyyy',dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime('hh:mm:ss am/pm',dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime('mm/dd/yyyy hh:mm:ss am/pm',dtNewValue) + ''''+ ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + ''''+ ',';
      end;
    end;
    sSQL := Copy(sSQL,1,Length(sSQL)-1);
    sSQL := sSQL + ')';
    ShowMessage(sSQL);
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := sSQL;
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
  Finally
    slKey.Free;
  end;
end;

procedure TfmClientMain.XDataset1RecordUpdate(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal : TStringList;
  xUpdateDataset : TXDataset;
begin
  If (((Sender as TXDataset).UpdatingTable = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg('Primary Key and UpdatingTable Required for Live Updates',mtWarning,[mbOK],0);
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'UPDATE ' + (Sender as TXDataset).UpdatingTable + ' SET ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
      If bFirstField then
      begin
        sSQL := sSQL + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
        bFirstField := False;
      end else  sSQL := sSQL + ', ' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + FormatFloat('#########0.0############',dNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime('mm/dd/yyyy',dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime('hh:mm:ss am/pm',dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime('mm/dd/yyyy hh:mm:ss am/pm',dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + slKeyVal[i1] else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + slKeyVal[i1] else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    sSQL := sSQL + sWhereClause;
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := sSQL;
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
    //ShowMessage(sSQL);
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

Rhett Price
IndySoft

Hi, do you have other examples, because I try to use these I got to many errors


Thanks 


procedure TDM1.XDataset1RecordDelete(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal : TStringList;
  xUpdateDataset : TXDataset;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg(SPrimaryKeyandUpdateTableNam,mtWarning,[mbOK],0); // Primary Key and UpdateTableName Required for Live Updates
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sPrimaryKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'DELETE FROM ' + (Sender as TXDataset).UpdateTableName + ' ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    sSQL := sSQL + sWhereClause;
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := AddN(sSQL);
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
    //ShowMessage(sSQL);
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

procedure TDM1.XDataset1RecordInsert(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sFields, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey : TStringList;
  xUpdateDataset : TXDataset;
  procedure AddFieldName;
  begin
    If bFirstField then
    begin
      sFields := sFields + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
      bFirstField := False;
    end else  sFields := sFields + ',' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
  end;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg(SPrimaryKeyandUpdateTableNam,mtWarning,[mbOK],0); // Primary Key and UpdateTableName Required for Live Updates
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
    end;

    sFields := ''; // default
    bFirstField := True;
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''' + ',';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IndyFormatFloat('#########0.0############',dNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL,' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultTimeFormat,dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL,' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + ''''+ ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + ''''+ ',';
      end;
    end;

    sSQL := 'INSERT INTO ' + (Sender as TXDataset).UpdateTableName + ' (' + sFields + ') VALUES (' + sSQL;

    sSQL := Copy(sSQL,1,Length(sSQL)-1);
    sSQL := sSQL + ')';
    //ShowMessage(sSQL);
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := AddN(sSQL);
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
  Finally
    slKey.Free;
  end;
end;

procedure TDM1.XDataset1RecordUpdate(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause, sMemo : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal, slTemp : TStringList;
  msTemp : TMemoryStream;
  xUpdateDataset : TXDataset;
  procedure AddFieldName;
  begin
    If bFirstField then
    begin
      sSQL := sSQL + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
      bFirstField := False;
    end else  sSQL := sSQL + ', ' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
  end;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    MessageDlg(SPrimaryKeyandUpdatingTableR,mtWarning,[mbOK],0); // Primary Key and UpdatingTable Required for Live Updates
  end;
  If (Sender as TXDataset).ModifiedFields.Count = 0 then exit;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sPrimaryKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'UPDATE ' + (Sender as TXDataset).UpdateTableName + ' SET ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IndyFormatFloat('#########0.0############',dNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultTimeFormat,dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime(DM1.TriDef1.sDefaultDateFormat, dtNewValue) + '''';
        end;
      end;
      {If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;}
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    if sSQL <> 'UPDATE ' + (Sender as TXDataset).UpdateTableName + ' SET ' then
    begin
      sSQL := sSQL + sWhereClause;
      xUpdateDataset := TXDataset.Create(Self);
      Try
        xUpdateDataset.Database := (Sender as TXDataset).Database;
        xUpdateDataset.SQL.Text := AddN(sSQL);
        xUpdateDataset.Execute;
      Finally
        xUpdateDataSet.Free;
      end;
      // ShowMessage(sSQL);
    end;

    // already have sWhereClause - check for any blob fields
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sMemo := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue);
        slTemp := TStringList.Create;
        try
          slTemp.Text := sMemo;
          DM1.UpdateACommentWithList((Sender as TXDataset).UpdateTableName, (Sender as TXDataset).ModifiedFields[i1-1].FieldName, sWhereClause, slTemp);
        finally
          slTemp.Free;
        end;
      end;

      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftBlob] then
      begin
        msTemp := TMemoryStream.Create;
        try
          ((Sender as TXDataset).ModifiedFields[i1-1] as TBlobField).SaveToStream(msTemp);
          DM1.UpdateABlobWithStream((Sender as TXDataset).UpdateTableName, (Sender as TXDataset).ModifiedFields[i1-1].FieldName, sWhereClause, msTemp);
        finally
          msTemp.Free;
        end;
      end;
    end;
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

I added 2 properties to TXDataSet - UpdateTableName and KeyFields

I set KeyFields to the Primary key (multi-field primary key separate with ; )

I then assign those procedures when UpdateTableName isn't blank.

  If (qTemp as TXDataset).UpdateTableName <> '' then
  begin
    (qTemp as TXDataset).OnRecordDelete := XDataset1RecordDelete;
    (qTemp as TXDataset).OnRecordUpdate := XDataset1RecordUpdate;
    (qTemp as TXDataset).OnRecordInsert := XDataset1RecordInsert;
  end else
  begin
    (qTemp as TXDataset).OnRecordDelete := nil;
    (qTemp as TXDataset).OnRecordUpdate := nil;
    (qTemp as TXDataset).OnRecordInsert := nil;
  end;

You can then use Insert/Edit/Post/Delete

Hi, it has the same error 

UpdateTableName  these property do not exist so it cant compile 
Do you have any test or demo that really work  ?


In case of Rhett's code, UpdateTableName is probably a property he added to the dataset because he inherited from TXDataSet. You can simply put it in some local variable and provide the table name in it. 

We intend to add this out of the box to RemoteDB, but still you might find that you need some specific code for insert/update/delete.

Yes, sorry for delay on this. We added 2 new string properties to TXDataset: UpdateTableName and KeyFields


Then, upon form creation we loop through any TXDatasets that have a UpdateTablename and set their Delete/Update/Insert methods to 3 base procedures we have created.

          If TXDataSet(fForm.components[iControlLoop]).UpdateTableName <> '' then
          begin
            TXDataSet(fForm.components[iControlLoop]).OnRecordDelete := XDataset1RecordDelete;
            TXDataSet(fForm.components[iControlLoop]).OnRecordUpdate := XDataset1RecordUpdate;
            TXDataSet(fForm.components[iControlLoop]).OnRecordInsert := XDataset1RecordInsert;
          end else
          begin
            TXDataSet(fForm.components[iControlLoop]).OnRecordDelete := nil;
            TXDataSet(fForm.components[iControlLoop]).OnRecordUpdate := nil;
            TXDataSet(fForm.components[iControlLoop]).OnRecordInsert := nil;
          end;

Here are the 3 procedures. There are some string constants you would need to replace. I'd be glad to answer any other questions. It has worked very well for us.

procedure TDM1.XDataset1RecordDelete(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal : TStringList;
  xUpdateDataset : TXDataset;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    IndyMessageDlg(SPrimaryKeyandUpdateTableNam,mtWarning,[mbOK],0); // Primary Key and UpdateTableName Required for Live Updates
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sPrimaryKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'DELETE FROM ' + (Sender as TXDataset).UpdateTableName + ' ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    sSQL := sSQL + sWhereClause;
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := AddN(sSQL);
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

procedure TDM1.XDataset1RecordInsert(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sFields, sPrimaryKey, sKey, sWhereClause : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey : TStringList;
  xUpdateDataset : TXDataset;
  procedure AddFieldName;
  begin
    If bFirstField then
    begin
      sFields := sFields + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
      bFirstField := False;
    end else  sFields := sFields + ',' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName ;
  end;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    IndyMessageDlg(SPrimaryKeyandUpdateTableNam,mtWarning,[mbOK],0); // Primary Key and UpdateTableName Required for Live Updates
  end;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
    end;

    sFields := ''; // default
    bFirstField := True;
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''' + ',';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IndyFormatFloat('#########0.0############',dNewValue) + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL,' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultTimeFormat,dtNewValue) + '''' + ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL,' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL,' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + ''''+ ',';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + ''''+ ',';
      end;
    end;

    sSQL := 'INSERT INTO ' + (Sender as TXDataset).UpdateTableName + ' (' + sFields + ') VALUES (' + sSQL;

    sSQL := Copy(sSQL,1,Length(sSQL)-1);
    sSQL := sSQL + ')';
    xUpdateDataset := TXDataset.Create(Self);
    Try
      xUpdateDataset.Database := (Sender as TXDataset).Database;
      xUpdateDataset.SQL.Text := AddN(sSQL);
      xUpdateDataset.Execute;
    Finally
      xUpdateDataSet.Free;
    end;
  Finally
    slKey.Free;
  end;
end;

procedure TDM1.XDataset1RecordUpdate(Sender: TObject);
Var
  i1, iNewValue, iKeyFieldPos : Integer ;
  sSQL, sPrimaryKey, sKey, sWhereClause, sMemo : String ;
  dNewValue : Double;
  bFirstField : Boolean;
  dtNewValue : TDateTime;
  slKey, slKeyVal, slTemp : TStringList;
  msTemp : TMemoryStream;
  xUpdateDataset : TXDataset;
  procedure AddFieldName;
  begin
    If bFirstField then
    begin
      sSQL := sSQL + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
      bFirstField := False;
    end else  sSQL := sSQL + ', ' + (Sender as TXDataset).ModifiedFields[i1-1].FieldName + '=';
  end;
begin
  If (((Sender as TXDataset).UpdateTableName = '') or ((Sender as TXDataset).KeyFields = '')) then
  begin
    IndyMessageDlg(SPrimaryKeyandUpdatingTableR,mtWarning,[mbOK],0); // Primary Key and UpdatingTable Required for Live Updates
  end;
  If (Sender as TXDataset).ModifiedFields.Count = 0 then exit;
  // Parse Primary Keys into StringList
  slKey := TStringList.Create;
  slKeyVal := TStringList.Create;
  Try
    sPrimaryKey := (Sender as TXDataset).KeyFields;
    If Pos(';',sPrimaryKey) > 0 then // Check to see if parsing necessary
    begin
      sKey := '';
      For i1 := 1 to Length(sPrimaryKey) do
      begin
        If sPrimaryKey[i1] = ';' then
        begin
          slKey.Add(sKey);
          slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
          sKey := '';
        end else sKey := sKey + sPrimaryKey[i1];
      end;
      If sKey <> '' then
      begin
        slKey.Add(sKey);
        slKeyVal.Add((Sender as TXDataset).FieldByName(sKey).AsString);
      end;
    end else
    begin
      slKey.Add(sPrimaryKey);
      slKeyVal.Add((Sender as TXDataset).FieldByName(sPrimaryKey).AsString);
    end;
    bFirstField := True;
    sSQL := 'UPDATE ' + (Sender as TXDataset).UpdateTableName + ' SET ';
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      iKeyFieldPos := slKey.IndexOf((Sender as TXDataset).ModifiedFields[i1-1].FieldName);
      If iKeyFieldPos > -1 then // Primary Key is being modified - adjust list for Where clause accordingly
      begin
        slKeyVal[iKeyFieldPos] := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].OldValue);
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftString, ftFixedChar, ftWideString] then
      begin
        AddFieldName;
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftSmallint, ftInteger, ftWord, ftLargeint] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          iNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IntToStr(iNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftFloat, ftCurrency, ftBCD] then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + IndyFormatFloat('#########0.0############',dNewValue);
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDate then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultDateFormat,dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          sSQL := sSQL + '''' + FormatDateTime(TriDef1.sDefaultTimeFormat,dtNewValue) + '''';
        end;
      end;
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType = ftDateTime then
      begin
        AddFieldName;
        If VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) = '' then sSQL := sSQL + 'NULL' else
        begin
          dtNewValue := (Sender as TXDataset).ModifiedFields[i1-1].NewValue;
          if dtNewValue <= 0 then sSQL := sSQL + 'NULL' else
            sSQL := sSQL + '''' + FormatDateTime(DM1.TriDef1.sDefaultDateFormat, dtNewValue) + '''';
        end;
      end;
      {If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sSQL := sSQL + '''' + VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue) + '''';
      end;}
    end;
    //Build Where Clause
    bFirstField := True;
    For i1 := 0 to slKey.Count-1 do
    begin
      If bFirstField then
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := ' WHERE ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := ' WHERE ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
        bFirstField := False;
      end else
      begin
        If (Sender as TXDataset).FieldByName(slKey[i1]).DataType in [ftFloat, ftCurrency, ftBCD, ftSmallint, ftInteger, ftWord, ftLargeint] then
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=' + FixDecimalSeparator(slKeyVal[i1]) else
            sWhereClause := sWhereClause + ' AND ' + slKey[i1] + '=''' + slKeyVal[i1] + '''';
      end;
    end;
    if sSQL <> 'UPDATE ' + (Sender as TXDataset).UpdateTableName + ' SET ' then
    begin
      sSQL := sSQL + sWhereClause;
      xUpdateDataset := TXDataset.Create(Self);
      Try
        xUpdateDataset.Database := (Sender as TXDataset).Database;
        xUpdateDataset.SQL.Text := AddN(sSQL);
        xUpdateDataset.Execute;
      Finally
        xUpdateDataSet.Free;
      end;
    end;

    // already have sWhereClause - check for any blob fields
    For i1 := 1 to (Sender as TXDataset).ModifiedFields.Count do
    begin
      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftMemo, ftWideMemo] then
      begin
        sMemo := VarToStr((Sender as TXDataset).ModifiedFields[i1-1].NewValue);
        slTemp := TStringList.Create;
        try
          slTemp.Text := sMemo;
          DM1.UpdateACommentWithList((Sender as TXDataset).UpdateTableName, (Sender as TXDataset).ModifiedFields[i1-1].FieldName, sWhereClause, slTemp);
        finally
          slTemp.Free;
        end;
      end;

      If (Sender as TXDataset).ModifiedFields[i1-1].DataType in [ftBlob] then
      begin
        msTemp := TMemoryStream.Create;
        try
          ((Sender as TXDataset).ModifiedFields[i1-1] as TBlobField).SaveToStream(msTemp);
          DM1.UpdateABlobWithStream((Sender as TXDataset).UpdateTableName, (Sender as TXDataset).ModifiedFields[i1-1].FieldName, sWhereClause, msTemp);
        finally
          msTemp.Free;
        end;
      end;
    end;
  Finally
    slKey.Free;
    slKeyVal.Free;
  end;
end;

Thanks,

Rhett


I spotted some functions in there. 

function TDM1.FixDecimalSeparator(s1 : String) : String ;
Var
  i, l : Integer ;
  sTemp : String ;
begin
  If FormatSettings.DecimalSeparator <> '.' then
  begin
    sTemp := s1;
    L := Length(sTemp);
    for I := 1 to L do
    begin
       if sTemp = FormatSettings.DecimalSeparator then
          sTemp := '.';
    end;
    Result := sTemp;
  end else Result := s1;
end;

AddN simply adds an N'  ' in front of strings to accommodate Unicode databases.

DM1.TriDef1.sDefaultDateFormat is simply your databases default format in string form.

- Rhett

Hi, Bro I Getting a huge amount of error that really I dont understand 

Could check code in order to verify what is happening 

Sure, on vacation at the moment but when I get back i can make it more generic. You got your UpdateTableName and KeyFields properties added? 

Hi Rhett,
Thank you so much for the code examples, they are working great.

Only one question: What is the AddN() function/procedure doing for us?
Example from your code: xUpdateDataset.SQL.Text:= AddN( sSQL );

I see it now .. AddN(sql) = 'N'+sql;

Hi,
there are progress on obtain an "ApplyUpdates" features (automatic and native) to RemoteDB?
It seems natural to me that this functionality serves to complete the functioning of RemoteDB.
Thanks

This topic is rather old.
In version 2.0 (2019), TMS RemoteDB added the AutoApply property so backend database update is done automatically by RemoteDB in the most common use cases.

Also, cached updates are leveraged to TClientDataset, there is no need to reinvent the wheel in our opinion. You can use TXDataset as a dataset provider to TClientDataset and use all its features together with RemoteDB.

The goal is to use only one set of dataset components, for optimization and ease of development. In applications with a large number of tables (and consequently a large number of datasets on the forms) multiplying the components becomes confusing and cumbersome.
However, if you intend to interact with other old components (like TClientdataset) that I want to eliminate, I accept your philosophy, even if I don't agree.
:-)