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
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
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