Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Updates to backend database via RemoteDB

 Post Reply Post Reply
Author
Fuller Graham View Drop Down
New Member
New Member
Avatar

Joined: 04 Jun 2013
Posts: 11
Post Options Post Options   Quote Fuller Graham Quote  Post ReplyReply Direct Link To This Post Topic: Updates to backend database via RemoteDB
    Posted: 07 Sep 2014 at 3:09pm
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
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 09 Sep 2014 at 9:02am
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
Back to Top
Fuller Graham View Drop Down
New Member
New Member
Avatar

Joined: 04 Jun 2013
Posts: 11
Post Options Post Options   Quote Fuller Graham Quote  Post ReplyReply Direct Link To This Post Posted: 12 Sep 2014 at 5:47am
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

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 12 Sep 2014 at 7:36am
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.
Back to Top
Fuller Graham View Drop Down
New Member
New Member
Avatar

Joined: 04 Jun 2013
Posts: 11
Post Options Post Options   Quote Fuller Graham Quote  Post ReplyReply Direct Link To This Post Posted: 12 Sep 2014 at 8:34am
Ah ha, I think the smoke is starting to clear, many thanks :-)
Back to Top
Price Rhett View Drop Down
New Member
New Member
Avatar

Joined: 11 Feb 2012
Posts: 33
Post Options Post Options   Quote Price Rhett Quote  Post ReplyReply Direct Link To This Post Posted: 02 Oct 2014 at 2:46pm
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

Back to Top
Gonzalez Wascar View Drop Down
New Member
New Member
Avatar

Joined: 20 Mar 2018
Posts: 3
Post Options Post Options   Quote Gonzalez Wascar Quote  Post ReplyReply Direct Link To This Post Posted: 20 Mar 2018 at 8:35pm
Hi, do you have other examples, because I try to use these I got to many errors

Thanks 


Back to Top
Price Rhett View Drop Down
New Member
New Member
Avatar

Joined: 11 Feb 2012
Posts: 33
Post Options Post Options   Quote Price Rhett Quote  Post ReplyReply Direct Link To This Post Posted: 21 Mar 2018 at 7:05pm
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

Back to Top
Gonzalez Wascar View Drop Down
New Member
New Member
Avatar

Joined: 20 Mar 2018
Posts: 3
Post Options Post Options   Quote Gonzalez Wascar Quote  Post ReplyReply Direct Link To This Post Posted: 24 May 2018 at 8:49pm
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  ?


Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 05 Jun 2018 at 12:06am
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.
Back to Top
Price Rhett View Drop Down
New Member
New Member
Avatar

Joined: 11 Feb 2012
Posts: 33
Post Options Post Options   Quote Price Rhett Quote  Post ReplyReply Direct Link To This Post Posted: 05 Jun 2018 at 4:58am
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


Back to Top
Price Rhett View Drop Down
New Member
New Member
Avatar

Joined: 11 Feb 2012
Posts: 33
Post Options Post Options   Quote Price Rhett Quote  Post ReplyReply Direct Link To This Post Posted: 05 Jun 2018 at 5:02am
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

Back to Top
Gonzalez Wascar View Drop Down
New Member
New Member
Avatar

Joined: 20 Mar 2018
Posts: 3
Post Options Post Options   Quote Gonzalez Wascar Quote  Post ReplyReply Direct Link To This Post Posted: 08 Jun 2018 at 4:10pm
Hi, Bro I Getting a huge amount of error that really I dont understand 
Could check code in order to verify what is happening 

Back to Top
Price Rhett View Drop Down
New Member
New Member
Avatar

Joined: 11 Feb 2012
Posts: 33
Post Options Post Options   Quote Price Rhett Quote  Post ReplyReply Direct Link To This Post Posted: 08 Jun 2018 at 4:23pm
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? 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down