Web Core / XData Service Query Example v3

The previous version of this topic was about getting a TStream from an XData service and loading it into an XDataWebDataSet for subsequent use later, with populating a TWeBTreeView used as an example. This has been working really well. However, it seems with a few more tweaks it could be made immensely more useful. Along the way I hit a few snags that took awhile to figure out (with lots of searches in the Support Center - and not always successful).

Firstl, I wanted to be able to load up the data from the XDataWebDataSet into an actual (read-only) grid for display. In this case, it is an FNC Grid. I struggled quite a bit with getting this to work. What ended up working is the following.

  • Drop an FNC Grid on a TWebForm.
  • Drop an FNCGridDatabaseAdapter on that.
  • Connect them via the Adapter property of the Grid and the Grid property of the Adapter.
  • Define the expected fields for the grid in both the Adapter and the Grid.
  • Adjust Grid as far as column widths and so on.
  • Add buttons for First/Prev/Next/Last to serve as navigator

For whatever reason, connecting an FNC Grid via the adapter to the XDataWebDataSet works for display, but the sorting doesn't do anything. The trick it seems is to use the LoadAllDataAndDisconnect method to, well, load all the data into the grid and disconnect from the Dataset. This fixes the soriting. But then the connection to the navigator is lost, as that is paired with the now disconnected dataset, so that had to be created separately. Here's what I neded up with. A grid with two columns (an index (integer) and a description (string), with the string stretched to fit the width, and a header and footer row).

procedure RefreshGrid;
var
  xd: TXDataWebDataSet;
  ds: TWebDataSource;
  rowcount :Integer;
begin
  // grid is the grid on the form
  // da is the databaseadapter on the form
  grid.Options.ColumnSize.Stretch := False;
  grid.Options.ColumnSize.StretchColumn := -1;

  xd := TXDataWebDataset.Create(nil);
  ds := TWebDataSource.Create(nil);

  ds.Dataset := xdParameterList;
  da.Datasource := dsParameterList;

 rowcount  := await(GetQueryData('ISomeService.GetData', '', xdParameterList));

  if (rowcount = -1) then
  begin
     // grid is empty
     exit;
  end;

  da.Active := True;
  da.LoadAllDataAndDisconnect;

  grid.RowCount := gridParameterList.RowCount+1;
  grid.Options.ColumnSize.Stretch := True;
  grid.Options.ColumnSize.StretchColumn := 1;

  grid.ColumnCalculation[0] := ccCOUNT;
  grid.Cells[1,grid.RowCount-1] := grid.Cells[0,grid.RowCount-1]+' Record(s) Found';
  grid.UpdateCalculations;
  grid.ListSelectedCell(Sender,0,0);
  grid.EndUpdate;

  dst.Free;
  xd.Free;
end;

procedure btnFirstClick(Sender: TObject);
begin
  grid.FocusedCell := MakeCell(0,0);
  gridParameterListSelectedCell(Sender,0,0);
end;

procedure btnPrevClick(Sender: TObject);
begin
  grid.FocusedCell := MakeCell(0,Max(gridParameterList.FocusedCell.Row-1,0));
  gridSelectedCell(Sender,0,0);
end;

procedure btnLastClick(Sender: TObject);
begin
  grid.FocusedCell := MakeCell(0,gridParameterList.RowCount-1);
  gridSelectedCell(Sender,0,0);
end;

procedure btnNextClick(Sender: TObject);
begin
  grid.FocusedCell :=  MakeCell(0,Min(grid.FocusedCell.Row+1,grid.RowCount -1));
  gridSelectedCell(Sender,0,0);
end;

procedure gridSelectedCell(Sender: TObject; ACol, ARow: Integer);
begin
  // Deal with First/Prev buttons 
  if (grid.FocusedCell.Row <= 1) then
  begin
    btnFirst.Enabled := False;
    btnPrev.Enabled  := False;
  end
  else if (grid.RowCount > 3) then
  begin
    btnFirst.Enabled := True;
    btnPrev.Enabled  := True;
  end;

  // Deal with Next/Last buttons
  if (grid.FocusedCell.Row > (grid.RowCount - 3)) then
  begin
    btnNext.Enabled := False;
    btnLast.Enabled := False;
  end
  else if (grid.RowCount > 3) then
  begin
    btnNext.Enabled := True;
    btnLast.Enabled := True;
  end;

