Loading Batch Move XData Stream Query to XDataWebDataSet - Web Core App

Padureanu_Dor_Bujor · March 2, 2022, 4:01pm

We have an XData server that opens a Query returning a Stream using BatchMove method depicted in tutorial: TMS XData: Accessing any database with SQL queries via the TMS XData REST server - YouTube
So the returned JSON is very simple, by records, in order to be easy to load on a basic web app.

To load the data we use the following code:

procedure TFLicenses.btnRefreshClick(Sender: TObject);
var
  Response : TXDataClientResponse;
  aOrganisationSecretKey : String;
  JSObjectResponse : JS.TJSObject;
  aStrResponse     : String;
  aStream          : TMemoryStream;
  aStrList         : TStringList;

begin
  XDataWebClientLicense.Connection    := XDataWebConnectionLicense;
  Response := await(XDataWebClientLicense.RawInvokeAsync('IMagisterLicenseService.GetOrganizationLicensesForWeb', ['EIFGLN99STWHIJ8U', 19, -1]));

  JSObjectResponse := TJSObject(Response.Result);
  aStrResponse     := JS.toString(JSObjectResponse);
  WebMemo1.Text    := aStrResponse;
end;

Obviously, I see the data in the WebMemo JSON formatted by fields. It has the following very simple syntax.

[{
"ID_ORGANISATION_LICENSE":107,
"ID_MAGISTER_LICENSE":99,
"APPLICATIONID":1,
"DISTRIBUTIONID":6,
"IDORGANISATION":3648,
"IDLOCATION":7872,
"LICENSE_TYPEOF":1,
"NAME":"Smartcash Hq - Licenta Permanenta Aplicatie",
"DESCRIPTION":"",
"OTHER_INFO":"Marina",
"PRODUCT_VERSION":19,
"PRICE":900,
"VAT_PERCENT":19,
"ACQUISITION_CONTRACT":"1",
"ACQUISITION_CONTRACT_DATE":"20220129",
"LICENSE_EXPIRE_DATE":"21001231",
"ISACTIVATED":1,
"LAST_ACTIVATION_DATE":"20220209T154233",
"LAST_VALIDATION_DATE":"20220301T072554",
"ORGANISATION":"Magister Software SRL",
"ORGANISATION_PHONE_1":"0318210150",
"ORGANISATION_PHONE_2":"0751517380",
"ORGANISATION_EMAIL_1":"dor@magister.ro",
"ORGANISATION_EMAIL_2":"dor.padureanu@gmail.com",
"ORGANISATION_CITY":"Bucuresti",
"ORGANISATION_CODE":"",
"ORGANISATION_COUNTRY":"Romania",
"ORGANISATION_STATE":"Sector 2",
"ORGANISATION_STREET":"Str. Radu de la Afumati nr. 62",
"SUBSCRIPTION_EVENT_DATE":"20260101",
"SUBSCRIPTION_PERCENT":10,
"SUBSCRIPTION_ACTIVE":1,
"ORGANISATION_ISACTIV":1,
"ORGANISATION_CRM_CODE":"ACC488",
"LOCATION":"Magister Software SRL - Departament Analiza",
"LOCATION_TYPEOF":1,
"LOCATION_PHONE_1":"0318210150",
"LOCATION_PHONE_2":"0751517380",
"LOCATION_EMAIL_1":"analiza@magister.ro",
"LOCATION_EMAIL_2":"dor.padureanu@gmail.com",
"LOCATION_CITY":"Bucuresti",
"LOCATION_CODE":"",
"LOCATION_COUNTRY":"Romania",
"LOCATION_STATE":"Sector 2",
"LOCATION_STREET":"Str. Radu de la Afumati nr. 62",
"USER_ACTIVATION":"Marina Bibac"
},
{
"ID_ORGANISATION_LICENSE":108,
"ID_MAGISTER_LICENSE":99,
"APPLICATIONID":1,
"DISTRIBUTIONID":6,
"IDORGANISATION":3648,
"IDLOCATION":7871,
"LICENSE_TYPEOF":1,
"NAME":"Smartcash Hq - Licenta Permanenta Aplicatie",
"DESCRIPTION":"",
"OTHER_INFO":"ionut",
"PRODUCT_VERSION":19,
"PRICE":900,
"VAT_PERCENT":19,
"ACQUISITION_CONTRACT":"1",
"ACQUISITION_CONTRACT_DATE":"20220129",
"LICENSE_EXPIRE_DATE":"21001231",
"ISACTIVATED":1,
"LAST_ACTIVATION_DATE":"20220214T120750",
"LAST_VALIDATION_DATE":"20220302T100300",
"ORGANISATION":"Magister Software SRL",
"ORGANISATION_PHONE_1":"0318210150",
"ORGANISATION_PHONE_2":"0751517380",
"ORGANISATION_EMAIL_1":"dor@magister.ro",
"ORGANISATION_EMAIL_2":"dor.padureanu@gmail.com",
"ORGANISATION_CITY":"Bucuresti",
"ORGANISATION_CODE":"",
"ORGANISATION_COUNTRY":"Romania",
"ORGANISATION_STATE":"Sector 2",
"ORGANISATION_STREET":"Str. Radu de la Afumati nr. 62",
"SUBSCRIPTION_EVENT_DATE":"20260101",
"SUBSCRIPTION_PERCENT":10,
"SUBSCRIPTION_ACTIVE":1,
"ORGANISATION_ISACTIV":1,
"ORGANISATION_CRM_CODE":"ACC488",
"LOCATION":"Magister Software SRL - Departament Software",
"LOCATION_TYPEOF":1,
"LOCATION_PHONE_1":"0318210150",
"LOCATION_PHONE_2":"0751517380",
"LOCATION_EMAIL_1":"software@magister.ro",
"LOCATION_EMAIL_2":"dor.padureanu@gmail.com",
"LOCATION_CITY":"Bucuresti",
"LOCATION_CODE":"",
"LOCATION_COUNTRY":"Romania",
"LOCATION_STATE":"Sector 2",
"LOCATION_STREET":"Str. Radu de la Afumati nr. 62",
"USER_ACTIVATION":"Ionut Lesnic"
}...]

