Web Core / XData Service Query Example

Being new to Web Core and XData, I've been very much impressed at how well everything works. Mixing JS and Delphi code in particular has been endlessly entertaining and super useful for tweaking UI web elements. Setting up new XData services is also amazingly simple and straighforward. Overall it has been as more fun developing in this sandbox than any I can recall! All good. It isn't perfect however, so this post is about outlining how to get a Query from the XData server into Web Core in a useful way, cobbled together from many of the posts here and elsewhere. I take no credit for any of the code, just my copy & paste talents :slight_smile:

Now, upfront I'm sure it is obvious that this is the kind of thing that Aurelius was designed for. Which is great, but there are a few things that make it not feasible to use in my case. Maybe an edge case, I don't know, but regardless the point of this post is to show how I've been able to cobble together something using a service endpoint instead of an entity model. Is this a good idea? I don't know, just what I've been able to cobble together into something that works for me. Curious to get feedback about how to do this more efficiently, better, or with other tools and approaches.

So to start with, I want to have an XData service that returns data that, in this example, is used to populate a WebTreeview that serves as a MainMenu for my web app. The tree just has two levels, so hopefully nothing to get tripped up on, and the query returns a rather inefficient representation that is used to create the treeview at the very end. While there may be efficiences to be found throughout, the main purpose of this topic is to cover the communication aspects, not the actual treeview aspects.

On the XDataServer side, I start with the service definition. In this case, the only parameter is optional, defaulting to JSON, with the actual query parameters included in a JWT.

    ///  <summary>
    ///    Retrieves the main menu from the Client Database.
    ///  </summary>
    ///  <param name="StreamFormat">
    ///    Determines how the data in the returned stream is formatted:
    ///    - JSON
    ///    - FireDAC
    ///    - XML
    ///    - Binary
    ///    - CSV
    ///  </param>
    ///  <remarks>
    ///    The Username/Database from the JWT is used to retrieve the menu
    ///  </remarks>

    [Authorize] [HttpGet] function GetMainMenu([XDefault('JSON')] StreamFormat: String = 'JSON'): TStream;

The service implementation is then the following. Note that the contents of the query are abstracted away for the most part - I write the query without having to define fields or anything like that in the service side of this process. I've left out details about the JWT and the query as they're not particularly relevant to what I'm after here.

function TMainMenuService.GetMainMenu(StreamFormat: String = 'JSON'): TStream;
var
  clientDB: TFDConnection;
  qry: TFDQuery;
 
  // used for JSON export
  bm: TFDBatchMove;
  bw: TFDBatchMoveJSONWriter;
  br: TFDBatchMoveDataSetReader;

  // used for CSV export
  L: TStringList;
  S: String;
  I: Integer;

begin
  Result := TMemoryStream.Create;

  // Get Menu from Client Database
  clientDB := TFDConnection.Create(nil);
  qry := TFDQuery.Create(nil);
  qry.Connection := clientDB;
  // Some extra stuff omitted here about the connection and the query parameters,
  // loading the query from elsewhere, JWT handling, etc.  
  // Not important for this discussion other than that it is an SQL query that returns
  // an arbitrary dataset with lots of columns and rows.  And some try/except blocks.
  clientDB.Connected := True;
  qry.Open;

  // Return the query results in whatever format was requested
  if (StreamFormat = 'FireDAC') then
  begin
    qry.SaveToStream(Result, sfJSON);
  end
  else if (StreamFormat = 'XML') then
  begin
    CoInitialize(nil);
    try
      qry.SaveToStream(Result, sfXML);
    finally
      CoUninitialize;
    end;
  end
  else if (StreamFormat = 'Binary') then
  begin
    qry.SaveToStream(Result, sfBinary);
  end
  else if (StreamFormat = 'CSV') then
  begin
    L := TStringList.Create;
    S := '';
    for I := 0 to qry.FieldCount - 1 do
    begin
      if (S > '') then S := S + ',';
      S := S + '"' + qry.FieldDefs.Items[I].Name + '"';
    end;
    L.Add(S);
    try
      qry.First;
      while not qry.Eof do
      begin
        S := '';
        for I := 0 to qry.FieldCount - 1 do
        begin
          if (S > '') then S := S + ',';
          S := S + '"' + qry.Fields[I].AsString + '"';
        end;
        L.Add(S);
        qry.Next;
      end;
    finally
      L.SaveToStream(Result);
      L.Free;
    end;
  end
  else // if (StreamFormat = 'JSON') then
  begin
    bm := TFDBatchMove.Create(nil);
    bw := TFDBatchMoveJSONWriter.Create(nil);
    br := TFDBatchMoveDataSetReader.Create(nil);
    try
      br.Dataset := qry;
      bw.Stream := Result;
      bm.Reader := br;
      bm.Writer := bw;
      bm.Execute;
    finally
      br.Free;
      bw.Free;
      bm.Free;
    end;
  end;

  qry.Close;
  qry.Free;
  clientDB.Connected := False;
  clientDB.Free;