Some additional notes. The stretching aspect seems to get cranky when the data in the grid is cleared. Not sure why it needs to be so vocal about it :slight_smile: Not unhappy about creating my own navigator as I have more control over its appearance, but a tad finicky about dealing with the header and footer rows being included in the counts (again, rookie FNC Grid user here) and wanting the right buttons enabled/disabled in the scenarios of having 0 or 1 records. Overall though this works pretty well. Interested to know if I overlooked anything obvious.

So now with the grid loaded up and functioning, my attention turned to what my users might want to do with the data. The first is the ability to export it into some local file/format. The most common being CSV, PDF and Excel. So I tinkered with what options are available within the FNC Grid itself and found that while it has some of this, the Web version doesn't (yet?) support Excel. And then I thought about the service call that got the grid data in the first place and, well, the server isn't running the Web version of FNC Grid so maybe just get the data from there. And then the thought was, well, why not just get the exported data from the service in whatever format is needed, no need to do it in the client at all, right?

So flipping over to the XDataServer code (back in the first version of this post), I've updated the documentation for the service definition's StreamFormat with some more options.

    ///  <param name="StreamFormat">
    ///    Determines how the data in the returned stream is formatted:
    ///    - CSV
    ///    - XML
    ///    - JSON
    ///    - FireDAC
    ///    - PDF (Base64)
    ///    - Excel (Base64)
    ///    - Binary (Base64)
    ///  </param>

For the non-text streams, I've changed them to Base64. Check out (far) below for a fun benefit that comes from this but it also makes it easier to test in Swagger as I can then copy the text to an online service like https://www.freeformatter.com/base64-encoder.html which can then decode the Base64 and present it as a file directly. Might be an option to add this to Swagger as well. No clue if it is possible to change that by me as a developer? Another adventure for another day. There are other tradeoffs that come with Base64. 25% larger when sending uncompressed, but apparently within 5% if compressed (via XDataServer automatically, potentially) with the added overhead of the server doing all this encoding and compression.

So to implement these StreamFormats, the idea is to just get a PDF or an Excel stream from somewhere, convert it to Base64 and send it back to the client. And there are many different ways to do that.

I first just tried creating an FNC Grid dynamically in the service using the query as the datasource much like is done in the client, but FNC Grid seems rather displeased to not have a window/form avaiable to draw itself in. I tried for awhile to work around that but didn't end up having much luck. A limitation of my skills, not the product I'm sure! So I instead went a different way and created a report using the Digital Metaphors ReportBuilder product. Which is not free. But neither is FNC Grid. It has issues too of course. But dropping a report on a datamodule seemed to work, and creating an instance of that datamodule at runtime solved the problem. I never went back and tried to drop an FNC Grid on the datamodule. That might very well have worked as well? In any event, I've managed to make the PDF and Excel export functions work using ReportBuilder, and in a way that should make it easy enough to replace with any other source of PDF or Excel data, even just loading them up from a file created entirely elsewhere.

// same function call as before with a few extra vars
var
...
  dm: TSomeDataModule;
  pd: TppPDFDevice;        // Specific to ReportBuilder
  fn: String;              // Temporary Filename
  ms: TMemoryStream;
