Blog
All Blog Posts | Next Post | Previous Post
Extend TMS WEB Core with JS Libraries with Andrew: Epic JSON Primer (part 2)
Bookmarks:
Friday, May 6, 2022
Here is part 2 of "Extend TMS WEB Core with JS Libraries with Andrew: Epic JSON Primer"
20 : Relative Performance.
I've often heard that while it is possible to use the TJSONObject variant of these approaches (the PAS code we've been working so diligently on), it is preferable instead to use the WC variant as it will be better performing. We'll put that to the test here in a bit of a contrived example. Often, readability and re-usability of code is more important than straight-up performance, particularly for difficult code that isn't executed frequently. But at the same time, situations certainly do come up where a bit of code is executed frequently in a tight loop, and squeezing out every bit of performance is important.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
PAS_Object: TJSONObject;
ElapsedTime: TDateTime;
i: Integer;
Count: Integer;
begin
ElapsedTime := Now;
// JS Create 1,000,000 Objects
asm
var JS_Object = {};
for (var i = 0; i <= 999999; i++) {
JS_Object['test '+i] = 'test '+i;
}
end;
console.log('JS Create: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
ElapsedTime := Now;
// JS Count Objects
asm
Count = Object.keys(JS_Object).length;
end;
console.log('JS Count: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
ElapsedTime := Now;
// WC Create 1,000,000 Objects
WC_Object := TJSObject.new;
for i := 0 to 999999 do
WC_Object['test '+IntToStr(i)] := 'test '+IntToStr(i);
console.log('WC Create: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
ElapsedTime := Now;
// WC Count Objects
Count := length(TJSObject.keys(WC_Object));
console.log('WC Count: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
ElapsedTime := Now;
// PAS Create 10,000 Objects
PAS_Object := TJSONObject.Create;
for i := 0 to 9999 do
PAS_Object.AddPair('test '+IntToStr(i),'test '+IntToStr(i));
console.log('PAS Create: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
ElapsedTime := Now;
// PAS Count Objects
Count := PAS_Object.Count;
console.log('PAS Count: '+IntToStr(MillisecondsBetween(Now,ElapsedTime))+' ms');
end;
console.log output:
JS Create: 1154 ms
JS Count: 301 ms
WC Create: 1006 ms
WC Count: 272 ms
PAS Create: 27439 ms
PAS Count: 0 ms
So that was eye-opening! I had to actually reduce the PAS loop to be a mere 10,000 Objects instead of the 1,000,000 Objects of the JS and WC loops just so that it would complete. And it still took nearly 30x longer to create the JSON Objects (so extrapolating, it is 3,000 times slower?!) Seems that someone somewhere has some optimization work to do. Note that the count is trivial in the PAS case so it doesn't really even register, where as with JS and WC it actually has to do quite a bit of work to get at that number. Takeaway? Well, same as before, really, just surprising that the penalty is so high. Hopefully that situation will improve in time. Or if it doesn't, that's a pretty solid argument for migrating PAS code that is in any kind of loop to use the JS or WC variant. To be fair, this is likely the only code in this entire article where any performance difference would even be detectable.
Also note that, running the test over and over again, the JS and WC Create times vary quite a bit, sometimes down to half as long. But the Count times didn't fluctuate much at all. PAS remained largely the same. This could be due to many things, but there's a lot of JS optimization that happens all over the place, so not too surprising. It does make it a challenge to come up with reasonable benchmarks though - you need to run lots of tests to get an average figure for it to be meaningful, and even then you have to factor in the conditions surrounding the test really carefully - was the app already loaded, was there an animation running somewhere, etc.
21 : Iterate through JSON Object Elements.
We've had examples of this kind of thing here and there already, but here's a more explicit example for all three variations.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
PAS_Object: TJSONObject;
i: Integer;
const
SampleObjectData = '{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}';
begin
asm var JS_Object = JSON.parse(SampleObjectData); end;
WC_Object := TJSJSON.parseObject(SampleObjectData);
PAS_Object := TJSONObject.ParseJSONValue(SampleObjectData) as TJSONObject;
asm
for (var Key in JS_Object) {
console.log('JS Key: '+Key+' Value: '+JS_Object[Key])
}
end;
i := 0;
while (i < length(TJSOBject.keys(WC_Object))) do
begin
console.log('WC Key: '+string(TJSObject.keys(WC_Object)[i])+' Value: '+string(WC_Object[TJSObject.keys(WC_Object)[i]]));
i := i + 1;
end;
i := 0;
while (i < PAS_Object.Count) do
begin
console.log('PAS Key: '+PAS_Object.Pairs[i].JSONString.Value+' Value: '+PAS_Object.Pairs[i].JSONValue.Value);
i := i + 1;
end;
end;
console.log output:
JS Key: apple Value: fruit
JS Key: banana Value: fruit
JS Key: orange Value: fruit
JS Key: carrot Value: vegetable
JS Key: potato Value: vegetable
WC Key: apple Value: fruit
WC Key: banana Value: fruit
WC Key: orange Value: fruit
WC Key: carrot Value: vegetable
WC Key: potato Value: vegetable
PAS Key: apple Value: fruit
PAS Key: banana Value: fruit
PAS Key: orange Value: fruit
PAS Key: carrot Value: vegetable
PAS Key: potato Value: vegetable
22 : Traverse Entire JSON Object.
We now have everything we need to tackle any kind of JSON that comes our way. Typically there isn't any need to traverse an entire JSON Object directly. Normally you're just looking to pick out certain values or update others. One use case though is to output a formatted version of the JSON Object. There are lots of online resources for doing this kind of thing for you - just copy and paste the JSON you'd like formatted, and it will output a nicer (pretty or beautified) version easily enough. Here we do something similar, annotating whatever we find. Note that the output is not at all JSON, just a list of what is in it and details about the data types. However, it should be able to regurgitate whatever JSON you through at it. It isn't pretty, but should cover everything.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
PAS_Object: TJSONObject;
const
SampleObjectData = '[[[123,{"key":true,"another key":"abc","more keys":[1,2,false]},null]]]';
function WC_Print(Element:TJSObject; Indentation:String; Key:String):String;
var
str: String;
typ: String;
begin
str := '';
typ := '';
if (Element = nil) then begin str := 'Null (Null)'; typ := 'Null'; end
else if (Element.ToString = 'true') then begin str := 'True (Boolean)'; typ := 'Boolean'; end
else if (Element.ToString = 'false') then begin str := 'False (Boolean)'; typ := 'Boolean'; end
else if (TJSJSON.stringify(Element).startsWith('"')) then begin str := Element.toString+' (String)'; typ := 'String'; end
else if (TJSJSON.stringify(Element).startsWith('{')) then begin str := 'Object {'; typ := 'Object'; end
else if (TJSJSON.stringify(Element).startsWith('[')) then begin str := 'Array ['; typ := 'Object'; end
else begin str := Element.ToString+' (Number)'; typ := 'Number'; end;
if (Key = '')
then console.log(Indentation+' '+str)
else console.log(Indentation+' '+Key+': '+str);
Result := typ;
end;
procedure WC_Output(JSONObject:TJSObject; Indentation:String);
var
i: integer;
typ: string;
const
indent = '____';
begin
if (TJSJSON.stringify(JSONObject).startsWith('[')) then
begin
if (Indentation = '') then
begin
console.log('WC [');
Indentation := indent;
end;
for i := 0 to (length(TJSObject.keys(JSONObject))-1) do
begin
typ := WC_Print(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation, IntToStr(i));
if (typ = 'Object') then
begin
WC_Output(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation+indent);
console.log(Indentation+' }');
end
else if (typ = 'Array') then
begin
WC_Output(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation+indent);
console.log(Indentation+' ]');
end;
end;
if (Indentation = indent) then console.log(' ]');
end
else if (TJSJSON.stringify(JSONObject).startsWith('{')) then
begin
if (Indentation = '') then
begin
console.log('WC {');
Indentation := '____';
end;
for i := 0 to (length(TJSObject.keys(JSONObject))-1) do
begin
typ := WC_Print(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation, string(TJSObject.keys(JSONObject)[i]));
if (typ = 'Object') then
begin
WC_Output(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation+indent);
console.log(Indentation+' }');
end
else if (typ = 'Array') then
begin
WC_Output(TJSObject(JSONObject[String(TJSObject.keys(JSONObject)[i])]), Indentation+indent);
console.log(Indentation+' ]');
end;
end;
if (Indentation = indent) then console.log('}');
end
else
begin
WC_Print(JSONObject,Indentation,'')
end;
end;
function PAS_Print(Element:TJSONValue; Indentation:String; Key:String):String;
var
str: String;
typ: String;
begin
str := '';
typ := '';
if (Element.ClassName = 'TJSONNull') then begin str := 'Null (Null)'; typ := 'Null'; end
else if (Element.ClassName = 'TJSONTrue') then begin str := 'True (Boolean)'; typ := 'Boolean'; end
else if (Element.ClassName = 'TJSONFalse') then begin str := 'False (Boolean)'; typ := 'Boolean'; end
else if (Element.ClassName = 'TJSONString') then begin str := Element.toString+' (String)'; typ := 'String'; end
else if (Element.ClassName = 'TJSONNumber') then begin str := Element.toString+' (Number)'; typ := 'String'; end
else if (Element.ClassName = 'TJSONObject') then begin str := 'Object {'; typ := 'Object'; end
else if (Element.ClassName = 'TJSONArray') then begin str := 'Array ['; typ := 'Object'; end;
if (Key = '')
then console.log(Indentation+' '+str)
else console.log(Indentation+' '+Key+': '+str);
Result := typ;
end;
procedure PAS_Output(JSONObject:TJSONObject; Indentation:String);
var
i: integer;
typ: string;
const
indent = '____';
begin
if (JSONObject.ClassName = 'TJSONArray') then
begin
if (Indentation = '') then
begin
console.log('PAS [');
Indentation := indent;
end;
for i := 0 to ((JSONObject as TJSONArray).Count - 1) do
begin
typ := PAS_Print((JSONObject as TJSONArray)[i] as TJSONValue, Indentation, IntToStr(i));
if (typ = 'Object') then
begin
PAS_Output((JSONObject as TJSONArray)[i] as TJSONObject, Indentation+indent);
console.log(Indentation+' }');
end
else if (typ = 'Array') then
begin
PAS_Output((JSONObject as TJSONArray)[i] as TJSONArray, Indentation+indent);
console.log(Indentation+' ]');
end;
end;
if (Indentation = indent) then console.log(']');
end
else if (JSONObject.ClassName = 'TJSONObject') then
begin
if (Indentation = '') then
begin
console.log('PAS {');
Indentation := '____';
end;
for i := 0 to (JSONObject.Count - 1) do
begin
typ := PAS_Print(JSONObject.Items[i], Indentation, JSONObject.Pairs[i].JSONString.Value);
if (typ = 'Object') then
begin
PAS_Output(JSONObject.Items[i] as TJSONObject, Indentation+indent);
console.log(Indentation+' }');
end
else if (typ = 'Array') then
begin
PAS_Output(JSONObject.Items[i] as TJSONArray, Indentation+indent);
console.log(Indentation+' ]');
end;
end;
if (Indentation = indent) then console.log('}');
end
else
begin
PAS_Print(JSONObject,Indentation,'')
end;
end;
begin
asm var JS_Object = JSON.parse(SampleObjectData); end;
WC_Object := TJSJSON.parseObject(SampleObjectData);
PAS_Object := TJSONObject.ParseJSONValue(SampleObjectData) as TJSONObject;
asm
function JS_Print(Element, Indentation, Key) {
var str = '';
var typ = '';
if (Element === null) { str = 'Null (Null)'; typ = 'Null'; }
else if (Element === true) { str = 'True (Boolean)'; typ = 'Boolean'; }
else if (Element === false) { str = 'False (Boolean)'; typ = 'Boolean'; }
else if (Array.isArray(Element)) { str = 'Array ['; typ = 'Array'; }
else if (typeof Element === 'string') { str = '"'+Element+'" (String)'; typ = 'String'; }
else if (typeof Element === 'number') { str = Element+' (Number)'; typ = 'Number'; }
else if (typeof Element === 'object') { str = 'Object {'; typ = 'Object'; }
if (Key === undefined) { console.log(Indentation+' '+str); }
else { console.log(Indentation+' '+Key+': '+str); }
return(typ)
}
function JS_Output(JSONObject, Indentation) {
const indent = '____';
if (Array.isArray(JSONObject)) {
if (Indentation == '') {
console.log("JS [");
Indentation = indent;
}
for (var i = 0; i < JSONObject.length; i++) {
switch (JS_Print(JSONObject[i],Indentation,i)) {
case 'Object': JS_Output(JSONObject[i],Indentation+indent); console.log(Indentation+" }"); break;
case 'Array': JS_Output(JSONObject[i],Indentation+indent); console.log(Indentation+" ]"); break;
}
}
if (Indentation == indent) { console.log("]") }
}
else if (typeof JSONObject === 'object') {
if (Indentation == '') {
console.log("JS {");
Indentation = '____';
}
for (var key in JSONObject) {
switch (JS_Print(JSONObject[key],Indentation,key)) {
case 'Object': JS_Output(JSONObject[key],Indentation+indent); console.log(Indentation+" }"); break;
case 'Array': JS_Output(JSONObject[key],Indentation+indent); console.log(Indentation+" ]"); break;
}
}
if (Indentation == indent) { console.log("}") }
}
else {
JS_Print(JSONObject,Indentation);
}
}
end;
asm JS_Output(JS_Object,''); end;
WC_Output(WC_Object,'');
PAS_Output(PAS_Object,'');
end;
console.log output (just JS, WC and PAS are the same):
JS [
____ 0: Array [
________ 0: Array [
____________ 0: 123 (Number)
____________ 1: Object {
________________ key: True (Boolean)
________________ another key: "abc" (String)
________________ more keys: Array [
____________________ 0: 1 (Number)
____________________ 1: 2 (Number)
____________________ 2: False (Boolean)
________________ ]
____________ }
____________ 2: Null (Null)
________ ]
____ ]
]
23 : Compare JSON.
The quickest (or at least, the simplest) way to compare two JSON Objects would be to just convert them to strings and check if they match. But this may not be accurate, given the unordered nature of JSON Object elements. If all you have is an Array, or if you know your Object is sorted by Key or by some other deterministic method (you created the elements in a particular order consistently, for example) then comparing strings could still work. But if that's not the case, another approach is needed to determine this 'semantic' equivalence. Here we iterate through one of the JSON Objects and then check that we can find everything in the appropriate place in a second JSON Object. For the Objects, we just lookup the Keys so they should match and the order doesn't matter. For Arrays, we use the index and the order does indeed matter. This approach also makes it possible to adjust the strictness of the comparison. Like if you wanted to have a case-insensitive match on string Values, for example, or less accuracy (or more!) when it comes to comparing numbers.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object1: TJSObject;
WC_Object2: TJSObject;
PAS_Object1: TJSONObject;
PAS_Object2: TJSONObject;
const
SampleObjectData1 = '[[[123,{"key":true,"another key":"abc","more keys":[1,3,false]},null,[{"a":"1","b":2,"c":"3"}]]]]';
SampleObjectData2 = '[[[123,{"another key":"abc","key":true,"more keys":[1,3,false]},null,[{"c":"3","b":2,"a":"1"}]]]]';
function WC_Compare(Element1: TJSObject; Element2:TJSObJect):Boolean;
var
test: Boolean;
i: integer;
begin
test := true;
if ((Element1 = nil) and (Element2 = nil)) then test := true
else if (Element1 = nil) or (Element2 = nil) then test := false
else if ((Element1.toString = 'true') and (Element2.toString = 'true')) then test := true
else if ((Element1.toString = 'false') and (Element2.toString = 'false')) then test := true
else if (TJSJSON.stringify(Element1).startsWith('[')) and (TJSJSON.stringify(Element2).startsWith('[')) and (length(TJSObject.keys(Element1)) = length(TJSObject.keys(Element2))) then
begin
for i := 0 to (length(TJSObject.keys(Element1))-1) do
begin
if not(WC_Compare(TJSObject(Element1[String(TJSObject.keys(Element1)[i])]),TJSObject(Element2[String(TJSObject.keys(ELement2)[i])]))) then
begin
test := false;
end;
end;
end
else if (TJSJSON.stringify(Element1).startsWith('{')) and (TJSJSON.stringify(Element2).startsWith('{')) and (length(TJSObject.keys(Element1)) = length(TJSObject.keys(Element2))) then
begin
for i := 0 to (length(TJSObject.keys(Element1))-1) do
begin
if not(WC_Compare(TJSObject(Element1[String(TJSObject.keys(Element1)[i])]),TJSObject(Element2[String(TJSObject.keys(Element1)[i])]))) then
begin
test := false;
end;
end;
end
else if (TJSJSON.stringify(Element1).startsWith('"')) and (TJSJSON.stringify(Element2).startsWith('"')) and (Element1.toString = Element2.toString) then test := true
else if (Element1.toString = Element2.toString) then test := true
else
begin
test := false;
end;
// if not(test) then console.log(TJSJSON.stringify(Element1)+' <> '+TJSJSON.stringify(Element2));
Result := test;
end;
function PAS_Compare(Element1: TJSONValue; Element2:TJSONValue):Boolean;
var
test: Boolean;
i: integer;
begin
test := true;
if ((Element1.ClassName = 'TJSONNull') and (Element2.Classname = 'TJSONNull')) then test := true
else if ((Element1.ClassName = 'TJSONNull') or (Element2.Classname = 'TJSONNull')) then test := false
else if ((Element1.ClassName = 'TJSONTrue') and (Element2.Classname = 'TJSONTrue')) then test := true
else if ((Element1.ClassName = 'TJSONFalse') and (Element2.Classname = 'TJSONFalse')) then test := true
else if ((Element1.ClassName = 'TJSONString') and (Element2.Classname = 'TJSONString')) and (Element1.ToString = Element2.ToString) then test := true
else if ((Element1.ClassName = 'TJSONNumber') and (Element2.Classname = 'TJSONNumber')) and (Element1.ToString = Element2.ToString) then test := true
else if ((Element1.ClassName = 'TJSONArray') and (Element2.Classname = 'TJSONArray')) and ((Element1 as TJSONArray).Count = (Element2 as TJSONArray).Count) then
begin
for i := 0 to ((Element1 as TJSONArray).Count - 1) do
begin
if not(PAS_Compare((Element1 as TJSONArray).Items[i], ((Element2 as TJSONArray).Items[i]))) then
begin
test := false;
end;
end;
end
else if ((Element1.ClassName = 'TJSONObject') and (Element2.Classname = 'TJSONObject')) and ((Element1 as TJSONOBject).Count = (Element2 as TJSONObject).Count) then
begin
for i := 0 to ((Element1 as TJSONObject).Count - 1) do
begin
if not(PAS_Compare((Element1 as TJSONObject).Pairs[i].JSONValue,(Element2 as TJSONObject).Get((Element1 as TJSONObject).Pairs[i].JSONString.value).JSONValue)) then
begin
test := false;
end;
end;
end
else
begin
if Element1.ToString <> Element2.toString
then test := false
else test := true;
end;
// if not(test) then console.log(Element1.ToString+' <> '+Element2.ToString);
Result := test;
end;
begin
asm var JS_Object1 = JSON.parse(SampleObjectData1); end;
asm var JS_Object2 = JSON.parse(SampleObjectData2); end;
WC_Object1 := TJSJSON.parseObject(SampleObjectData1);
WC_Object2 := TJSJSON.parseObject(SampleObjectData2);
PAS_Object1 := TJSONObject.ParseJSONValue(SampleObjectData1) as TJSONObject;
PAS_Object2 := TJSONObject.ParseJSONValue(SampleObjectData2) as TJSONObject;
asm
function JS_Compare(Element1, Element2) {
var test = true;
if ((Element1 === null) && (Element2 === null)) {test = true}
else if ((Element1 === null) || (Element2 === null)) {test = false}
else if ((Element1 === true) && (Element2 === true)) {test = true}
else if ((Element1 === false) && (Element2 === false)) {test = true}
else if ((Array.isArray(Element1)) && (Array.isArray(Element2)) && (Element1.length == Element2.length)) {
for (var i = 0; i < Element1.length; i++) {
if (!JS_Compare(Element1[i],Element2[i])) {
test = false;
}
}
}
else if ((typeof Element1 === 'string') && (typeof Element2 === 'string') && (Element1 === Element2)) {test = true}
else if ((typeof Element1 === 'number') && (typeof Element2 === 'number') && (Element1 === Element2)) {test = true}
else if ((typeof Element1 === 'object') && (typeof Element2 === 'object') && (Object.keys(Element1).length == Object.keys(Element2).length)) {
for (var key in Element1) {
if (!JS_Compare(Element1[key],Element2[key])) {
test = false;
}
}
}
else {
test = false;
}
// if (!test) {console.log(JSON.stringify(Element1)+' <> '+JSON.stringify(Element2))};
return(test);
}
end;
asm console.log('JS '+JS_Compare(JS_Object1, JS_Object2)); end;
console.log('WC '+BoolToStr(WC_Compare(WC_Object1,WC_Object2),true));
console.log('PAS '+BoolToStr(PAS_Compare(PAS_Object1,PAS_Object2),true));
end;
console.log output:
JS true
WC True
PAS True
24 : Sort JSON Object Elements.
JSON Objects are not ordered, but often it is desirable to display them sorted. Note that we're deliberately not updating the JSON Object with the elements sorted, but rather just iterating through them so that the output is sorted. The gist of this is just extracting all the Keys from the JSON Object, sorting the Keys by whatever method is handy, and then displaying the Keys in their newly sorted order along with the Value corresponding to each.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
PAS_Object: TJSONObject;
i: integer;
WC_Keys: TStringList;
PAS_Keys: TStringList;
const
SampleObjectData = '{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}';
begin
asm var JS_Object = JSON.parse(SampleObjectData); end;
WC_Object := TJSJSON.parseObject(SampleObjectData);
PAS_Object := TJSONObject.ParseJSONValue(SampleObjectData) as TJSONObject;
asm
var keys = Object.keys(JS_Object).sort();
for (var i = 0; i < keys.length; i++) {
console.log('JS Key: '+keys[i]+' Value: '+JS_Object[keys[i]])
}
end;
// There are other ways to sort but I didn't have much luck here.
// For example, System.Generics.Collections would hang compiler for some reason
WC_Keys := TStringList.Create;
for i := 0 to length(TJSObject.keys(WC_Object))-1 do
WC_Keys.Add(TJSObject.keys(WC_Object)[i]);
WC_Keys.sort;
for i := 0 to WC_Keys.Count -1 do
console.log('WC Key: '+WC_Keys[i]+' Value: '+string(WC_Object[WC_Keys[i]]));
// Same approach used here for same reason
PAS_Keys := TStringList.Create;
for i := 0 to PAS_Object.Count-1 do
PAS_Keys.Add(PAS_Object.Pairs[i].JSONString.Value);
PAS_Keys.sort;
for i := 0 to PAS_Keys.Count -1 do
console.log('PAS Key: '+PAS_Keys[i]+' Value: '+PAS_Object.Get(PAS_Keys[i]).JSONValue.ToString);
end;
console.log output:
JS Key: apple Value: fruit
JS Key: banana Value: fruit
JS Key: carrot Value: vegetable
JS Key: orange Value: fruit
JS Key: potato Value: vegetable
WC Key: apple Value: fruit
WC Key: banana Value: fruit
WC Key: carrot Value: vegetable
WC Key: orange Value: fruit
WC Key: potato Value: vegetable
PAS Key: apple Value: "fruit"
PAS Key: banana Value: "fruit"
PAS Key: carrot Value: "vegetable"
PAS Key: orange Value: "fruit"
PAS Key: potato Value: "vegetable"
25 : Move from JS to WC or PAS.
Let's say that you've used JavaScript to craft a bit of JSON, or happen to have it from some JavaScript code for some other reason, but then want to reference it in Delphi using either the WC or PAS variations that we've been using so far. Here, we define a JS_Object as a Delphi variable of type JSValue so that we can reference it later. And then we access it using just the WC functions and the PAS functions that we've been using up until now, without even having to define variables to hold them.
procedure TForm1.WebButton1Click(Sender: TObject);
var
JS_Object: JSValue;
const
SampleObjectData = '{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}';
begin
asm JS_Object = JSON.parse(SampleObjectData); end;
// WC: Delphi code to access JavaScript JSON Object
// Wrapping it in TJSObject() makes it equivalent to a WC TJSObject
console.log('WC: '+TJSJSON.stringify(JS_Object));
console.log('WC: '+IntToStr(length(TJSObject.keys(TJSObject(JS_Object)))));
// PAS: Converting to a TJSONObject via Strings works
console.log('PAS: '+(TJSONObject.parseJSONValue(TJSJSON.stringify(JS_Object)) as TJSONObject).ToString);
console.log('PAS: '+IntToStr((TJSONObject.parseJSONValue(TJSJSON.stringify(JS_Object)) as TJSONObject).Count));
end;
console.log output:
WC: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
WC: 5
PAS: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
PAS: 5
26 : Move from WC to JS or PAS.
Here, WC_Object can simply be accessed directly in JS. And the PAS code is basically the same as last time.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
const
SampleObjectData = '{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}';
begin
WC_Object := TJSJSON.parseObject(SampleObjectData);
// JS: Works as if it were natively defined in JS
asm
console.log('JS: '+JSON.stringify(WC_Object));
console.log('JS: '+Object.keys(WC_Object).length);
end;
// PAS: Converting to a TJSONObject via Strings works as before
console.log('PAS: '+(TJSONObject.parseJSONValue(TJSJSON.stringify(WC_Object)) as TJSONObject).ToString);
console.log('PAS: '+IntToStr((TJSONObject.parseJSONValue(TJSJSON.stringify(WC_Object)) as TJSONObject).Count));
end;
console.log output:
JS: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
JS: 5
PAS: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
PAS: 5
27 : Move from PAS to JS or WC.
And here, perhaps you've created some JSON in an VCL app using the TJSONObject variant (PAS) and then find that you want to use it directly in JavaScript or with some other code that uses the WC variant. Interestingly, if you output the PAS TJSONObject while using JS, you'll see the underlying structure and some hints as to why it is more costly to use:
{"fjv":{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"},"fjo$1":{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"},"FMembers":{"FList":{"FList":[],"FCount":0,"FCapacity":0}}}But it also gives a clue as to how to use it in JS - just reference "fjv" and you'll have your JSON. Converting via strings is also possible here, but there's a convenient JSObject property that is part of TJSONObject, making the conversion to the WC variant almost as easy.
procedure TForm1.WebButton1Click(Sender: TObject);
var
PAS_Object: TJSONObject;
const
SampleObjectData = '{"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}';
begin
PAS_Object := TJSONObject.parseJSONValue(SampleObjectData) as TJSONObject;
// JS
asm
console.log('JS: '+JSON.stringify(PAS_Object['fjv']));
console.log('JS: '+Object.keys(PAS_Object['fjv']).length);
end;
// WC
console.log('WC: '+TJSJSON.stringify(PAS_Object.JSObject));
console.log('WC: '+IntToStr(length(TJSObject.keys(PAS_Object.JSObject))));
end;
console.log output:
JS: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
JS: 5
WC: {"apple":"fruit","banana":"fruit","orange":"fruit","carrot":"vegetable","potato":"vegetable"}
WC: 5
28 : FireDAC to JSON (Simple).
JSON is often used to contain traditional datasets - columns and rows of data that we're perhaps more accustomed to. In this example, we're going to take a sample dataset and show what it looks like when converted to JSON. FireDAC is a popular choice, but any other database likely has a similar mechanism to go from a query or table or some kind of dataset into a JSON representation. Here, the assumption is that this code is running as part of a VCL app as FireDAC itself isn't (entirely?) available within TMS WEB Core directly. In this case, we're using the BatchMove method, where just the data itself is included in the JSON. The sample data is just something I've pulled directly from a project. Nothing particularly interesting or proprietary about it, just a handful of columns and a few records to show what it looks like.
procedure TMyService.GetJSONData(ParametersOfSomeKind: String):TStream; var clientDB: TFDConnection; qry: TFDQuery; bm: TFDBatchMove; bw: TFDBatchMoveJSONWriter; br: TFDBatchMoveDataSetReader; begin Result := TMemoryStream.Create; // Some kind of database connection clientDB := TFDConnection.Create(nil); clientDB.ConnectionDefName := SomeDatabaseConnection; clientDB.Connected := True; // Some kind of query qry.Connection := clientDB; qry.SQL.Text := 'select * from DATAMARK.LUTS;'; qry.Open; // Convert the query to simplified JSON 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; // Cleanup afterwards qry.Free; clientDB.Connected := False; clientDB.Free; end;
The resulting JSON might look something like this.
[{"ID":1,"LOOKUP":0,"SORTORDER":0,"RESPONSE":"Administration","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:34","GROUPTYPE":16},{"ID":2,"LOOKUP":1,"SORTORDER":1,"RESPONSE":"Labour","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:47:52","GROUPTYPE":16},{"ID":3,"LOOKUP":2,"SORTORDER":2,"RESPONSE":"IT","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:56","GROUPTYPE":16}]If we run it through a JSON formatter, it becomes a little more legible.
[
{
"ID": 1,
"LOOKUP": 0,
"SORTORDER": 0,
"RESPONSE": "Administration",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:34",
"GROUPTYPE": 16
},
{
"ID": 2,
"LOOKUP": 1,
"SORTORDER": 1,
"RESPONSE": "Labour",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:47:52",
"GROUPTYPE": 16
},
{
"ID": 3,
"LOOKUP": 2,
"SORTORDER": 2,
"RESPONSE": "IT",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:56",
"GROUPTYPE": 16
}
]
This is not terribly efficient in terms of storage - lots of text that is repeated, that sort of thing. So you wouldn't really want to do all your database work exclusively in JSON. However it is compact in the sense that there isn't anything extraneous here - the Keys represent the columns and the Values representing the data. It is an Array so it is easy to figure out how many records there are, and it uses a couple of the supported JSON data types - Strings and Numbers. There's a date/time encoded in my favourite variation of ISO8601.
NOTE: I modified my FireDAC source to output this format. By default I think it uses YYYYMMDDTHHMMSS (no separators). There's a post in the TMS Support Center that discusses this.
A dataset formatted this way in JSON is pretty useful as-is. Even if you don't know specifically what the database field types are, you have a pretty good idea. ID, LOOKUP, SORTORDER and GROUPTYPE appear to be Integers. RESPONSE and MODIFIER appear to be Strings. And MODIFIED appears to be a timestamp. We're making assumptions here but sometimes that's plenty sufficient, and sometimes it isn't. But it is simple, so it has that going for it. There are many situations where you could take this data and just run with it without any further considerations.
29 : FireDAC to JSON (Complex).
Similar to the above, we're exporting a dataset into JSON. In this case, FireDAC has its own method for exporting to JSON that also includes metadata that describes the data types of each column and a host of other things that are likely less interesting but useful if moving between FireDAC controls in different environments.
procedure TMyService.GetJSONData(ParametersOfSomeKind: String):TStream; var clientDB: TFDConnection; qry: TFDQuery; begin Result := TMemoryStream.Create; // Some kind of database connection clientDB := TFDConnection.Create(nil); clientDB.ConnectionDefName := SomeDatabaseConnection; clientDB.Connected := True; // Some kind of query qry.Connection := clientDB; qry.SQL.Text := 'select * from sample;'; qry.Open; // Convert the query to FireDAC's JSON qry.SaveToStream(Result, sfJSON); // Cleanup afterwards qry.Free; clientDB.Connected := False; clientDB.Free; end;
Not much to look at though.
{"FDBS":{"Version":15,"Manager":{"UpdatesRegistry":true,"TableList":[{"class":"Table","Name":"DATAMARK.LUTS","SourceName":"DATAMARK.LUTS","SourceID":1,"TabID":0,"EnforceConstraints":false,"MinimumCapacity":50,"ColumnList":[{"class":"Column","Name":"ID","SourceName":"ID","SourceID":1,"DataType":"Int64","Precision":19,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"ID","SourcePrecision":19},{"class":"Column","Name":"LOOKUP","SourceName":"LOOKUP","SourceID":2,"DataType":"Int32","Precision":10,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OInKey":true,"OriginColName":"LOOKUP","SourcePrecision":10},{"class":"Column","Name":"SORTORDER","SourceName":"SORTORDER","SourceID":3,"DataType":"Int32","Precision":10,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"SORTORDER","SourcePrecision":10},{"class":"Column","Name":"RESPONSE","SourceName":"RESPONSE","SourceID":4,"DataType":"AnsiString","Size":200,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"RESPONSE","SourcePrecision":200,"SourceSize":200},{"class":"Column","Name":"DESCRIPTION","SourceName":"DESCRIPTION","SourceID":5,"DataType":"AnsiString","Size":250,"Searchable":true,"AllowNull":true,"Base":true,"OAllowNull":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"DESCRIPTION","SourcePrecision":250,"SourceSize":250},{"class":"Column","Name":"MODIFIER","SourceName":"MODIFIER","SourceID":6,"DataType":"AnsiString","Size":32,"Searchable":true,"FixedLen":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"MODIFIER","SourcePrecision":32,"SourceSize":32},{"class":"Column","Name":"MODIFIED","SourceName":"MODIFIED","SourceID":7,"DataType":"DateTimeStamp","Searchable":true,"AllowNull":true,"Base":true,"OAllowNull":true,"OInUpdate":true,"OInWhere":true,"OriginColName":"MODIFIED","SourcePrecision":26},{"class":"Column","Name":"GROUPTYPE","SourceName":"GROUPTYPE","SourceID":8,"DataType":"Int32","Precision":10,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OInKey":true,"OriginColName":"GROUPTYPE","SourcePrecision":10}],"ConstraintList":[],"ViewList":[],"RowList":[{"RowID":0,"Original":{"ID":1,"LOOKUP":0,"SORTORDER":0,"RESPONSE":"Administration","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:34","GROUPTYPE":16}},{"RowID":1,"Original":{"ID":2,"LOOKUP":1,"SORTORDER":1,"RESPONSE":"Labour","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:47:52","GROUPTYPE":16}},{"RowID":2,"Original":{"ID":3,"LOOKUP":2,"SORTORDER":2,"RESPONSE":"IT","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:56","GROUPTYPE":16}}]}],"RelationList":[],"UpdatesJournal":{"Changes":[]}}}}If we run it through a formatter, it is a little easier to see what is going on.
{
"FDBS": {
"Version": 15,
"Manager": {
"UpdatesRegistry": true,
"TableList": [
{
"class": "Table",
"Name": "DATAMARK.LUTS",
"SourceName": "DATAMARK.LUTS",
"SourceID": 1,
"TabID": 0,
"EnforceConstraints": false,
"MinimumCapacity": 50,
"ColumnList": [
{
"class": "Column",
"Name": "ID",
"SourceName": "ID",
"SourceID": 1,
"DataType": "Int64",
"Precision": 19,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "ID",
"SourcePrecision": 19
},
{
"class": "Column",
"Name": "LOOKUP",
"SourceName": "LOOKUP",
"SourceID": 2,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OInKey": true,
"OriginColName": "LOOKUP",
"SourcePrecision": 10
},
{
"class": "Column",
"Name": "SORTORDER",
"SourceName": "SORTORDER",
"SourceID": 3,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "SORTORDER",
"SourcePrecision": 10
},
{
"class": "Column",
"Name": "RESPONSE",
"SourceName": "RESPONSE",
"SourceID": 4,
"DataType": "AnsiString",
"Size": 200,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "RESPONSE",
"SourcePrecision": 200,
"SourceSize": 200
},
{
"class": "Column",
"Name": "DESCRIPTION",
"SourceName": "DESCRIPTION",
"SourceID": 5,
"DataType": "AnsiString",
"Size": 250,
"Searchable": true,
"AllowNull": true,
"Base": true,
"OAllowNull": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "DESCRIPTION",
"SourcePrecision": 250,
"SourceSize": 250
},
{
"class": "Column",
"Name": "MODIFIER",
"SourceName": "MODIFIER",
"SourceID": 6,
"DataType": "AnsiString",
"Size": 32,
"Searchable": true,
"FixedLen": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "MODIFIER",
"SourcePrecision": 32,
"SourceSize": 32
},
{
"class": "Column",
"Name": "MODIFIED",
"SourceName": "MODIFIED",
"SourceID": 7,
"DataType": "DateTimeStamp",
"Searchable": true,
"AllowNull": true,
"Base": true,
"OAllowNull": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "MODIFIED",
"SourcePrecision": 26
},
{
"class": "Column",
"Name": "GROUPTYPE",
"SourceName": "GROUPTYPE",
"SourceID": 8,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OInKey": true,
"OriginColName": "GROUPTYPE",
"SourcePrecision": 10
}
],
"ConstraintList": [],
"ViewList": [],
"RowList": [
{
"RowID": 0,
"Original": {
"ID": 1,
"LOOKUP": 0,
"SORTORDER": 0,
"RESPONSE": "Administration",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:34",
"GROUPTYPE": 16
}
},
{
"RowID": 1,
"Original": {
"ID": 2,
"LOOKUP": 1,
"SORTORDER": 1,
"RESPONSE": "Labour",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:47:52",
"GROUPTYPE": 16
}
},
{
"RowID": 2,
"Original": {
"ID": 3,
"LOOKUP": 2,
"SORTORDER": 2,
"RESPONSE": "IT",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:56",
"GROUPTYPE": 16
}
}
]
}
],
"RelationList": [],
"UpdatesJournal": {
"Changes": []
}
}
}
}That's a lot of ground to cover for the same three records but there is absolutely no guesswork when it comes to the data types we're using. Which is what we're after here. Curiously, you can see that there is a DESCRIPTION field in the database, but presumably it didn't have any data so it was excluded from the subsequent records. Just the kind of optimization that happens behind the scenes that will drive a person mad!
30 : JSON to TDataset (Simple).
Using the JSON from the simple variation, everything we've covered applies in terms of looking up data or sorting or whatever else might be needed. Often, one of the goals is to get the JSON data into a local TDataset where we can happily go back to using all of our Delphi tools. In TMS WEB Core, one of the ways to do this is using the TXDataWebDataSet component. You can drop one of these onto a form, and then double-click on it to bring up the familar design-time Fields editor. When you're ready to load data into the dataset, you can call SetJSONData with a JSON Array to load the Dataset. There's also the familiar TWebDataSet and TWebGrid. At design-time, it might look like this.
To get the data into the dataset, there's a SetJSONData() function. There are some caveats here. First, the data has to be in the format that corresponds to the column definitions or it will be excluded. DateTime columns need to be in a specific format or they'll be excluded. Things like that. So a bit tricky to work through the first couple of times. There's also not much in the way of feedback when it gets data it doesn't know what to do with. Here's what it looks like.
procedure TForm1.WebButton1Click(Sender: TObject);
var
WC_Object: TJSObject;
const
SampleObjectData = '[{"ID":1,"LOOKUP":0,"SORTORDER":0,"RESPONSE":"Administration","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:34","GROUPTYPE":16},'+
'{"ID":2,"LOOKUP":1,"SORTORDER":1,"RESPONSE":"Labour","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:47:52","GROUPTYPE":16},'+
'{"ID":3,"LOOKUP":2,"SORTORDER":2,"RESPONSE":"IT","MODIFIER":"ASIMARD","MODIFIED":"2021-11-17T19:42:56","GROUPTYPE":16}]';
begin
WC_Object := TJSJSON.parseObject(SampleObjectData);
XDataWebDataSet1.SetJSONData(WC_Object);
XdataWebDataSet1.Open;
end;
Note that we've done all the hard work in the design-time environment - figuring out what columns we want, how wide they need to be and so on. If things go according to plan, you're ready to go with a grid that might look something like this.
And you're off and running!
31 : JSON to TDataset (Complex).
And at long last we come to the problem that I first encountered when using TMS WEB Core. The basic idea is to use the TMS WEB Core application as a client to, in my case, an XData server. The server sends down JSON data from any of hundreds of different queries. The same thing could be done for any number of other data sources, like weather data and so on, that have nothing to do with XData. The JSON that arrives then needs to be handled on the client.
Initially, the intent was to automatically create a local TDataSet with the data, ready to go. However, without knowing all the field defintions ahead of time, and with not wanting to create them all in design-time, there was no way to load the data using the SetJSONData() call. And at this stage, the JSON data coming in was more of a coummincations mechanism. The fields are defined as a result of the original query, and they'll need to be known when the final UI is presented to the user, but inbetween we really don't care what is going on - JSON is just the name given to the data stream - its contents don't really matter at certain stages.
So what to do? Well, as we've seen, the FireDAC version of the JSON has everything we'd ever need to know about data types. The trick then is to parse that version of the JSON to get the column names, data types, widths and so on so that we can dynamically build a TDataSet at runtime without having any design-time components of any kind. Tricky business though as we have to heavily parse the JSON data to get what we need. Intially I managed to find examples in the TMS Support Center that got me going, but it was a bit of a mystery as it was all WC-style JSON - so not the TJSONObject PAS variant I was accustomed to, nor the ubiquitous JS variant. But it worked. Let's figure out what it actually does, now that we know a thing or two about JSON that I didn't when I started.
function GetQueryData(Endpoint: String; Dataset: TXDataWebDataSet):Integer;
var
Conn: TXDataWebConnection;
Client: TXDataWebClient;
Response: TXDataClientResponse;
JFDBS: TJSObject;
JManager: TJSObject;
JTableList: TJSObject;
JColumnList: TJSArray;
JRowList: TJSArray;
StringFields: Array of TStringField;
IntegerFields: Array of TIntegerField;
// Likely need a few more datatypes here
i: Integer;
begin
// Value to indicate the request was unsuccessful
Result := -1;
// Setup connection to XData Server
Conn := TXDataWebConnection.Create(nil);
Conn.URL := DM1.CarnivalCoreServer; // could also be a parameter
Conn.OnRequest := PopulateJWT; // See below
Client := TXDataWebClient.Create(nil);
Client.Connection := Conn;
await(Conn.OpenAsync);
// Make the Request
// Likely to have another version of function that includes more endpoint parameters
try
Response := await(Client.RawInvokeAsync(Endpoint, ['FireDAC']));
except
on Error: Exception do
begin
Client.Free;
Conn.Free;
console.log('...something is amiss...');
exit;
end;
end;
// Process the FireDAC-specific JSON that was returned
JFDBS := TJSObject(TJSObject(TJSJson.Parse(string(Response.Result)))['FDBS']);
JManager := TJSObject(JFDBS['Manager']);
JTableList := TJSObject(TJSArray(JManager['TableList'])[0]);
JColumnList := TJSArray(JTableList['ColumnList']);
JRowList := TJSArray(JTableList['RowList']);
// Don't really want 'Original' in field names, so let's remove it from JSON first
// Probably a better one-liner, but this seems to work
for i := 0 to JRowList.Length - 1 do
JRowList.Elements[i] := TJSObject(JRowList.Elements[i])['Original'];
// We're assuming Dataset parameter is newly created and empty.
// First, add all the fields from JSON
// NOTE: Very likely more datatypes need to be added here
for i := 0 to JColumnList.Length-1 do
begin
if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'AnsiString') then
begin
// NOTE: Different datatypes may need different values set (eg: Size for strings)
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(Dataset);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := Integer(TJSObject(JColumnList.Elements[i])['Size']);
StringFields[Length(StringFields)-1].Dataset := Dataset;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Int32') then
begin
SetLength(IntegerFields, Length(IntegerFields) + 1);
IntegerFields[Length(IntegerFields)-1] := TIntegerField.Create(Dataset);
IntegerFields[Length(IntegerFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
IntegerFields[Length(IntegerFields)-1].Dataset := Dataset;
end
else
begin
console.log('ERROR: Field ['+String(TJSObject(JColumnList.Elements[i])['Name'])+'] has an unexpected datatype ['+String(TJSObject(JColumnList.Elements[i])['DataType'])+']');
end;
end;
// Add the data and return the dataset as opened
Dataset.SetJSONData(JRowList);
Dataset.Open;
// Just for fun
Result := Dataset.RecordCount;
// No dataset stuff to free as the dataset was created by the caller and
// all the fields created were created with that dataset as the parent
Client.Free;
Conn.Free;
end;
For me, the mysterious bit was always this:
// Process the FireDAC-specific JSON that was returned
JFDBS := TJSObject(TJSObject(TJSJson.Parse(string(Response.Result)))['FDBS']);
JManager := TJSObject(JFDBS['Manager']);
JTableList := TJSObject(TJSArray(JManager['TableList'])[0]);
JColumnList := TJSArray(JTableList['ColumnList']);
JRowList := TJSArray(JTableList['RowList']);
// Don't really want 'Original' in field names, so let's remove it from JSON first
// Probably a better one-liner, but this seems to work
for i := 0 to JRowList.Length - 1 do
JRowList.Elements[i] := TJSObject(JRowList.Elements[i])['Original'];
If we push our sample dat through this, we can more clearly see what is going on.
JFDBS := TJSObject(TJSObject(TJSJson.Parse(string(Response.Result)))['FDBS']);
This is loading the JSON initially (Response.Result is coming directly from the server) and removing the top-most outer wrapper, 'FDBS'.
JManager := TJSObject(JFDBS['Manager']);
This removes another outer wrapper, 'Manager'.
JTableList := TJSObject(TJSArray(JManager['TableList'])[0]);
This removes another outer wrapper, 'TableList' but still has quite a bit of stuff being carried along.
{
"class": "Table",
"Name": "DATAMARK.LUTS",
"SourceName": "DATAMARK.LUTS",
"SourceID": 1,
"TabID": 0,
"EnforceConstraints": false,
"MinimumCapacity": 50,
"ColumnList": [
{
"class": "Column",
"Name": "ID",
"SourceName": "ID",
"SourceID": 1,
"DataType": "Int64",
"Precision": 19,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "ID",
"SourcePrecision": 19
},
{
"class": "Column",
"Name": "LOOKUP",
"SourceName": "LOOKUP",
"SourceID": 2,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OInKey": true,
"OriginColName": "LOOKUP",
"SourcePrecision": 10
},
{
"class": "Column",
"Name": "SORTORDER",
"SourceName": "SORTORDER",
"SourceID": 3,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "SORTORDER",
"SourcePrecision": 10
},
{
"class": "Column",
"Name": "RESPONSE",
"SourceName": "RESPONSE",
"SourceID": 4,
"DataType": "AnsiString",
"Size": 200,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "RESPONSE",
"SourcePrecision": 200,
"SourceSize": 200
},
{
"class": "Column",
"Name": "DESCRIPTION",
"SourceName": "DESCRIPTION",
"SourceID": 5,
"DataType": "AnsiString",
"Size": 250,
"Searchable": true,
"AllowNull": true,
"Base": true,
"OAllowNull": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "DESCRIPTION",
"SourcePrecision": 250,
"SourceSize": 250
},
{
"class": "Column",
"Name": "MODIFIER",
"SourceName": "MODIFIER",
"SourceID": 6,
"DataType": "AnsiString",
"Size": 32,
"Searchable": true,
"FixedLen": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "MODIFIER",
"SourcePrecision": 32,
"SourceSize": 32
},
{
"class": "Column",
"Name": "MODIFIED",
"SourceName": "MODIFIED",
"SourceID": 7,
"DataType": "DateTimeStamp",
"Searchable": true,
"AllowNull": true,
"Base": true,
"OAllowNull": true,
"OInUpdate": true,
"OInWhere": true,
"OriginColName": "MODIFIED",
"SourcePrecision": 26
},
{
"class": "Column",
"Name": "GROUPTYPE",
"SourceName": "GROUPTYPE",
"SourceID": 8,
"DataType": "Int32",
"Precision": 10,
"Searchable": true,
"Base": true,
"OInUpdate": true,
"OInWhere": true,
"OInKey": true,
"OriginColName": "GROUPTYPE",
"SourcePrecision": 10
}
],
"ConstraintList": [],
"ViewList": [],
"RowList": [
{
"RowID": 0,
"Original": {
"ID": 1,
"LOOKUP": 0,
"SORTORDER": 0,
"RESPONSE": "Administration",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:34",
"GROUPTYPE": 16
}
},
{
"RowID": 1,
"Original": {
"ID": 2,
"LOOKUP": 1,
"SORTORDER": 1,
"RESPONSE": "Labour",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:47:52",
"GROUPTYPE": 16
}
},
{
"RowID": 2,
"Original": {
"ID": 3,
"LOOKUP": 2,
"SORTORDER": 2,
"RESPONSE": "IT",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:56",
"GROUPTYPE": 16
}
}
]
}Lots of things in there we don't really need. We'll get to that in a moment. Now we're down to column definitions.
JColumnList := TJSArray(JTableList['ColumnList']);
This has just the 'ColumnList' from above, but still lots of extras.
JRowList := TJSArray(JTableList['RowList']);
Now we're dealing with just the 'RowList' section, which is what we need for SetJSONData to do its thing. This looks like the following. Similar to the simple JSON, which is of course what we're after in terms of the data.
[
{
"RowID": 0,
"Original": {
"ID": 1,
"LOOKUP": 0,
"SORTORDER": 0,
"RESPONSE": "Administration",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:34",
"GROUPTYPE": 16
}
},
{
"RowID": 1,
"Original": {
"ID": 2,
"LOOKUP": 1,
"SORTORDER": 1,
"RESPONSE": "Labour",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:47:52",
"GROUPTYPE": 16
}
},
{
"RowID": 2,
"Original": {
"ID": 3,
"LOOKUP": 2,
"SORTORDER": 2,
"RESPONSE": "IT",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:56",
"GROUPTYPE": 16
}
}
]
When this is loaded into a TDataset though, that little 'Original' Object grouping is a bit annoying. This gets rid of it by basically replacing the 'Original' Object with its contents.
// Don't really want 'Original' in field names, so let's remove it from JSON first // Probably a better one-liner, but this seems to work for i := 0 to JRowList.Length - 1 do JRowList.Elements[i] := TJSObject(JRowList.Elements[i])['Original'];
Which results in this, the final form that is imported via SetJSONData:
[
{
"ID": 1,
"LOOKUP": 0,
"SORTORDER": 0,
"RESPONSE": "Administration",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:34",
"GROUPTYPE": 16
},
{
"ID": 2,
"LOOKUP": 1,
"SORTORDER": 1,
"RESPONSE": "Labour",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:47:52",
"GROUPTYPE": 16
},
{
"ID": 3,
"LOOKUP": 2,
"SORTORDER": 2,
"RESPONSE": "IT",
"MODIFIER": "ASIMARD",
"MODIFIED": "2021-11-17T19:42:56",
"GROUPTYPE": 16
}
]
But before we can load this into the TDataset, we need to create all the fields. This is a bit of a chore, depending on how many different kinds of fields you're dealing with, but it is generally a one-time effort to sort through and then you don't have to think about it anymore.
// We're assuming Dataset parameter is newly created and empty.
// First, add all the fields from JSON
// NOTE: Very likely more datatypes need to be added here
for i := 0 to JColumnList.Length-1 do
begin
if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'AnsiString') then
begin
// NOTE: Different datatypes may need different values set (eg: Size for strings)
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(Dataset);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := Integer(TJSObject(JColumnList.Elements[i])['Size']);
StringFields[Length(StringFields)-1].Dataset := Dataset;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Int32') then
begin
SetLength(IntegerFields, Length(IntegerFields) + 1);
IntegerFields[Length(IntegerFields)-1] := TIntegerField.Create(Dataset);
IntegerFields[Length(IntegerFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
IntegerFields[Length(IntegerFields)-1].Dataset := Dataset;
end
else
begin
console.log('ERROR: Field ['+String(TJSObject(JColumnList.Elements[i])['Name'])+'] has an unexpected datatype ['+String(TJSObject(JColumnList.Elements[i])['DataType'])+']');
end;
end;
It is now not so mysterious to see what is going on, with the column data types being pulled from JColumnList and, where necessary, extra information about the data types as well. Since this was written originally, it has been expanded to support a handful more data types that I use regularly, but keeping an eye out for the "unexpected data type" message in console.log is a good idea.
Conclusion.
Well. That about covers it. I think any follow-up questions that are of a technical nature, suggested improvements to the individual blocks of code, and so on should probably be handled via posts in the TMS Support Center where we have a little more control over formatting and threaded responses and so on. If this proves to be a popular topic, then creating a GitHub repository for it may also make some sense.
And I have no doubt at all that there are improvements to be found in many of these examples. More than half of the sections were written just for this article, particularly the WC variants. As I'm relatively new to the WC approach, it is entirely likely I've overlooked, or plainly misunderstood, some of the options available. This was also written with TMS WEB Core 1.9.8.3. No doubt future versions of TMS WEB Core, and the underlying pas2js project, will introduce changes and refinements that will make improvements possible that might not be possible today. TJSONObject is missing a few methods, for example. JavaScript itself has also evolved and will continue to evolve, so improvements there are just as likely, over time.
As I mentioned at the outset, the JS code is pure JavaScript and the WC and PAS code are pure Delphi. There are at least a handful of places where relaxing this restriction could simplify or otherwise improve and generally speed-up some bits of the code. There's not really any reason to keep a strict separation between them other than for our purposes of demonstrating that there are multiple ways of doing things, starting with a minimum of three. Best to use whatever variation or combination of variations that best solves your problem, regardless of where the boundaries of one variation ends and another begins. This is of course the whole point of this article - to help provide you with the tools to make the most amazing TMS WEB Core applications you possibly can!
Andrew Simard.
Masiha Zemarai
Bookmarks:
This blog post has received 6 comments.
2. Friday, May 6, 2022 at 1:34:13 PM
Hi Andrew Great article on JSON.
Just what I needed to start to better understand this subject.
1. Do you know if the example code is going to work on older versions of Delphi. ( I am on Delphi Berlin 10.1.2 Ent/Arch) ?
(I suspect that its not always possible to support lower versions ; as the libraries for delphi etc. are always being updated and improved.)
If that is the case are there any work arounds to get this code to work and explore its possibilities?
2. Any plan eventually , for a full source project download of these snippets. Always useful to have.
Best regards
Kamran
3. Friday, May 6, 2022 at 4:10:24 PM
Big help. Thanks you
Voirol Jean-No
4. Friday, May 6, 2022 at 7:54:26 PM
Thanks for the feedback! Kamran, I think JSON support was first added to Delphi (TJSONObject) back around Delphi 2009. There''s nothing particularly fancy going on in the examples here, so it should in theory work fine in Delphi Berlin. I don''t know how JSON has evolved since that time though. Fortunately, you should just be able to copy & paste the code and see if it works. If you''re not running the code in a TMS WEB Core project, just remove the WC* and JS* referencing code. I will set up a GitHub repository with these examples and also separated-out versions of these examples and will post a link here when that is ready.
Simard Andrew
5. Friday, May 6, 2022 at 8:47:45 PM
Is there a common JSON operation that would be useful to TMS WEB Core projects that I didn''t cover here?
Simard Andrew
6. Saturday, May 7, 2022 at 4:31:01 AM
The GitHub repository can be found at: https://github.com/500Foods/TMS-WEB-Core-JSON-PrimerSimard Andrew
All Blog Posts | Next Post | Previous Post
These chapters on JSON handling is a solid reference. Thanks.
Borbor Mehmet Emin