How to return DataSet from DataSnap server?

Question

Hello everyone who reads this! I hope you can help me with my problem, but if not, thank you for try. I have DataSnap server and client. DataSnap server methods can return to client a DataSet as function result. I'm getting data from MySQL DB with TFDQuery component. Somebody please help me to understand, how can I get a dataset from FDQuery component that already has data?

TDataSet.Data is OleVariant type property containing all data. But FDQuery doesn't have the same property. I need to return a dataset from FDQuery as OleVariant in function.

*Try, Except, FreeAndNil, DisposeOf etc removed from code for better understanding the problem

//Client side
procedure TForm1.GetDataSetFromServer;
var
  Server: TServerMethods1Client;
  DS: TClientDataSet;
begin
  Server := TServerMethods1Client.Create(ClientModule1.SQLConnection1.DBXConnection);
  DS := TClientDataSet.Create(nil);   
  DS.Data := Server.GetDataSet; //Call remote server method
end;

//DataSnap server side
function TServerMethods1.GetDataSet: OleVariant;
begin
  FDQuery1.Close;
  FDQuery1.SQL.Text := 'SELECT * FROM Table1';
  FDQuery1.Open;
  //Now i need to return all data as function result
  result := ???
end;

Need any information that can be helpful. Thanks in advance! Have a nice day!

Answer 1

The simplest way to do this, AFAIK, is to add a TDataSetProvider, and also a TClientDataSet (if you don't already have one) to your Server module.

Then, you can modify your server code as follows:

function GetDataSet: OleVariant;
begin
  if ClientDataSet1.Active then
    ClientDataSet1.Close;

  FDQuery1.Close;
  FDQuery1.SQL.Text := 'SELECT * FROM Table1';
  //  FDQuery1.Open; Leave this to the DataSetProvider/ClientDataSet

  //Now i need to return all data as function result
  //result := ???

  DataSetProvider1.DataSet := FDQuery1;
  ClientDataSet1.ProviderName := 'DataSetProvider1';
  ClientDataSet1.Open;
  Result := ClientDataSet1.Data;
end;

The point of doing it this way is that TDataSetProvider has all the internal machinery necessary to package up its DataSet's data (i.e. FDQuery1's data) in a form that that can be sent between ClientDataSets. Incorporating the DataSetProvider in the server minimizes the code necessary in the client to consume the CDS data.

Btw, I'm assuming that your server module has the code necessary to 'export' GetDataSet as a server method.

Answer 2

You can also do return a TDataSet from the server function:

function TServerMethods1.GetDataSet: TDataSet;
begin
  if ClientDataSet1.Active then
    ClientDataSet1.Close;

  FDQuery1.Close;
  FDQuery1.SQL.Text := 'SELECT * FROM Table1';

  DataSetProvider1.DataSet := FDQuery1;
  ClientDataSet1.ProviderName := 'DataSetProvider1';
  ClientDataSet1.Open;
  Result := ClientDataSet1;
end;

For the client side, it now depends on what kind of server you are using.

If it is a DBX Datasnap Server, you will have to use a TsqlServerMethod (note the lower case) 'sql' here, and a TDataSetProvider with a TClientDataset, all preconfigured to retrieve the data from that Server.

If it is a REST Datasnap server, you can do this at the client:

procedure TfrmClientMain.btnRefreshClick(Sender: TObject);
var
  Server: TServerMethods1Client;
  lDataSet: TDataSet;
  DSP: TDataSetProvider;
begin
  Server := TServerMethods1Client.Create(ClientModule1.DSRestConnection1);
  try
    CDS.Close; // a TClientDataSet has been placed on the form
    lDataSet := Server.GetDataSet();
    DSP := TDataSetProvider.Create(Self);
    try
      DSP.DataSet := lDataSet;
      CDS.SetProvider(DSP);
      CDS.Open;
    finally
      CDS.SetProvider(nil);
      DSP.Free;
    end;
 finally
   Server.Free;
 end;
end;