...
begin
// rest of function is same, getting a TFDQuery populated
...
  else if (Uppercase(StreamFormat) = 'PDF') then
  begin
    dm := TSomeDataModule.Create(nil);
    pd := TppPDFDevice.Create(nil);
    ms := TMemoryStream.Create;

    // The DataModule as a ppReport, a DataSource and a ppDBPipeline already connected
    // This is just how ReportBuilder works, and is the equivalent of having 
    // FNCGrid, DataSource, FNCGridDatabaseAdapater and PDFIO components
    dm.DataSource1.DataSet := qry;
    pd.OutputStream := ms;
    pd.PDFSettings := dm.ppReport1.PDFSettings;
    pd.Publisher := dm.ppReport1.Publisher;
    dm.ppReport1.PrintToDevices;

    try
      ms.Position := 0;
      TNetEncoding.Base64.Encode(ms, Result);
    finally
      ms.Free;
      pd.Free;
      dm.Free;
    end;
  end

  else if (Uppercase(StreamFormat) = 'EXCEL') then
  begin
    dm := TDMConfigMgr.Create(nil);
    ms := TMemoryStream.Create;
    fn := TPath.GetTempFileName;

    dm.DataSource1.DataSet := qry;
    dm.ppReport1.DeviceType := dtXLSData;
    dm.ppReport1.TextFileName := fn;
    dm.ppReport1.ShowPrintDialog := False;
    dm.ppReport1.ShowCancelDialog := False;
    dm.ppReport1.XLSSettings.MergeAdjacentCells := False;
    dm.ppReport1.XLSSettings.ScaleToPageWidth := True;
    dm.ppReport1.XLSSettings.ExportComponents := [ecText,ecImage,ecRichText,ecBarCode,ecOther];
    dm.ppReport1.Print;

    try
      ms.LoadFromFile(fn);
      ms.Position := 0;
      TNetEncoding.Base64.Encode(ms, Result);
    finally
      ms.Free;
      dm.Free;
      DeleteFile(fn);
    end;
  end

  else // if (Uppercase(StreamFormat) = 'JSON') then
...
// rest of the funciton is unchanged

Some additional notes. The configuration of the PDF formatting or the Excel formatting is largely done in the ppReport here, but the same could be done in another Grid (like FNC Grid or a cxGrid or whatever else might be available) and the result is just something in its respective format dumped into the ms TMemoryStream (or even loaded from a temp file as was doen for Excel) that is then converted to Base64 and returned as the TStream result. I noticed that while the FNC Grid's PDFIO component could write directly to a TStream, the ExcelIO equivalent would have to do this temp file two-step as well.

In this case, I'm just generating the PDF or Excel on the fly with the general understanding that this is a very quick process, just a few seconds at most. If this were a longer process, such as a complex report, it might be better to handle the request outside of this function call and just return a 'pending result' to the caller and then have another service to check on or retrieve the results of that. For now though, this works pretty well and there isn't much of a delay between the user clicking ont he button and the resulting PDF or Excel file being returned.

Ok, so back at the client, we've got an incoming PDF or Excel file in Base64. But not access to the TNetEncoding functions, so a few more challenges await. Also, it isn't clear to me how to handle the incoming TStream, so having it as a WideString makes it a bit easier conceptually at least.

Also, previously we were just concerned about getting an XDataWebDatasset populated with the FireDAC JSON coming from the service, and now we're looking for an actual file of some kind. So we've got a different function call to make.

function GetFileData(Endpoint: String; var FileData: WideString; FileFormat: String):Integer;
var
  Conn:     TXDataWebConnection;
  Client:   TXDataWebClient;
  Response: TXDataClientResponse;

begin

  // This indicates the request was unsuccessful
  Result := -1;

  // Setup connection to XData Server
  Conn              := TXDataWebConnection.Create(nil);
  Conn.URL          := // URL for XDATA Server
  Conn.OnRequest    := PopulateJWT;                      // Same as GetQueryData
  Client            := TXDataWebClient.Create(nil);
  Client.Connection := Conn;
  await(Conn.OpenAsync);

  // Make the Request
  try
    Response := await(Client.RawInvokeAsync(Endpoint, [FileFormat]));
  except
    on Error: Exception do
    begin
      // something happened
      Client.Free;
      Conn.Free;
      exit;
    end;
  end;

  // Binary Files - Convert Base64
  if ((FileFormat = 'PDF') or (FileFormat = 'Binary') or (FileFormat = 'Excel')) then
  begin
    // this is the Web Core Base64 decode equivalent
    FileData := window.atob(string(Response.Result));  
  end
  // Text files 
  else
  begin
    FileData := string(Response.Result);
  end;

  Result := Length(FileData);

  Client.Free;
  Conn.Free;
end;

So the result is a FileData WideString that has either the text data or the Base64-decoded file. I stuggled for quite a bit with the code samples in the Support Center that dealt with deoding JavaScript byte arrays, and issues with WideString being two-byte-per-char arrays and the rest of it, and at the end of it all I somehow eneded up back where I started with just WideString. It would seem at this point that my service call could just send a WideString instead of a TStream and it would work just as well.