Now, my question is, how can I load it in a XDataWebDataset or any dataset compatible with TMS WebCore Apps?
My goal is to show it in a grid and do some updates on the selected record.

Thank you

Mehmet_Emin_Borbor · March 2, 2022, 5:37pm

TWebClientDataSet version would be something like below.

ADataSet.Active := False;
ADataSet.Rows := TJSArray(JSObjectResponse);
ADataSet.Active := True;

Mehmet_Emin_Borbor · March 2, 2022, 5:44pm

But for TXDataWebClient check this link
https://doc.tmssoftware.com/biz/xdata/guide/web.html#loading-data-manually

XDataWebDataset1.SetJsonData(Response.Result);
XDataWebDataset1.Open;

Padureanu_Dor_Bujor · March 3, 2022, 10:21am

Unfortunately it doesn't work.
I tried both methods but it doesn't load anything inside dataset.
Is there any other approach?

wlandgraf · March 3, 2022, 11:15am

The methods mentioned by @Mehmet_Emin_Borbor are the correct way to do so.

Padureanu_Dor_Bujor · March 3, 2022, 3:32pm

Those methods don't understand following JSON syntax:

[{
"ID_ORGANISATION_LICENSE":107,
"ID_MAGISTER_LICENSE":99,
"APPLICATIONID":1,
"DISTRIBUTIONID":6,
"IDORGANISATION":3648,
"IDLOCATION":7872,
"LICENSE_TYPEOF":1,
"NAME":"Smartcash Hq - Licenta Permanenta Aplicatie",
"DESCRIPTION":"",
"OTHER_INFO":"Marina",
"PRODUCT_VERSION":19,
"PRICE":900,
"VAT_PERCENT":19,
"ACQUISITION_CONTRACT":"1",
"ACQUISITION_CONTRACT_DATE":"20220129",
"LICENSE_EXPIRE_DATE":"21001231",
"ISACTIVATED":1,
"LAST_ACTIVATION_DATE":"20220209T154233",
"LAST_VALIDATION_DATE":"20220301T072554",
"ORGANISATION":"Magister Software SRL",
"ORGANISATION_PHONE_1":"0318210150",
"ORGANISATION_PHONE_2":"0751517380",
"ORGANISATION_EMAIL_1":"dor@magister.ro",
"ORGANISATION_EMAIL_2":"dor.padureanu@gmail.com",
"ORGANISATION_CITY":"Bucuresti",
"ORGANISATION_CODE":"",
"ORGANISATION_COUNTRY":"Romania",
"ORGANISATION_STATE":"Sector 2",
"ORGANISATION_STREET":"Str. Radu de la Afumati nr. 62",
"SUBSCRIPTION_EVENT_DATE":"20260101",
"SUBSCRIPTION_PERCENT":10,
"SUBSCRIPTION_ACTIVE":1,
"ORGANISATION_ISACTIV":1,
"ORGANISATION_CRM_CODE":"ACC488",
"LOCATION":"Magister Software SRL - Departament Analiza",
"LOCATION_TYPEOF":1,
"LOCATION_PHONE_1":"0318210150",
"LOCATION_PHONE_2":"0751517380",
"LOCATION_EMAIL_1":"analiza@magister.ro",
"LOCATION_EMAIL_2":"dor.padureanu@gmail.com",
"LOCATION_CITY":"Bucuresti",
"LOCATION_CODE":"",
"LOCATION_COUNTRY":"Romania",
"LOCATION_STATE":"Sector 2",
"LOCATION_STREET":"Str. Radu de la Afumati nr. 62",
"USER_ACTIVATION":"Marina Bibac"
},
{
"ID_ORGANISATION_LICENSE":108,
"ID_MAGISTER_LICENSE":99,
"APPLICATIONID":1,
"DISTRIBUTIONID":6,
"IDORGANISATION":3648,
"IDLOCATION":7871,
"LICENSE_TYPEOF":1,
"NAME":"Smartcash Hq - Licenta Permanenta Aplicatie",
"DESCRIPTION":"",
"OTHER_INFO":"ionut",
"PRODUCT_VERSION":19,
"PRICE":900,
"VAT_PERCENT":19,
"ACQUISITION_CONTRACT":"1",
"ACQUISITION_CONTRACT_DATE":"20220129",
"LICENSE_EXPIRE_DATE":"21001231",
"ISACTIVATED":1,
"LAST_ACTIVATION_DATE":"20220214T120750",
"LAST_VALIDATION_DATE":"20220302T100300",
"ORGANISATION":"Magister Software SRL",
"ORGANISATION_PHONE_1":"0318210150",
"ORGANISATION_PHONE_2":"0751517380",
"ORGANISATION_EMAIL_1":"dor@magister.ro",
"ORGANISATION_EMAIL_2":"dor.padureanu@gmail.com",
"ORGANISATION_CITY":"Bucuresti",
"ORGANISATION_CODE":"",
"ORGANISATION_COUNTRY":"Romania",
"ORGANISATION_STATE":"Sector 2",
"ORGANISATION_STREET":"Str. Radu de la Afumati nr. 62",
"SUBSCRIPTION_EVENT_DATE":"20260101",
"SUBSCRIPTION_PERCENT":10,
"SUBSCRIPTION_ACTIVE":1,
"ORGANISATION_ISACTIV":1,
"ORGANISATION_CRM_CODE":"ACC488",
"LOCATION":"Magister Software SRL - Departament Software",
"LOCATION_TYPEOF":1,
"LOCATION_PHONE_1":"0318210150",
"LOCATION_PHONE_2":"0751517380",
"LOCATION_EMAIL_1":"software@magister.ro",
"LOCATION_EMAIL_2":"dor.padureanu@gmail.com",
"LOCATION_CITY":"Bucuresti",
"LOCATION_CODE":"",
"LOCATION_COUNTRY":"Romania",
"LOCATION_STATE":"Sector 2",
"LOCATION_STREET":"Str. Radu de la Afumati nr. 62",
"USER_ACTIVATION":"Ionut Lesnic"
}...]

