Jump to content

TClientDataset with TFDStoredProc? is that possible? foolish?

Recommended Posts

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.




Share this post

Link to post

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.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
        result := cdsFD.Data;

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?

Share this post

Link to post

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
      i : integer;
      s, s2, s3 : string;
      if cdsFD.Active then cdsFD.Close;
      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
          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];


      cdsFD.Active := true;


      while not spFD.EOF do
          cdsFD.copyFields (spFD);



// start of main procedure

  GetFDConnection;  // create new TFDConnection named DBCFD

  spFD.Connection := DBCFD;


  spFD.StoredProcName := 'JCMGetAllTimeLookupLists';
  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



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



Edited by KMarb

Share this post

Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now