end;

So when I use swagger to test this out against a test database, I get the following results (after logging in and getting a valid JWT to pass to the service).

StreamFormat = JSON:

StreamFormat = FireDAC:

StreamFormat = XML

StreamFormat = Binary

StreamFormat = CSV

This all works pretty well, no issues come to mind. I don't know what use-case there is for Binary but its part of FireDAC and trivial to implement. For XML, I had to add "ActiveX" to the uses clause. The contents of the query here are not really important but this is generic enough that the query itself doesn't really enter into the code anywhere beyond running the query, so I imagine this will work for any queries that don't have blob fields. And maybe those too so long as you don't select CSV :slight_smile:

So now over to Web Core where we want to retrieve this information and display it in a tree view. Now this can all be done more efficiently strictly with JSON but as most often this kind of data will need to be displayed in a grid, I'm trying to avoid dealing with JSON beyond decoding the stream.

So the first part of the function just gets the data from the XDataServer. In this case, we're getting the FireDAC version of the stream as it contains the field names and formats which we'd really like to use later on. Then we take that JSON raw data and convert it into something that can be put into a dataset. And then process the dataset to build the treeview.

// an XDataWebDataset called 'dtMenu' has been dropped onto the form with
// fields defined that we expect the incoming data to match up with

procedure TfrmMenu.RefreshMainMenu;
var
  XServer: TXDataWebConnection;
  Client:   TXDataWebClient;
  Response: TXDataClientResponse;

  JFDBS: TJSObject;
  JManager: TJSObject;
  JRowList: TJSArray;
  JTableList: TJSObject;

  MenuGroup: TTreeNode;
  MenuGroupName: String;

begin
  XServer := TXDataWebConnection.Create(nil);
  Client := TXDataWebClient.Create(nil);
  XServer.URL := // URL for XDATA Server
  XServer.OnRequest := XDataWEbConnection1Request;  // see below
  await(XServer.OpenAsync);
  Client.Connection := XServer;

  try
    Response := await(Client.RawInvokeAsync('IMainMenuService.GetMainMenu', ['FireDAC']));
  except
    on Error: Exception do
    begin
      // do something 
    end;
  end;

  // alright, Response.Result has the data we're after, so lets decode it

  JFDBS      := TJSObject(TJSObject(TJSJson.Parse(string(Response.Result)))['FDBS']);
  JManager   := TJSObject(JFDBS['Manager']);
  JTableList := TJSObject(TJSArray(JManager['TableList'])[0]);
  JRowList   := TJSArray(JTableList['RowList']);

  dtMenu.Connection :=XServer; // We're just doing this so it works, not actually using this connection
  dtMenu.Close;  // really important non-obvious thing here
  dtMenu.SetJSONData(JRowList);
  dtMenu.Open;

  // So the fields have been added in advance using "Original.XYZ" as the field names to
  // correspond to what is in the JSON data.  In a perfect world, we'd be able to 
  // extract that information from the JSON as well and create it via FieldDefs so that
  // no design-time elements need to be dropped on the form and so that it would
  // automatically work for any query.  But the fielddefs don't work. And I don't think
  // they planned on devs using XDataWebDataset in a disconnected way like this.

  dtMenu.First;
  MenuGroupName := '';
  MainMenuIndex.Text := '';
  treeMainMenu.BeginUpdate;
  treeMainMenu.Items.Clear;
  while not(dtMenu.EOF) do
  begin
    if (dtMenu.FieldByName('Original.GRP').AsString <> MenuGroupName) then
    begin
      MenuGroupName := dtMenu.FieldByName('Original.GRP').AsString;
      MenuGroup := treeMainMenu.Items.Add(MenuGroupName);
    end;
    treeMainMenu.Items.AddChild(MenuGroup,dtMenu.FieldByName('Original.NAM').AsString);
      dtMenu.Next;
  end;
  treeMainMenu.EndUpdate;

  Client.Free;
  XServer.Free;
end;

procedure TfrmMenu.XDataWebConnection1Request(
  Args: TXDataWebConnectionRequest);
begin
  Args.Request.Headers.SetValue('Authorization', 'JWT from elsewhere');
end;

So this all works, and I get a menu populated quickly enough. If instead of using it for a treeview, I could connect the XDataWebDataset (dtMenu in my example) to a WebDBGrid and a WebDataSource and see the data just as easily.

What I'd like to do though is to be able to have the ability to create the FieldDefs at runtime so that I can also create the XDataWebDataset at runtime and then dispose of it when done. Ideally, the FieldDefs could be created directly from the JSON data with a function call like dtMenu.SetJSONFields(JTableList); This isn't the biggest obstacle though as it is workable now and most of the time the XDataWebDataset will be defined at design time to display data in grids or other controls that make better use of design time tools. If parts of FireDAC were available, I'd be able to create a TFDmemTable and just do the inverse function to load the JSON data, much as I would do if this were a VCL app. Or if TDataset were available with a similar SetJSONData function.