What is the Json syntax for Response.Result undertood by XDataWebDataset1.SetJsonData(Response.Result) method?

wlandgraf · March 3, 2022, 4:43pm

It accepts a JSON array, which is the case of your JSON. Please provide more details, error message, etc.

AndrewSimard · March 3, 2022, 6:07pm

I too struggled to get a JSON array loaded into a local dataset. I was originally interested in using the more complex JSON generated by FireDAC which also includes explicit information about datatypes. You can read more about that in these posts: v1, v2, and v3. You might find some clues as to what is tripping you up, particularly as it pertains to SetJSONData.

While that was ultimately very successful, I ended up using Tabulator in my project for handling the grid side of things, which deals directly with the simpler JSON variant you're using (the output of BatchMove operations). And thus I didn't need to worry about getting it into a local dataset in the first place.

Padureanu_Dor_Bujor · March 4, 2022, 3:21pm

Thank you Andrew for your message it helped me a lot to understand that I'm completly lost! For me, a beginner in JavaScript programming it deepen the problems. You where the closest to my needs, but it was not enough.

We used before superobject to handle Delphi JSON, but inside WebCore nothing else works then it's own JSON handling functions. I've tryed including superobject unit and use it to parse the JSON but it does not compile.

So here is the my reformulated question.

How can I put the following JSON formatted string:

