TClientDataset with TFDStoredProc? is that possible? foolish?

[KMarb 5]

I've only ever used TClientDataset with TSQLQuery, with the needed TDatasetProvider in between (TSQLQuery -> TDatasetProvider -> TClientDataset).

 

I have a project to update an old datasnap server that used DBExpress (TSQLQuery comes from there). I'm wondering if I can drop DBExpress and use FireDac, which I know much better.

 

Does this component linking work or make sense: (TFDStoredProc -> TDatasetProvider -> TClientDataset)?

 

Or better, is there a way to pass an entire dataset using datasnap, but using FD datasets, not DBExpress datasets? With DBX, you use TClientDatasets (CDS) to pass an entire table of data with metadata with these lines:

 

transData := CDS.Data; // on the client side, then a server call passes the transData (array of OleVariants) to a server procedure. CDS is linked to a local SQLite table

 

CDS.Data := transData ; // on the server side, and now the TClientDataset has an entire table.

 

The big picture is to have an efficient way for a client (android datasnap client) to send tables of data to the server, and for the server to efficiently save that data as new rows in existing SQL Server tables. I mention that because I feel there is probably a good way to take CDS.Data (or its FD equivalent) and save all rows to a server table without writing much code, but I wouldn't know where to start my research to do that.

 

Discussion and feedback are really apprecaited.

 

Keith

 

[KMarb 5]

My server needs to send 19 datasets to the android client (datasnap). I nearly have things working using Firedac, but I cannot figure out how to make a Clientdataset use datasets in the TFDStoredProc other than the 1st dataset.

 

I have a SQL (Microsoft) stored procedure, 'JCMGetAllLookupLists' that returns 19 datasets. The TFDStoredProc (spFD) can step through those datasets easily enough using spFD.NextRecordset, but I don't know how to get the dataset in the CDS so I can send CDS.Data back to the cliente.

 

here is the gyst of my code. The code below is in a server function that returns an OleVariant

 

    Result := VarArrayCreate ([0, 19], varVariant);  // result [19] is not a dataset

 

    spFD.StoredProcName := 'JCMGetAllLookupLists';
    spFD.Prepare;
    spFD.paramByName ('@TSLastRequest').Value := lastRequestTimeStamp;
    spFD.paramByName ('@ReturnTS').Value := 'N';

    cdsFD.Open;  // cdsFD is linked to a datasetprovider (dspFD) using cdsFD.Providername, and dspFD.Dataset is set to spFD

 

    // at this point, cdsFD can access the first dataset in spFD

    // but, how to get to the other 18 datasets?

    result [0] := cdsFD.Data;  // this works fine

 

    // the loop below does not work... cdsFD.Data always has the data from spFD's FIRST dataset...

    for i := 1 to 18 do
      begin
        spFD.NextRecordSet;
        result := cdsFD.Data;
      end;
 

I could make 19 different calls to the SQL server to get the 19 datasets (that's what I'm doing now), but it seems much more efficient if the server returns all 19 datasets at once and then my server code pulls out the data for each to send back to the client.

 

Does anyone know how to step through FD datasets using NextRecordset in a way that a linked ClientDataset "sees" each of the FD datasets?

[KMarb 5]

I needed to get something working, so I wrote the code below. I want to use FireDac for database access, but I want to pass entire datasets to the datasnap client, and the only way I know how to do that now is to put the data in a TClientdataset and then send to client using CDS.Data.

 

If I could use DBExpress to access the server database it is FAR simpler to pass datasets.

 

If you feel like looking at code (working code, although not sure how efficient), please read below and tell me if there is a better way to do what I'm trying to do. Thank you.

 

cdsFD : TClientDataset; // created with data module create

 

    procedure linkCDS;  // sub-procedure
    var
      i : integer;
      s, s2, s3 : string;
    begin
      if cdsFD.Active then cdsFD.Close;
      cdsFD.FieldDefs.Clear;
      cdsFD.FieldDefs.Assign(spFD.FieldDefs); // spFD is the TFDStoredProc with data from server that needs to be sent to client

 

      // field definitions from the server might have auto-increment fields, readonly fields and required fields... Turn all those off in the CDS

      for i := 0 to cdsFD.FieldDefs.Count - 1 do
        begin
          if cdsFD.FieldDefs .DataType = ftAutoinc then
            cdsFD.FieldDefs .DataType := ftInteger;

 

          if DB.faReadonly in cdsFD.FieldDefs .Attributes then
            cdsFD.FieldDefs .Attributes := cdsFD.FieldDefs .Attributes - [DB.faReadonly];

 

          if DB.faRequired in cdsFD.FieldDefs .Attributes then
            cdsFD.FieldDefs .Attributes := cdsFD.FieldDefs .Attributes - [DB.faRequired];

        end;

      cdsFD.CreateDataSet; 
      cdsFD.Active := true;

 

      spFD.First;
      while not spFD.EOF do
        begin
          cdsFD.Insert;
          cdsFD.copyFields (spFD);
          cdsFD.Post;

          spFD.Next;
        end;
    end;

 

// start of main procedure

begin
  GetFDConnection;  // create new TFDConnection named DBCFD

  spFD.Connection := DBCFD;

 

  spFD.StoredProcName := 'JCMGetAllTimeLookupLists';
  spFD.Prepare;
  spFD.paramByName ('@TSLastRequest').Value := lastRequestTimeStamp;
  spFD.paramByName ('@ReturnTS').Value := 'N';

  spFD.open;  // retrieves 19 datasets

 

  Result := VarArrayCreate ([0, 19], varVariant);

 

  linkCDS;  // see above... put FDStoredProc data into a CDS

 

  result [0] := cdsFD.Data;

  for i := 1 to 18 do
    begin
      spFD.NextRecordSet;

 

      linkCDS;

      result  := cdsFD.Data;  // weird... I keep trying to make this result [ i ] :=, but when I hit save (on delphipraxis), the [ i ] keeps disappearing
    end;

 

  spFD.Close;
end;

Edited November 23, 2022 by KMarb