So the next issue was about what to do with this file that has come back from the service call. A user clicks on a button and expects the file to appear, presumably via a download link, regardless of the format they chose. Not obvious how to go about this when the file is actually sitting in a TStream. It could in theory be written out to a file somewhere. Some posts suggest just sending a link to an HREF that is pointing at the XDataServer to get the file from there. Not sure I'd even want the user seeing anything at all to do with the XDataServer - they may not even have access to it. And they'd be likely to save that as a link somewhere which would then be invalid after some period of time and, and, and... so many issues.

But the most fun thing about Web Core is that you can drop in popular JS libraries with almost zero effort and be on your merry way in no time at all. In this case, the problem can be addressed using FileSaver.js which does exactly this - sends a file that is generated within the client to the user.

In my Project.html I can add a link to FileSaver.js which can be stored on my server or perhaps more efficiently served up by a CDN. For example:

<script src="https://cdn.jsdelivr.net/npm/file-saver@2.0.5/dist/FileSaver.min.js"></script>

Most of the time this kind of "add a JS library" works without issue but in this case there was an extra bit of code needed to make it work, shown below but documented here.

So now when the user clicks on a button (perhaps in my custom navigator) to generate a CSV or other text file, a call is made to get the file and then the JS is used to deliver it to the user.

procedure btnExportCSVClick(Sender: TObject);
var
  FileData: WideString;
  FileSize: Integer;
  FileName: String;
  JSError: String;
begin
  FileSize := await(GetFileData('ISomeService.GetSomeData', FileData, 'CSV'))

  if (FileSize = -1) then
  begin
    // got nothing for you 
    exit;
  end;

  // Ok, We've got a file
  JSError := '';
  try
    asm
      try {
        function SaveAsFile(t,f,m) {
          try {
            var b = new Blob([t],{type:m});
            saveAs(b, f);
          } catch (e) {
            window.open("data:"+m+"," + encodeURIComponent(t), '_blank','');
          }
        }
        SaveAsFile(FileData, FileName, "text/csv;charset=utf-8");
      }
      catch(e) {
        JSError = e.message;
        }
    end;
  except on E: Exception do
    begin
        // something happened
    end;
  end;
  if (JSError <> '') then // something else happened
end;

This works remarkably well for text formats. I don't know why the SaveAsFile function call needs to be defined in this way but it doesn't seem to work without it. I also tried putting the function call definition in a common JS library for my project but while that worked for desktop browsers it didn't work on iOS for some reason. Having it here seems to address it though. And while this does work on the iOS version that I have (prompting the user to save the file which ends up in the Files app), it may not work on all versions of iOS and might behave differently (ie poorly) for PDF files vs. other files. Test, test, test!

        SaveAsFile(FileData, FileName, "text/csv;charset=utf-8");
        SaveAsFile(FileData, FileName, "application/json;charset=utf-8");
        SaveAsFile(FileData, FileName, "application/xml;charset=utf-8");
        SaveAsFile(FileData, FileName, "application/pdf;charset=utf-8");
        SaveAsFile(FileData, FileName, "application/vnd.ms-excel;charset=utf-8");

For binary formats, the WideString returned needs to be converted into something else. Went through quite a few iterations before hitting upon the key functionality here - the TJSArrayBuffer and the BinaryStringtoAB function call that is in WEBLib.Crypto. Credit goes to this thread for this little nugget.

procedure btnExportExcelClick(Sender: TObject);
var
  FileData: WideString;
  FileSize: Integer;
  FileSend: TJSArrayBuffer;
  FileName: String;
begin
  FileSize := await(GetFileData('ISomeService.GetQueryData', FileData, 'EXCEL'))
  FileName := 'something.xls';

  if (FileSize = -1) then
  begin
    // got nothing for you 
    exit;
  end;

  // Ok, We've got a file
  JSError := '';
  try
    FileSend := BinaryStringToAB(FileData);
    asm
      try {
        function SaveAsFile(t,f,m) {
          try {
            var b = new Blob([t],{type:m});
            saveAs(b, f);
          } catch (e) {
            window.open("data:"+m+"," + encodeURIComponent(t), '_blank','');
          }
        }
        SaveAsFile(FileSend, FileName, "application/vnd.ms-excel;charset=utf-8");
      }
      catch(e) {
        JSError = e.message;
      }
    end;
  except on E: Exception do
    begin
      // something happened
    end;
  end;
  if (JSError <> '') then // something else happened