[{
"ID":107,
"NAME":"Test1 Name",
},
{
"ID":108,
"NAME":"Test2 Name",
},
{
"ID":109,
"NAME":"Test3 Name",
}]

inside a TXDataWebDataSet using Web Core handling functions.

Many thank for you all, who understand what is under web core hood!!!
Because all the tools we had in Delphi don't work with web core components (debug, code completion. units exploring, classes, etc).

AndrewSimard · March 4, 2022, 9:52pm

I totally agree that JSON is one of those core topics that could benefit from a little more coverage. Certainly if I were to write a book about TMS Web Core, Chapter 1, 2 and 3 would be "JSON", "JSON and Delphi" and "JSON and TMS Web Core" It doesn't help that there are actually several ways of dealing with JSON within TMS Web Core. And sometimes the best way is to try to avoid it altogether, as we'll soon see here.

Being new to Javascript is also something I struggle with. It is a very different environment, full of little surprises all over the place. Amazing that it works at all, really. But super-powerful once you get a handle on everything. I find that the combination of Delphi's tools and the JS console do a pretty good job of providing enough debugging information but it is different than a pure VCL environment. If you're having issues with things like class browsing and code completion, you should post questions about them though, as those should be working.

So to address your problem, I offer up this example. The main thing to be aware of at the outset is that your JSON didn't work because of the trailing commas in each array element. Not sure if you can address that in your source data for your project, but hopefully a minor issue. But one of those little surprises that stops you in your tracks without really expecting it to.

Here's the example code:

unit JSONTest;

interface

uses
  System.SysUtils, System.Classes, JS, Web, WEBLib.Graphics, WEBLib.Controls,
  WEBLib.Forms, WEBLib.Dialogs, Vcl.StdCtrls, WEBLib.StdCtrls, Vcl.Controls, WEBLib.JSON,
  Data.DB, XData.Web.JsonDataset, XData.Web.Dataset, XData.Web.Client;

type
  TForm1 = class(TWebForm)
    WebMemo1: TWebMemo;
    WebButton1: TWebButton;
    procedure WebButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.WebButton1Click(Sender: TObject);
var
  SomeString: WideString;
  SomeDataSet: TXDataWebDataSet;
  StrField: TStringField;
  IntField: TIntegerField;

