Working with small JSON data sets in memory using Smart Mobile Studio

TW3DataSet

TW3Dataset is simply a small in-memory datastore that allowed you to save small data. You can save data to a normal string or stream. It also can be used as an intermediate format, you can both push data to a server (small number of records) as well as retrieve data from a server.  If you want to persist some JSON information in the browser. Depending on user interaction with the application, you want to store 5-6 different JSON object into memory, the TW3DataSet is an option. This is great to create prototyping applications.

Note: storing large data in memory has a couple of disadvantages:

• non-scalable — when you decide to use more processes, each process will need to make same api request;
• fragile — if your process crashes you will lose the data.

Also working with large amount of data can block process for longer time than you would like.Solution: I'll use external storage! It can be MongoDB or RDBMS; - update data in separate process, triggered with cron; - don't drop the whole database: there is a chance that someone will make a request right after that (if your storage doesn't support transactions, of course), update records. Working with large JSON datasets can be a pain, particularly when they are too large to fit into memory.  

---

Example JSON

The following is an example of the JSON for the Products table. The JSON returned by the Delphi web server have this format: 

[{
 "productid" : "9V-BATTERY-12PK",
 "description" : "12-pack of 9-volt batteries",
 "listprice" : 20,
 "shipping" : 2
 }, {
 "productid" : "9V-BATTERY-4PK",
 "description" : "4-pack of 9-volt batteries",
 "listprice" : 4.5,
 "shipping" : 1.5
 }
]

We can fill a combobox using TW3Dataset, like in this example:

Define/Create the dataset

Take a closer look at the above JSON format returned by our server (column and row data).  Before we create a dataset, we have to define what the table looks like, we have to define the field-definition property.
Ensure to add the System.Dataset unit;

  ProductsDS := TDataset.Create;
  
  ProductsDS.FieldDefs.Add('productid',ftString);
  ProductsDS.fieldDefs.Add('description',ftString);
  ProductsDS.fieldDefs.Add('listprice',ftFloat);
  ProductsDS.fieldDefs.add('shipping',ftFloat);
  ProductsDS.CreateDataset;

 DataSet Columns: When we define manually a dataset and the SaveToString method is called from the application.
The following JSON format is returned by our application:

{
 "dhMagic" : 51966,
 "dhCount" : 0,
 "dhFieldDefs" : {
  "ddMagic" : 3401235116,
  "ddDefs" : [{
    "fdName" : "productid",
    "fdDatatype" : 4
   }, {
    "fdName" : "description",
    "fdDatatype" : 4
   }, {
    "fdName" : "listprice",
    "fdDatatype" : 3
   }, {
    "fdName" : "shipping",
    "fdDatatype" : 3
   }
  ]
 },
 "dhData" : []
}

Field Types: Observe the following details the various column types and how they should be specified:

Field Type	Code	Description
ftUnknown	0	Unknown type - not specified collumn type
ftBoolean	1	Boolean
ftInteger	2	Integer
ftFloat	3	Float
ftString	4	String
ftDateTime	5	DateTime/Float
ftAutoInc	6	generated field
ftGUID	7	generated field

Adding records

This task is more or less identical to how you would do it under Delphi.
You have both append and insert operations. Let's use Append method for this example:

  procedure fillProductsDS;
  begin
    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := '9V-BATTERY-12PK';
    ProductsDS.Fields.FieldByName('description').AsString := '12-pack of 9-volt batteries';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 20;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 2;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := '9V-BATTERY-4PK';
    ProductsDS.Fields.FieldByName('description').AsString := '4-pack of 9-volt batteries';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 4.5;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 1.5;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'CALCULATOR-BUSINESS';
    ProductsDS.Fields.FieldByName('description').AsString := 'Business calculator';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 10;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 1;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'CASH-REGISTER';
    ProductsDS.Fields.FieldByName('description').AsString := 'Cash register with thermal printer';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 170;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 10;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'FLASH-USB-16GB';
    ProductsDS.Fields.FieldByName('description').AsString := '16GB USB flash drive';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 15;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 0.5;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'FLASH-USB-32GB';
    ProductsDS.Fields.FieldByName('description').AsString := '32GB USB flash drive';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 25;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 0.5;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'FLASH-USB-8GB';
    ProductsDS.Fields.FieldByName('description').AsString := '8GB USB flash drive';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 10;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 0.5;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'LABEL-MAKER';
    ProductsDS.Fields.FieldByName('description').AsString := 'Label maker - plastic labels';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 35;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 2;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'PEN-BP-12PK';
    ProductsDS.Fields.FieldByName('description').AsString := '12-pack of ballpoint pens';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 12;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 0.6;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'PHONE-HEADSET';
    ProductsDS.Fields.FieldByName('description').AsString := 'Hands-free phone headset';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 15;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 2;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'PHONE-SYSTEM-4HS';
    ProductsDS.Fields.FieldByName('description').AsString := '4-handset phone system with main base';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 120;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 4;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'PROJECTOR-HD';
    ProductsDS.Fields.FieldByName('description').AsString := '1080p HD Projector';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 850;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 56;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'SCANNER-SF';
    ProductsDS.Fields.FieldByName('description').AsString := 'Sheet-feed paper scanner';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 150;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 7;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'SHREDDER-SF-CC';
    ProductsDS.Fields.FieldByName('description').AsString := 'Sheet-feed, cross-cut shredder with bin';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 8;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 10;
    ProductsDS.Post;

    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := 'USB-CARD-READER';
    ProductsDS.Fields.FieldByName('description').AsString := 'USB magnetic strip card reader';
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := 25;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := 2;
    ProductsDS.Post;
  end;

  { fill Customer dataset }  
  fillProductsDS;

 

Dataset row JSON structure

DataSet Rows: Observe the following details on the column "dhData", when the SaveToString method was called. The field "dhData" was populated, of course this could be requested from the web server using HTTP GET request.

{
 "dhMagic" : 51966,
 "dhCount" : 15,
 "dhFieldDefs" : {
  "ddMagic" : 3401235116,
  "ddDefs" : [{
    "fdName" : "productid",
    "fdDatatype" : 4
   }, {
    "fdName" : "description",
    "fdDatatype" : 4
   }, {
    "fdName" : "listprice",
    "fdDatatype" : 3
   }, {
    "fdName" : "shipping",
    "fdDatatype" : 3
   }
  ]
 },
 "dhData" : [{
   "productid" : "9V-BATTERY-12PK",
   "description" : "12-pack of 9-volt batteries",
   "listprice" : 20,
   "shipping" : 2
  }, {
   "productid" : "9V-BATTERY-4PK",
   "description" : "4-pack of 9-volt batteries",
   "listprice" : 4.5,
   "shipping" : 1.5
  }, {
   "productid" : "CALCULATOR-BUSINESS",
   "description" : "Business calculator",
   "listprice" : 10,
   "shipping" : 1
  }, {
   "productid" : "CASH-REGISTER",
   "description" : "Cash register with thermal printer",
   "listprice" : 170,
   "shipping" : 10
  }, {
   "productid" : "FLASH-USB-16GB",
   "description" : "16GB USB flash drive",
   "listprice" : 15,
   "shipping" : 0.5
  }, {
   "productid" : "FLASH-USB-32GB",
   "description" : "32GB USB flash drive",
   "listprice" : 25,
   "shipping" : 0.5
  }, {
   "productid" : "FLASH-USB-8GB",
   "description" : "8GB USB flash drive",
   "listprice" : 10,
   "shipping" : 0.5
  }, {
   "productid" : "LABEL-MAKER",
   "description" : "Label maker - plastic labels",
   "listprice" : 35,
   "shipping" : 2
  }, {
   "productid" : "PEN-BP-12PK",
   "description" : "12-pack of ballpoint pens",
   "listprice" : 12,
   "shipping" : 0.6
  }, {
   "productid" : "PHONE-HEADSET",
   "description" : "Hands-free phone headset",
   "listprice" : 15,
   "shipping" : 2
  }, {
   "productid" : "PHONE-SYSTEM-4HS",
   "description" : "4-handset phone system with main base",
   "listprice" : 120,
   "shipping" : 4
  }, {
   "productid" : "PROJECTOR-HD",
   "description" : "1080p HD Projector",
   "listprice" : 850,
   "shipping" : 56
  }, {
   "productid" : "SCANNER-SF",
   "description" : "Sheet-feed paper scanner",
   "listprice" : 150,
   "shipping" : 7
  }, {
   "productid" : "SHREDDER-SF-CC",
   "description" : "Sheet-feed, cross-cut shredder with bin",
   "listprice" : 8,
   "shipping" : 10
  }, {
   "productid" : "USB-CARD-READER",
   "description" : "USB magnetic strip card reader",
   "listprice" : 25,
   "shipping" : 2
  }
 ]
}

 

Loading/Saving records

TW3Dataset allows you to save your data to a normal string or a stream.

function    SaveToString:String;
Procedure   LoadFromString(Const aText:String);
 
Procedure   SaveToStream(const Stream:TStream);virtual;
Procedure   LoadFromStream(const Stream:TStream);virtual;

So we can store a dataset locally with SaveToString/SaveToStream methods
and retrieve the data using LoadFromString/LoadFromStream methods.
Example:

ProductsDS := TDataset.Create;
ProductsDS.LoadFromString( jsonData );
ProductsDS.CreateDataset;

This will load locally a dataset. We can use it as intermediate bridge, we can insert, append, delete data locally, and push the data to a server. In this example, let's just list two Products fields:

ProductsDS.Active := true;

procedure ListProductsDS;
begin
ProductsDS.first;
while not ProductsDS.EOF do
begin
  var id  := ProductsDS.fields.fieldbyname('productid').asString;
  var price := ProductsDS.fields.fieldbyname('listprice').asString;
  writeln(id + ' ' + price);
  ProductsDS.Next;
end;
end;

{ List ProductsDS }
ListProductsDS;

/*
9V-BATTERY-12PK   20
9V-BATTERY-4PK   4.5
CALCULATOR-BUSINESS     10
CASH-REGISTER   170
FLASH-USB-16GB   15
FLASH-USB-32GB   25
FLASH-USB-8GB   10
LABEL-MAKER   35
PEN-BP-12PK   12
PHONE-HEADSET   15
PHONE-SYSTEM-4HS  120
PROJECTOR-HD   850
SCANNER-SF   150
SHREDDER-SF-CC   8
USB-CARD-READER  25
*/

 

Load remote data

Another nice feature is that you can define your dataset locally and load data remotely. Let's suppose our Delphi REST server is returning this:

{"rows" : [{
 "productid" : "9V-BATTERY-12PK",
 "description" : "12-pack of 9-volt batteries",
 "listprice" : 20,
 "shipping" : 2
}, {
 "productid" : "9V-BATTERY-4PK",
 "description" : "4-pack of 9-volt batteries",
 "listprice" : 4.5,
 "shipping" : 1.5
}, {
 "productid" : "CALCULATOR-BUSINESS",
 "description" : "Business calculator",
 "listprice" : 10,
 "shipping" : 1
}, {
 "productid" : "CASH-REGISTER",
 "description" : "Cash register with thermal printer",
 "listprice" : 170,
 "shipping" : 10
}, {
 "productid" : "FLASH-USB-16GB",
 "description" : "16GB USB flash drive",
 "listprice" : 15,
 "shipping" : 0.5
}, {
 "productid" : "FLASH-USB-32GB",
 "description" : "32GB USB flash drive",
 "listprice" : 25,
 "shipping" : 0.5
}, {
 "productid" : "FLASH-USB-8GB",
 "description" : "8GB USB flash drive",
 "listprice" : 10,
 "shipping" : 0.5
}, {
 "productid" : "LABEL-MAKER",
 "description" : "Label maker - plastic labels",
 "listprice" : 35,
 "shipping" : 2
}, {
 "productid" : "PEN-BP-12PK",
 "description" : "12-pack of ballpoint pens",
 "listprice" : 12,
 "shipping" : 0.6
}, {
 "productid" : "PHONE-HEADSET",
 "description" : "Hands-free phone headset",
 "listprice" : 15,
 "shipping" : 2
}, {
 "productid" : "PHONE-SYSTEM-4HS",
 "description" : "4-handset phone system with main base",
 "listprice" : 120,
 "shipping" : 4
}, {
 "productid" : "PROJECTOR-HD",
 "description" : "1080p HD Projector",
 "listprice" : 850,
 "shipping" : 56
}, {
 "productid" : "SCANNER-SF",
 "description" : "Sheet-feed paper scanner",
 "listprice" : 150,
 "shipping" : 7
}, {
 "productid" : "SHREDDER-SF-CC",
 "description" : "Sheet-feed, cross-cut shredder with bin",
 "listprice" : 8,
 "shipping" : 10
}, {
 "productid" : "USB-CARD-READER",
 "description" : "USB magnetic strip card reader",
 "listprice" : 25,
 "shipping" : 2
}
]}

The dataset rows must be loaded at run-time, and the data rows can come from the web server application in JSON format. When the rows are loaded, you can specify that the rows be appended to the existing rows in the dataset, or completely replace the current rows in the dataset.
The above example of the JSON for the Products dataset returned by a server.
We can fill the predefined dataset remotely, from a JSON string, for instance:

procedure fillProductsDS(strJSON: string);
begin
  var resultSet := JSON.Parse(strJSON).rows;
  for i in resultSet do
  begin
    ProductsDS.Append;
    ProductsDS.Fields.FieldByName('productid').AsString   := resultSet[i].productid;
    ProductsDS.Fields.FieldByName('description').AsString := resultSet[i].description;
    ProductsDS.Fields.FieldByName('listprice').AsFloat    := resultSet[i].listprice;
    ProductsDS.Fields.FieldByName('shipping').AsFloat     := resultSet[i].shipping;
    ProductsDS.Post;
  end;
end;

fillProductsDS(dataJSON);

Tags: TW3Dataset; in-memory datastore; JSON store.