end;

This works just as well for PDFs. So if you can create a PDF for downloading, why not a PDF for printing? JS to the rescue again. In this case, it is PrintJS that comes to the rescue and it works fantastically well here. The same approach is used for getting the library installed.

<script src="https://cdn.jsdelivr.net/npm/print-js@1.6.0/dist/print.min.js"></script>

And then the function call works the same as for downloading a text file. In a bit of a sleight-of-hand here, the same function call is made but a lowercase 'pdf' is sent to the function so that the returned file is still encoded as Base64. This is what PrintJS is expecting in this particular arrangement but there are other options here as well. The XDataServer's service itself deliberately doesn't care about the case of the StreamFormat parameter. It is just the client function that decodes it. Could do the same thing with another parameter to the function call rather than relying on the case. But that'd be less fun. In any event, to print the results, this is the function.

procedure btnExportPrintClick(Sender: TObject);
var
  FileData: WideString;
  FileSize: Integer;
  FileName: String;
  ElapsedTime: TDateTime;
begin
   FileSize := await(GetFileData('ISomeService.GetQueryData', FileData, 'pdf'))

  if (FileSize = -1) then
  begin
    // got nothing for you 
    exit;
  end;

  // Ok, We've got a file
  if (FileSize > 0)  then
  begin
    asm
      printJS({printable: FileData, type: 'pdf', base64: true})
    end;
  end;

So on IOS, for example, clicking the button pops up a print preview screen showing the pdf along with a list of available printers. No muss, no fuss, works great, no issues I've run across.

With that out of the way, the only thing left to sort out with XData Service Query is dealing with editable datasets... implementing CRUD of some form. Seems this will need another service setup and a bit of work with the GridDatabaseAdapter to figure out what to send to it. Another post for another day, once I get it figured out. Maybe some posts before then depending on how well it goes!

3 Likes

Oh, and it is a good idea to wrap the asm...end block in the calls that include SaveAsFile in {$IFNDEF WIN32}...{$ENDIF} so that all the {} and [] and () in the JS don't fubar the Object Inspector when doubleclicking on an event for a Menu, for example :slight_smile:

Thank you for this detailed post and analysis. It makes it clear to us that there is still a lot of improvements that can be done in various areas. It's very helpful to know where our focus and attention needs to go to improve the FNC grid on WEB as well as the way the grid is connected to data. Thanks!

Awesome post, @AndrewSimard. Some comments I have about it:

  • SwaggerUI is a 3rd party JavaScript library, thus it's not modified by us. Of course you can suggest features in their own project: https://github.com/swagger-api/swagger-ui.
  • There are functions in Bcl.Utils unit that you can also use in Web Core, which has functions like PercentEncode and DecodeBase64.
  • You can always try to translate the asm code to Pascal. That's the idea of Web Core! Of course sometimes it's not possible or sometimes it's just handy to throw the asm code. But if you have issues with code completion and things like that, you can take the effort and do it.
  • For reporting and generating PDF and Excel server-side, there is also the TMS alternative, the awesome TMS Flexcel. It's thread-safe, really fast and very powerful.

No criticisms were intended by my post, just showing what hoops I had to jump through. As a new FNC Grid customer (in addition to Web Core and XData) it was challenging to figure out how to get it to work, but the good news is that it does work pretty well at the end of the day, which is what matters most!

3 Likes

Thanks for the bits of info. I will check out the swagger proejct, see what kinds of options are available for customizing things a bit more.

Is there a place where these other functions are documented, like the Bcl.Utils? My IDE (10.3) seems to have issues with code completion generally in WebCore that I've not yet been able to figure out so the usual process of discovery for this kind of thing isn't working out so well. I might have to post something about that issue separately.

Being able to include JS via asm blocks directly is I think my most favourite part of Web Core! Even when Delphi functions are available to do the same thing :slight_smile: It is just that in this particular bit of code for calling the FileSave.js functions their JS went a little crazy with the bracketing, crazy enough that the IDE couldn't quite sort it out for whatever reason. Wrapping it in {$IFNDEF WIN32}...{$ENDIF} allowed me to keep their JS as-is as well as keep the IDE happy so all good!

I'll check out FlexCel as well, thanks!

1 Like