begin
  WebMemo1.Clear;
  WebMemo1.Lines.Add('JSON testing');

  // Arbitrary JSON string.  Note that this is a pretty strict definition of JSON - eg, no trailing commas anywhere
  SomeString := '[{"ID":107,"NAME":"Test1 Name"},{"ID":108,"NAME":"Test2 Name"},{"ID":109,"NAME":"Test3 Name"}]';
  WebMemo1.Lines.Add('Source String:');
  WebMemo1.Lines.Add(SomeString);

  // Create a new DataSet
  SomeDataset := TXDataWebDataSet.Create(nil);

  // Define its fields explicity
  IntField := TIntegerField.Create(SomeDataSet);
  IntField.FieldName := 'ID';
  IntField.DataSet := SomeDataSet;

  StrField := TStringField.Create(SomeDataSet);
  StrField.FieldName := 'NAME';
  StrField.Size := 50;
  Strfield.DataSet := SomeDataSet;

  // Load the JSON into the DataSet
  SomeDataset.SetJsonData(TJSArray(TJSJson.Parse(SomeString)));

  // Test the DataSet
  SomeDataSet.Open;
  SomeDataSet.First;
  WebMemo1.Lines.Add('Dataset Data: '+IntToStr(SomeDataSet.RecordCount)+' Records');
  while not(SomeDataSet.EOF) do
  begin
    WebMemo1.Lines.Add(SomeDataSet.FieldByName('ID').AsString+': '+SomeDataSet.FieldByName('NAME').AsString);
    SomeDataSet.Next;
  end;

end;

And the output looks like this.

JSON testing
Source String:
[{"ID":107,"NAME":"Test1 Name"},{"ID":108,"NAME":"Test2 Name"},{"ID":109,"NAME":"Test3 Name"}]
Dataset Data: 3 Records
107: Test1 Name
108: Test2 Name
109: Test3 Name

So the JSON bit is really just understanding that you have a JSON Array and need to pass it to SetJsonData in a format that it expects. It's not particularly forgiving, and it doesn't report anything when it doesn't like what it gets (or maybe I just don't know how to listen to it?).

I think the main thing though is that you have to create a dataset and then create the fields you're expecting to populate before you can load the data. There are other ways of creating the fields for the dataset if you have a connection to a database or something like that, particularly if using components in the IDE.

AndrewSimard · March 4, 2022, 11:00pm

Also, in the examples I linked to earlier, the reason I used the FireDAC version of the exported data was to have the datatypes included. So the JSON is parsed to determine these datatypes so that the fields can be properly created before the data is loaded. Tedious. But just until you have a function to do this for you that can handle all your datatypes so you don't have to do it again

I find that in javascript land there are a whole lot of assumptions being made about datatypes, usually that they are either integer or string, and most often string, and you run into issues when you want to do other things, like passing dates or times and so on. So it can be more forgiving at times but that isn't always as helpful as it might seem when you're dealing with actual databases.

Padureanu_Dor_Bujor · March 5, 2022, 1:35pm

Thank you very much @AndrewSimard!!! You are great!!

Here is what I did:
I took your FIREDAC example and I understood what you did. It is a great example and it works for me too. I will use it because of the reason you mentioned: Field datatypes can be populated automatically.

The main thing that only you mentioned it, was that I have to create the fields manually in the dataset, and without that any load method don't work.
It is crucial, and without you I would struggle in vane to load the dataset even now.

Thanks for the second example for BM JSON version.
It helps a lot in order to understand the way web core handles arrays.

The construction:

SomeDataset.SetJsonData(TJSArray(TJSJson.Parse(SomeString)));

Is very usefull.

Now, the only problem I have remains the initialization and loading the TDateTime and TDate fields. Because in BM Delphi JSON a date field is string converted like 20220301 for 2022/03/31 or 20220301T2300 for 2022/03/31 23:00 and the Load method don't perform the conversion automatically.

What I did for this type of fields was to initialize the fields based in FIREDAC JSON defs like:

....
    else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Date') then
    begin
      SetLength(DateFields, Length(DateFields) + 1);
      DateFields[Length(DateFields)-1] := TDateField.Create(XDataWebDataSet);
      DateFields[Length(DateFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
      DateFields[Length(DateFields)-1].Dataset   := XDataWebDataSet;
    end
    else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'DateTimeStamp') then
    begin
      SetLength(DateTimeFields, Length(DateTimeFields) + 1);
      DateTimeFields[Length(DateTimeFields)-1] := TDateTimeField.Create(XDataWebDataSet);
      DateTimeFields[Length(DateTimeFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
      DateTimeFields[Length(DateTimeFields)-1].Dataset   := XDataWebDataSet;
    end;
....

using your code, but obviously, when I use SetJsonData method it loads nothing in these TDate fields .
When I declare them as strings shure they appear.
I tried several methods to overcome this.

First I tried to create Calculated fields, but this doesn't seam to work, because the design time calculated created fields doesn't seem to work.
The second one is to load the data using basic Append and Post operation to the dataset and add each row using the following approach:

for i := 0 to JRowList.Length - 1 do  begin
  XDataWebDataSet.Append;
  XDataWebDataSet.FieldByName('ID_ORGANISATION_LICENSE').AsInteger     := Integer(TJSObject(JRowList.Elements[i])['ID_ORGANISATION_LICENSE']);
  .....
  XDataWebDataSet.Post;
end;

I get strange results using second approach because not all the lines where posted, but I will try to test more, maybe I'm missing something.
If you have any advice related to a way of loading TDateFields in the dataset it would be highly appreciated.

Anyway thanks very much for your help man! You are great!

AndrewSimard · March 5, 2022, 8:39pm

You're most welcome, happy to help. I was in excatcly your shoes only a few months ago!

As for your next question, let me introduce you to my little friend.... ISO 8601. The underlying problem is that FireDAC (or perhaps other sources of date/time data) strictly adhere to one format, whereas the SetJsonData function strictly adheres to a different format (though technically in the same standard?!). So SetJsonData just ignores it.

I ran into this exact problem and tackled it in perhaps not the most elegant way here. I just changed the date/time format that FireDAC uses when it generates JSON. Which wasn't an easy thing to figure out.

I don't know if there are other, easier alternatives. Maybe alter the contents of the JSON after it has been generated? Or if there is way to change FireDAC's output without altering its source code. Perhaps altering the source code for SetJsonData could also work, but that's even less desirable given that TMS updates their codebase far more frequently than I update FireDAC. Ideally TMS will at some point update it to also accept these popular variants of ISO 8601.

As for the field-generating code, I do the initialization just as youv'e got there for Date and DateTimeStamp. I don't know if I've run across any Time fields yet but probably the same approach would apply. Depending on your underlying databases there are quite a number of other types of fields that could come into play, so keep an eye out for those as well.

AndrewSimard · March 6, 2022, 1:16am

For anyone using or considering Tabulator, it has a "mutator" mechanism to help deal with this kind of thing. Incoming data is passed through a function that can then change it before it reaches the table. So if you've got an odd date format to deal with or a lookup value to dereference or something, then a mutator function can be added for that field. This is separate from formatters and editors that are also field-level mechanisms to change how the field is displayed and altered. By defeault, it guesses at all these things and does pretty well for a first pass. But as I was mentioning previously, javascript tends to see everything as either integers or strings so as soon as you try anything else you've got to roll up your sleeves and get to work

Padureanu_Dor_Bujor · March 6, 2022, 7:35am

Thanks Andrew.
I end up by creating at runtime calculated TStringField fields that duplicates all the "TDate/TDateTime" fields coming from FIREDAC JSON also as StringFields.
They cannot be created at designtime and this is why it didn't work at beginning.
And OnCalcFields we format the calculated stringfields as we need using FormatDate function and a local FireDac Date string parsing function.
It is a good compromise and it works very well.
Thanks again for your support.

AndrewSimard · March 6, 2022, 7:58am

Ah, very good.

system · March 14, 2022, 2:09pm

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