dfdquery.sql.text := ‘select a.*, b.* from a left join b on a.id = b.id
fdqery.cacheupdate := true; //This makes it easier to roll back multi-table errors
fdquery.updateoptions.updatenobasefields := true; //Sub table fields can be edited
fdquery.updateobject := TFdUpdateSqL; or fdquery.onupdaterecord event.
Here is an example:
procedure TForm2.Button3Click(Sender: TObject); var ne:Integer; begin qrym.CheckBrowseMode; if not qrym.UpdatesPending then Exit; qrym.UpdateObject := FDUpdateSQL1; ne := qrym.ApplyUpdates(0); qrym.UpdateObject := FDUpdateSQL2; ne := ne + qrym.ApplyUpdates(0); if ne = 0 then begin qrym.CommitUpdates; ShowMessage('ok'); end else ShowMessage('Error'); end;
Using events
procedure TfrmCachedUpdates.qrProductsUpdateRecord(ASender: TDataSet; ARequest: TFDUpdateRequest; var AAction: TFDErrorAction; AOptions: TFDUpdateRowOptions); begin usProducts.ConnectionName := qrProducts.ConnectionName; usProducts.DataSet := qrProducts; usProducts.Apply(ARequest, AAction, AOptions); usCategories.ConnectionName := qrProducts.ConnectionName; usCategories.DataSet := qrProducts; usCategories.Apply(ARequest, AAction, AOptions); AAction := eaApplied; end;
Another way is possible:
var qrya, qryb:TFDuqey; qrya.sql = 'select * from a'; qryb.sql := 'select * from b'; In the qry.onupdaterecord event implement qry copies records to qrya, qry copies records to qryb, Update qrya and qryb respectively. qry executes applyupdate, commitupdate.
Attached is a method to generate SQL commands
{********************************************** *********} { } { Delphi FireDAC Framework } { FireDAC TFDUpdateSQL editor form } { } { Copyright(c) 2004-2021 Embarcadero Technologies, Inc. } { All rights reserved } { } {************************************************ ******} {$I FireDAC.inc} unit FireDAC.VCLUI.USEdit; interface uses {$IFDEF MSWINDOWS} Winapi.Messages, Winapi.Windows, {$ENDIF} System.SysUtils, System.Classes, Vcl.Graphics, Vcl.Forms, Vcl.ComCtrls, Vcl.StdCtrls, Vcl.ExtCtrls, Vcl.Controls, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Comp.Client, FireDAC.VCLUI.OptsBase, FireDAC.VCLUI.UpdateOptions, FireDAC.VCLUI.Controls, FireDAC.VCLUI.Memo; type TfrmFDGUIxFormsUSEdit = class(TfrmFDGUIxFormsOptsBase) pcMain: TPageControl; tsGenerate: TTabSheet; Label1: TLabel; cbxTableName: TComboBox; btnDSDefaults: TButton; btnGenSQL: TButton; btnServerInfo: TButton; GroupBox2: TLabel; lbKeyFields: TListBox; GroupBox3: TLabel; lbUpdateFields: TListBox; GroupBox4: TLabel; lbRefetchFields: TListBox; tsSQL: TTabSheet; tsOptions: TTabSheet; ptreeOptions: TFDGUIxFormsPanelTree; GroupBox5: TPanel; cbQuoteTabName: TCheckBox; cbQuoteColName: TCheckBox; frmUpdateOptions: TfrmFDGUIxFormsUpdateOptions; Bevel4: TBevel; Bevel1: TBevel; Bevel5: TBevel; pcSQL:TPageControl; tsInsert: TTabSheet; tsModify: TTabSheet; tsDelete: TTabSheet; tsLock: TTabSheet; tsUnlock: TTabSheet; tsFetchRow: TTabSheet; procedure cbxTableNameDropDown(Sender: TObject); procedure btnServerInfoClick(Sender: TObject); procedure btnDSDefaultsClick(Sender: TObject); procedure btnGenSQLClick(Sender: TObject); procedure cbxTableNameExit(Sender: TObject); procedure FormCreate(Sender: TObject); procedure mmSQLExit(Sender: TObject); procedure mmSQLKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState); procedure cbxTableNameChange(Sender: TObject); procedure cbxTableNameClick(Sender: TObject); private FConnection: TFDCustomConnection; FDataSet: TFDAdaptedDataSet; FUpdateSQL: TFDUpdateSQL; FOpts: IFDStanOptions; procedure UpdateExistSQLs; procedureGenCommands; function GetSQL(AIndex: Integer): TFDGUIxFormsMemo; function ExecuteBase(AUpdSQL: TFDUpdateSQL; const ACaption: String): Boolean; function UseField(const AFieldName: String): Boolean; public class function Execute(AUpdSQL: TFDUpdateSQL; const ACaption: String): Boolean; end; var frmFDGUIxFormsUSEdit: TfrmFDGUIxFormsUSEdit; implementation {$R *.dfm} uses System.UITypes, Vcl.Dialogs, Data.DB, FireDAC.Stan.ResStrs, FireDAC.Stan.Util, FireDAC.DatS, FireDAC.Phys.Intf; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.FormCreate(Sender: TObject); var i: Integer; oSQL: TFDGUIxFormsMemo; begin for i := 0 to 5 do begin oSQL := TFDGUIxFormsMemo.Create(Self); oSQL.Parent := pcSQL.Pages[i]; oSQL.Left := 5; oSQL.Top := 5; oSQL.Width := oSQL.Parent.ClientWidth - 12; oSQL.Height := oSQL.Parent.ClientHeight - 12; oSQL.Anchors := [akLeft, akTop, akRight, akBottom]; oSQL.Align := alNone; oSQL.Visible := True; oSQL.OnExit := mmSQLExit; oSQL.OnKeyDown := mmSQLKeyDown; end; end; {------------------------------------------------ -------------------------- } function TfrmFDGUIxFormsUSEdit.GetSQL(AIndex: Integer): TFDGUIxFormsMemo; begin Result := pcSQL.Pages[AIndex].Controls[0] as TFDGUIxFormsMemo; end; {------------------------------------------------ -------------------------- } function TfrmFDGUIxFormsUSEdit.UseField(const AFieldName: String): Boolean; begin Result := (FDataSet = nil) or (FDataSet.FieldCount = 0) or (FDataSet.FindField(AFieldName) <> nil); end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.cbxTableNameDropDown(Sender: TObject); begin {$IFDEF MSWINDOWS} cbxTableName.Perform(CB_SETDROPPEDWIDTH, Width div 2, 0); {$ENDIF} if cbxTableName.Items.Count = 0 then try FConnection.GetTableNames('', '', '', cbxTableName.Items, [osMy]); except cbxTableName.DroppedDown := False; raise; end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.cbxTableNameChange(Sender: TObject); begin btnGenSQL.Enabled := (cbxTableName.Text <> ''); end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.cbxTableNameClick(Sender: TObject); begin if cbxTableName.Text <> '' then begin btnServerInfoClick(nil); btnDSDefaultsClick(nil); end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.cbxTableNameExit(Sender: TObject); begin end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.btnServerInfoClick(Sender: TObject); var oConnMeta: IFDPhysConnectionMetadata; oView: TFDDatSView; sName: String; eAttrs: TFDDataAttributes; i, j: Integer; begin FConnection.ConnectionIntf.CreateMetadata(oConnMeta); oView := oConnMeta.GetTableFields('', '', cbxTableName.Text, ''); try lbKeyFields.Items.Clear; lbUpdateFields.Items.Clear; lbRefetchFields.Items.Clear; for i := 0 to oView.Rows.Count - 1 do begin sName := oView.Rows[i].GetData('COLUMN_NAME'); if sName = '' then sName := '_' + IntToStr(lbKeyFields.Items.Count); lbKeyFields.Items.Add(sName); lbUpdateFields.Items.Add(sName); lbRefetchFields.Items.Add(sName); end; for i := 0 to oView.Rows.Count - 1 do begin sName := oView.Rows[i].GetData('COLUMN_NAME'); if UseField(sName) then begin j := oView.Rows[i].GetData('COLUMN_ATTRIBUTES'); eAttrs := TFDDataAttributes(Pointer(@J)^); if (sName <> '') and (eAttrs * [caCalculated, caInternal, caUnnamed] = []) then lbUpdateFields.Selected[i] := True; if eAttrs * [caAutoInc, caROWID, caDefault, caRowVersion, caCalculated, caVolatile] <> [] then lbRefetchFields.Selected[i] := True; end; end; finally FDFree(oView); end; oView := oConnMeta.GetTablePrimaryKeyFields('', '', cbxTableName.Text, ''); try for i := 0 to oView.Rows.Count - 1 do begin sName := oConnMeta.UnQuoteObjName(oView.Rows[i].GetData('COLUMN_NAME')); if UseField(sName) then begin j := lbKeyFields.Items.IndexOf(sName); if j <> -1 then lbKeyFields.Selected[j] := True; end; end; finally FDFree(oView); end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.btnDSDefaultsClick(Sender: TObject); var oConnMeta: IFDPhysConnectionMetadata; i, j: Integer; oFld: TField; sFldName: String; begin if FDataSet = nil then Exit; if (FConnection <> nil) and (FConnection.ConnectionIntf <> nil) then FConnection.ConnectionIntf.CreateMetadata(oConnMeta) else oConnMeta := nil; if FDataSet.FieldCount <> 0 then begin for i := 0 to lbKeyFields.Items.Count - 1 do lbKeyFields.Selected[i] := False; for i := 0 to lbUpdateFields.Items.Count - 1 do lbUpdateFields.Selected[i] := False; for i := 0 to lbRefetchFields.Items.Count - 1 do lbRefetchFields.Selected[i] := False; for i := 0 to FDataSet.FieldCount - 1 do begin oFld := FDataSet.Fields[i]; if oFld.Origin = '' then sFldName := oFld.FieldName else sFldName := oFld.Origin; if oConnMeta <> nil then sFldName := oConnMeta.UnQuoteObjName(sFldName); j := lbKeyFields.Items.IndexOf(sFldName); if j <> -1 then begin lbKeyFields.Selected[j] := pfInKey in oFld.ProviderFlags; lbUpdateFields.Selected[j] := pfInUpdate in oFld.ProviderFlags; lbRefetchFields.Selected[j] := (oFld.AutoGenerateValue <> TAutoRefreshFlag.arNone); end; end; end; if FDataSet.Adapter <> nil then begin FOpts.UpdateOptions.RestoreDefaults; frmUpdateOptions.LoadFrom(FOpts.UpdateOptions); end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.btnGenSQLClick(Sender: TObject); begin frmUpdateOptions.SaveTo(FOpts.UpdateOptions); GenCommands; UpdateExistSQLs; pcMain.ActivePage := tsSQL; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.mmSQLExit(Sender: TObject); begin UpdateExistSQLs; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.mmSQLKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState); begin if (Shift = [ssCtrl]) and ((Key = Ord('A')) or (Key = Ord('a'))) then begin TFDGUIxFormsMemo(Sender).SelectAll; Key := 0; end else if Key = VK_ESCAPE then begin ModalResult := mrCancel; Key := 0; end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.UpdateExistSQLs; var i: Integer; s: String; begin for i := 0 to 5 do begin s := pcSQL.Pages[i].Caption; if GetSQL(i).Lines.Count > 0 then begin if Pos('*', s) = 0 then s := s + ' *'; end else begin if Pos('*', s) <> 0 then s := Copy(s, 1, Pos('*', s) - 1); end; pcSQL.Pages[i].Caption := s; end; end; {------------------------------------------------ -------------------------- } procedure TfrmFDGUIxFormsUSEdit.GenCommands; var i, j: Integer; oCmdGen: IFDPhysCommandGenerator; oTab: TFDDatSTable; oCol: TFDDatSColumn; oFld: TField; sFldName: String; oCmd: IFDPhysCommand; oOpts: IFDStanOptions; begin oTab := TFDDatSTable.Create; FConnection.ConnectionIntf.CreateCommand(oCmd); try oOpts := oCmd.Options; oOpts.UpdateOptions.Assign(FOpts.UpdateOptions); oOpts.FetchOptions.RowsetSize := 0; oOpts.FetchOptions.Mode := fmManual; oOpts.FetchOptions.Items := oOpts.FetchOptions.Items + [fiMeta]; // define table oCmd.Prepare('select * from ' + cbxTableName.Text); oCmd.Define(oTab); oTab.SourceName := cbxTableName.Text; // Include into Where only fields existing in dataset and // having pfInWhere in ProviderFlags if FDataSet <> nil then for i := 0 to oTab.Columns.Count - 1 do begin oCol := oTab.Columns[i]; if coInWhere in oCol.Options then begin oCol.Options := oCol.Options - [coInWhere]; for j := 0 to FDataSet.FieldCount - 1 do begin oFld := FDataSet.Fields[j]; if oFld.Origin = '' then sFldName := oFld.FieldName else sFldName := oFld.Origin; if (AnsiCompareText(oCol.Name, sFldName) = 0) and (pfInWhere in oFld.ProviderFlags) then oCol.Options := oCol.Options + [coInWhere]; end; end; end; // Include into where selected Key fields for i := 0 to lbKeyFields.Items.Count - 1 do begin oCol := oTab.Columns.ColumnByName(lbKeyFields.Items[i]); if lbKeyFields.Selected[i] then oCol.Options := oCol.Options + [coInKey, coInWhere] else oCol.Options := oCol.Options - [coInKey, coInWhere]; end; // Include into update selected Updating fields for i := 0 to lbUpdateFields.Items.Count - 1 do begin oCol := oTab.Columns.ColumnByName(lbUpdateFields.Items[i]); if lbUpdateFields.Selected[i] then oCol.Options := oCol.Options + [coInUpdate, coInWhere] else oCol.Options := oCol.Options - [coInUpdate, coInWhere]; end; // Include into refetch selected Refreshing fields for i := 0 to lbRefetchFields.Items.Count - 1 do begin oCol := oTab.Columns.ColumnByName(lbRefetchFields.Items[i]); if lbRefetchFields.Selected[i] then oCol.Options := oCol.Options + [coAfterInsChanged, coAfterUpdChanged] else oCol.Options := oCol.Options - [coAfterInsChanged, coAfterUpdChanged]; end; // Setup SQL generator FConnection.ConnectionIntf.CreateCommandGenerator(oCmdGen, oCmd); oCmdGen.FillRowOptions := [foData, foBlobs, foDetails, foClear] + FDGetFillRowOptions(oOpts.FetchOptions); oCmdGen.GenOptions := [goBeautify]; if cbQuoteColName.Checked then oCmdGen.GenOptions := oCmdGen.GenOptions + [goForceQuoteCol] else oCmdGen.GenOptions := oCmdGen.GenOptions + [goForceNoQuoteCol]; if cbQuoteTabName.Checked then oCmdGen.GenOptions := oCmdGen.GenOptions + [goForceQuoteTab] else oCmdGen.GenOptions := oCmdGen.GenOptions + [goForceNoQuoteTab]; oCmdGen.Options := oOpts; oCmdGen.Table := oTab; //Generate commands if FOpts.UpdateOptions.EnableInsert then GetSQL(0).Lines.Text := oCmdGen.GenerateInsert; if FOpts.UpdateOptions.EnableUpdate then GetSQL(1).Lines.Text := oCmdGen.GenerateUpdate; if FOpts.UpdateOptions.EnableDelete then GetSQL(2).Lines.Text := oCmdGen.GenerateDelete; if FOpts.UpdateOptions.LockMode <> lmNone then begin GetSQL(3).Lines.Text := oCmdGen.GenerateLock; GetSQL(4).Lines.Text := oCmdGen.GenerateUnLock; end; GetSQL(5).Lines.Text := oCmdGen.GenerateSelect(False); finally FDFree(oTab); oCmdGen := nil; oCmd := nil; end; end; {------------------------------------------------ -------------------------- } function TfrmFDGUIxFormsUSEdit.ExecuteBase(AUpdSQL: TFDUpdateSQL; const ACaption: String): Boolean; var oTestCmd: TFDCustomCommand; i: Integer; function GetConnection: TFDCustomConnection; begin if FUpdateSQL.ConnectionName <> '' then Result := FDManager.AcquireConnection(FUpdateSQL.ConnectionName, FUpdateSQL.Name) else begin if FUpdateSQL.Connection <> nil then Result := FUpdateSQL.Connection else if (FDataSet <> nil) and (FDataSet.PointedConnection <> nil) then Result := FDataSet.PointedConnection else Result := oTestCmd.GetConnection(False); if Result = nil then raise Exception.Create(S_FD_USEditCantEdit); Result := FDManager.AcquireConnection(Result, FUpdateSQL.Name); end; end; function GetParentObject: TPersistent; begin if FDataSet <> nil then Result := FDataSet else Result := oTestCmd; end; function GetUpdateOptions: TFDBottomUpdateOptions; begin if FDataSet <> nil then Result := FDataSet.OptionsIntf.UpdateOptions as TFDBottomUpdateOptions else Result := oTestCmd.UpdateOptions; end; begin LoadState; FUpdateSQL := AUpdSQL; FDataSet := FUpdateSQL.DataSet; oTestCmd := FUpdateSQL.Commands[arInsert]; FConnection := GetConnection; try FConnection.CheckActive; if (FDataSet <> nil) and not FDataSet.Active and (FDataSet.Command <> nil) and (Trim(FDataSet.Command.CommandText.Text) <> '') and (MessageDlg(S_FD_USEditOpenDS, mtConfirmation, [mbYes, mbNo], -1) = mrYes) then FDataSet.Open; FOpts := TFDOptionsContainer.Create(GetParentObject, TFDFetchOptions, TFDUpdateOptions, TFDTopResourceOptions, nil); FOpts.ParentOptions := GetUpdateOptions.Container as IFDStanOptions; FOpts.UpdateOptions.Assign(GetUpdateOptions); Caption := Format(S_FD_USEditCaption, [ACaption]); btnDSDefaults.Enabled := (FDataSet <> nil); btnGenSQL.Enabled := False; pcMain.ActivePage := tsGenerate; ActiveControl := cbxTableName; frmUpdateOptions.LoadFrom(FOpts.UpdateOptions); frmUpdateOptions.SQLGenerator := True; cbxTableName.Text := GetUpdateOptions.UpdateTableName; if (cbxTableName.Text = '') and (FDataSet <> nil) and (FDataSet.Table <> nil) then cbxTableName.Text := FDataSet.Table.ActualOriginName; if cbxTableName.Text <> '' then begin cbxTableNameChange(cbxTableName); cbxTableNameClick(cbxTableName); end; if btnDSDefaults.Enabled then btnDSDefaultsClick(nil); for i := 0 to 5 do GetSQL(i).RDBMSKind := FConnection.RDBMSKind; GetSQL(0).Lines.SetStrings(AUpdSQL.InsertSQL); GetSQL(1).Lines.SetStrings(AUpdSQL.ModifySQL); GetSQL(2).Lines.SetStrings(AUpdSQL.DeleteSQL); GetSQL(3).Lines.SetStrings(AUpdSQL.LockSQL); GetSQL(4).Lines.SetStrings(AUpdSQL.UnlockSQL); GetSQL(5).Lines.SetStrings(AUpdSQL.FetchRowSQL); UpdateExistSQLs; Result := (ShowModal = mrOK); finally FDManager.ReleaseConnection(FConnection); end; if Result then begin AUpdSQL.InsertSQL.SetStrings(GetSQL(0).Lines); AUpdSQL.ModifySQL.SetStrings(GetSQL(1).Lines); AUpdSQL.DeleteSQL.SetStrings(GetSQL(2).Lines); AUpdSQL.LockSQL.SetStrings(GetSQL(3).Lines); AUpdSQL.UnlockSQL.SetStrings(GetSQL(4).Lines); AUpdSQL.FetchRowSQL.SetStrings(GetSQL(5).Lines); end; SaveState; end; {------------------------------------------------ -------------------------- } class function TfrmFDGUIxFormsUSEdit.Execute(AUpdSQL: TFDUpdateSQL; const ACaption: String): Boolean; var oFrm: TfrmFDGUIxFormsUSEdit; begin oFrm := TfrmFDGUIxFormsUSEdit.Create(nil); try Result := oFrm.ExecuteBase(AUpdSQL, ACaption); finally FDFree(oFrm); end; end; end.