In this example, I also realize that I could build the menu without using any of the dataset stuff just by processing the JRowList array. However, most of the time this mechanism is used to display data in a grid, so the DataSet would be required anyway. Might as well do the work there.

Sorry for the long example, but hopefully it is useful to see what I'm doing. Looking for feedback on how to address the runtime FieldDefs or whether I'm completely out to lunch and should be doing something else here entirely :crazy_face:

3 Likes

Maybe this topic will help you for runtime FieldDefs at least for JSON. Fields, definition, json package

Hi @AndrewSimard,

To start with, awesome words! Thank you for your compliments, I hope we can quote this and spread the work about Web Core and XData! :wink:

Regarding your post, also thank you for the detailed information. I'm sure this will be useful for many users searching the Support Center and finding such information you provided. A good summary.

Finally, about your specific question: I see you also posted a similar question here. It should actually be pretty simple, so I'm falling to see what are you struggling with. Maybe the whole point is that you trying to deal with FieldDefs, while what you should be doing is just to create persistent fields (the ones that go eventually in Fields).

Have you tried to just dynamically create the dataset fields, as it's usually done in Delphi? Like:

var
  T: TStringField;
begin
  T := TStringField.Create(DataSet1);
  T.FieldName := 'LastName';
  T.DataSet := DataSet1;
end;

Aha. I thought I was creating fields using the FieldDefs but apparently not the same thing. So I've gotten rid of the design-time aspects and used this instead:

var
  // other stuff as before
  dtMenu: TXDataWebDataSet;
  dtMenu_GRP: TStringField;
  dtMenu_NAM: TStringField;
  dtMenu_REF: TIntegerField;
  dtMenu_ICO: TIntegerField;
  dtMenu_SRT: TIntegerField;
  dtMenu_DSC: TStringField;
begin
  // other stuff as before up to dtMenu.Connection which is now replaced with
  dtMenu := TXDataWebDataset.Create(nil);

  dtMenu_GRP := TStringField.Create(dtMenu);
  dtMenu_GRP.FieldName := 'Original.GRP';
  dtMenu_GRP.Size := 30;
  dtMenu_GRP.Dataset := dtMenu;

  dtMenu_NAM := TStringField.Create(dtMenu);
  dtMenu_NAM.FieldName := 'Original.NAM';
  dtMenu_NAM.Size := 30;
  dtMenu_NAM.Dataset := dtMenu;

  dtMenu_REF := TIntegerField.Create(dtMenu);
  dtMenu_REF.FieldName := 'Original.REF';
  dtMenu_REF.Dataset := dtMenu;

  dtMenu_ICO := TIntegerField.Create(dtMenu);
  dtMenu_ICO.FieldName := 'Original.ICO';
  dtMenu_ICO.Dataset := dtMenu;

  dtMenu_SRT := TIntegerField.Create(dtMenu);
  dtMenu_SRT.FieldName := 'Original.SRT';
  dtMenu_SRT.Dataset := dtMenu;

  dtMenu_DSC := TStringField.Create(dtMenu);
  dtMenu_DSC.FieldName := 'Original.DSC';
  dtMenu_DSC.Size := 250;
  dtMenu_DSC.Dataset := dtMenu;

  dtMenu.SetJSONData(JRowList);
  dtMenu.Open;

  LogThis(' -- Building Main Menu');

  dtMenu.First;
   // other stuff as before, with this added at the end

  dtMenu.Close;
  dtMenu_GRP.Free;
  dtMenu_NAM.Free;
  dtMenu_REF.Free;
  dtMenu_SRT.Free;
  dtMenu_ICO.Free;
  dtMenu_DSC.Free;
  dtMenu.Free;
end;

So this works pretty well! No more trouble with the connection aspects of XDataWebDataset, and I get my runtime-only instantiation without another design-time element hanging around unnecessarily.

Any suggestions on how to have the fields created using the contents of the JSON to pick out the names and datatypes/sizes? And I don't know how you'd declare the fields ahead of time (var) if you don't know what they are? Maybe Just an array of the different types and then assign them based on what you find in the JSON?

I did look at the thread suggested by Igor but didn't make the connection as far as how it could help here. I think the JSON coming from FireDAC has all the information I need so it is sitting there. I guess it is just a matter of unpacking it and iterating through the values to get what I need. Seems it would be something that lots of people have already done?

Reason for wanting this is so that the same block of code can be used more generically for many queries that might be retrieved. Once the data is there it can be passed over to whatever is using the data and can be more specific about fields, but up until that point, keeping things as generic as possible field-wise I think would work best.

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.

You will have to manually iterate through the JavaScript objects created from JSON and create the fields from there, there isn't something automatic as far as I know.

Implemented in the update to this thread: Web Core / XData Service Query Example v2

1 Like

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.