Encode JSON to a FireDAC Memory Table without REST Request

At least twice this month I’ve been asked the question “Can I pass JSON to the TRESTDatasetAdapter component without binding it to a request?” and the short answer is no, you can’t. So what is the problem we’re trying to solve, and how can we go about it?

The Problem

The TRESTDatasetAdapter takes JSON data returned from a REST service an inserts it into a data set, usually an in-memory data set component. I imagine the main use for this is to display that data using the Live Bindings feature of Delphi / C++ Builder. The problem is that the JSON data must be returned from a REST service, and it must be well formatted as a straight array of JSON data.

Many REST services do not stick to good practice. Instead, they may return nested JSON data, or a container object around the data array. This kind of data cannot be handled by the TRESTDatasetAdapter component.

The Solution

[ See comments for bugs / updates ]

The solution, as indicated by the question I’m addressing, is to somehow go straight from a JSON string to the data set. This would allow you to make the request, pre-process the JSON data to be compatible, and then feed it into the data set for display. Unfortunately, the TRESTDatasetAdapter does not support this feature.

I solved this issue by creating my own custom equivalent component to TRESTDatasetAdapter which I named TJSONDatasetAdapter.
Here’s the source:

unit jsonadapter;

interface
uses
  System.SysUtils,
  System.Classes,
  System.Json,
  Data.DB;

type
  EDataTypeCoersion = class(Exception); //- Can't alter data-type for existing field definition.
  EDataTypeUnsupported = class(Exception); //- Can't support data-type from json data. (objects and arrays)
  EUnknownDataType = class(Exception); //- Unable to determine data type from json data.

type
  TJSONDatasetAdapter = class(TComponent)
  private
    fJSON: TStrings;
    fDatasetRef: TDataset;
    procedure SetDatasetRef(const Value: TDataset);
    procedure setJSON(const Value: TStrings);
    procedure SetFieldDefs(a: TJSONArray);
    procedure InsertData(a: TJSONArray);
  public
    constructor Create( aOwner: TComponent ); override;
    destructor Destroy; override;
  public
    procedure UpdateDataset;
  published
    property JSON: TStrings read fJSON write setJSON;
    property Dataset: TDataset read fDatasetRef write SetDatasetRef;
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('REST Client', [TJSONDatasetAdapter]);
end;

{ TJSONDatasetAdapter }

constructor TJSONDatasetAdapter.Create(aOwner: TComponent);
begin
  inherited Create( aOwner );
  fJSON := TStringList.Create;
  fJSON.Text := '[]';
  fDatasetRef := nil;
end;

destructor TJSONDatasetAdapter.Destroy;
begin
  fJSON.DisposeOf;
  fDatasetRef := nil;
  inherited Destroy;
end;

procedure TJSONDatasetAdapter.SetDatasetRef(const Value: TDataset);
begin
  fDatasetRef := Value;
  UpdateDataset;
end;

procedure TJSONDatasetAdapter.setJSON(const Value: TStrings);
begin
  if not assigned(value) then begin
    fJSON.Clear;
    exit;
  end;
  fJSON.Assign(Value);
  UpdateDataset;
end;

procedure TJSONDatasetAdapter.SetFieldDefs( a: TJSONArray );
var
  o: TJSONObject;
  e: TJSONPairEnumerator;
  p: TJSONPair;
  v: TJSONValue;
  n: string;
  idx: uint32;
  FieldDef: TFieldDef;
begin
  if a.Count<1 then begin
    exit;
  end;
  //- Loop through data to determine data-types.
  for idx := 0 to pred(a.Count) do begin
    v := a.Items[idx];
    if not (v is TJSONObject) then begin
      exit;
    end;
    o := v as TJSONObject;
    try
      e := o.GetEnumerator;
      if not e.MoveNext then begin
        exit;
      end;
      repeat
        p := e.GetCurrent;
        if not assigned(p) then continue;
        //- Get the name of the field, and ensure we have a field def.
        n := Lowercase(Trim(p.JsonString.ToString));
        n := StringReplace(n,'"','',[rfReplaceAll]);
        FieldDef := nil;
        if fDatasetRef.FieldDefs.IndexOf(n)>=0 then begin
          FieldDef := fDatasetRef.FieldDefs.Find(n);
        end;
        if not assigned(FieldDef) then begin
          FieldDef := fDatasetRef.FieldDefs.AddFieldDef;
          FieldDef.Name := n;
        end;
        //- Determine the type of field.
        v := p.JsonValue;
        if v is TJSONString then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftString;
          end;
        end else if v is TJSONNumber then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftFloat;
          end else if (FieldDef.DataType <> TFieldType.ftFloat) then begin
            raise EDataTypeCoersion.Create('');
          end;
        end else if v is TJSONBool then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftBoolean;
          end else if (FieldDef.DataType<>ftBoolean) then begin
            raise EDataTypeCoersion.Create('');
          end;
        end else if v is TJSONNull then begin
          //- Do nothing, another record may indicate data type.
        end else if v is TJSONObject then begin
          raise EDataTypeUnsupported.Create('');
        end else if v is TJSONArray then begin
          raise EDataTypeUnsupported.Create('');
        end;
      until not e.MoveNext;
    finally
      o := nil;
    end;
  end;
 //- Ensure that all field defs have known data types.
 if fDatasetRef.FieldDefs.Count<1 then begin
   exit;
 end;
 for idx := 0 to pred(fDatasetRef.FieldDefs.Count) do begin
   if fDatasetRef.FieldDefs[idx].DataType=TFieldType.ftUnknown then begin
     raise EUnknownDataType.Create('field: '+fDatasetRef.FieldDefs[idx].Name);
   end;
 end;
end;

procedure TJSONDatasetAdapter.InsertData( a: TJSONArray );
var
  idx: uint32;
  idy: uint32;
  v: TJSONValue;
  o: TJSONObject;
  FieldName: string;
begin
  if fDatasetRef.FieldDefs.Count<1 then begin
    exit;
  end;
  if a.Count<1 then begin
    exit;
  end;
  for idx := 0 to pred(a.Count) do begin
    v := a.Items[idx];
    if not (v is TJSONObject) then continue; //[ Exception here? ]
    o := v as TJSONObject;
    fDatasetRef.Insert;
    for idy := 0 to pred(fDatasetRef.FieldDefs.Count) do begin
      FieldName := fDatasetRef.FieldDefs[idy].Name;
      v := o.GetValue(FieldName);
      if assigned(v) then begin
        if v is TJSONString then begin
          fDatasetRef.FieldByName(FieldName).AsString := TJSONString(v).Value;
        end else begin
          fDatasetRef.FieldByName(FieldName).AsString := v.ToJSON;
        end;
      end;
    end;
    fDatasetRef.Post;
  end;
end;

procedure TJSONDatasetAdapter.UpdateDataset;
var
  o: TJSONObject;
  a: TJSONArray;
  v: TJSONValue;
begin
  if not assigned(fDatasetRef) then begin
    exit;
  end;
  fDatasetRef.Active := False;
  fDatasetRef.FieldDefs.Clear;
  try
    o := TJSONObject.ParseJSONValue(TEncoding.ASCII.GetBytes('{ "data": '+fJSON.Text+'}'),0) as TJSONObject;
  except
    on E: Exception do begin
     exit;
    end;
  end;
  if not assigned(o) then begin
    exit;
  end;
  v := o.GetValue('data');
  if not assigned(v) then begin
    exit;
  end;
  if not (v is TJSONArray) then begin
    exit;
  end;
  a := v as TJSONArray;
  if a.Count=0 then begin
    exit;
  end;
  SetFieldDefs(a);
  fDatasetRef.Active := True;
  InsertData(a);
end;

end.

You can also download this source with a package which may be installed into your IDE, Download Here: jsonadapt

So how do I use it?

Having installed the component, it should appear on your component palette under the “REST Client” category.
Here are some instructions for building a sample application for it:

  1. Create a new application (VCL or FMX).
    (note, you may need to set your project path to include the location of jsonadapter.pas if you did not configure this during installation).
  2. Drop a TFDMemTable onto your form.
  3. Drop a TJSONDatasetAdapter adapter onto your form
  4. Drop a TStringGrid onto your form.
  5. Set the JSONDatasetAdapter1.Dataset property to FDMemTable1
  6. Set the JSONDatasetAdapter1.JSON property to some JSON data (see example data below).
  7. Use live bindings to bind your FDMemTable1 component to the string grid.

For good measure, I’ve recorded myself building this very demo here…
(Best viewed Full Screen, recorded in 1080p)

Here’s the sample JSON data that I used:

[{"county_name":"Del Norte","latitude":41.869383569541917,"longitude":-124.21444724840021,"spot_id":652,"spot_name":"Smith River Kellog Road"},
{"county_name":"Del Norte","latitude":41.786844846578227,"longitude":-124.25521494045159,"spot_id":653,"spot_name":"Point St George"},
{"county_name":"Del Norte","latitude":41.76951799906643,"longitude":-124.23922049300231,"spot_id":654,"spot_name":"Garths Reef"}]

Summary

As usual, my disclaimer:

This code is free to use as you see fit. The code is prepared for demonstration purposes only. I will not be held responsible for any damages caused by the use or misuse of this code.

So you now have the ability to push JSON data directly into a TFDMemTable without having to first attach a TRESTRequest component, go forth and consume JSON.

Thanks for Reading!

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

4 Responses

  1. jxluron@gmail.com says:

    Hello,
    yes, I am French, sorry for my English. I’m talking about data accented in the json: after the assignment to the fdmemtable, when the application is compiled under windows, the data is no longer accentuated. when the application is compiled under android, the accents are misinterpreted: for example “Fougère” becomes “Foug?re”.
    the solution that I was given: line 202
    // o: = TJSONObject.ParseJSONValue (TEncoding.ASCII.GetBytes (‘{“data”:’ + fJSON.Text + ‘}’), 0) as TJSONObject;
    // to manage the accents
    o: = TJSONObject.ParseJSONValue (TEncoding.UTF8.GetBytes (‘{“data”:’ + fJSON.Text + ‘}’), 0) as TJSONObject;
    and it works ! THANK YOU very much for this very useful component.

    :o)

    delaio.

  2. jxluron@gmail.com says:

    hello Craig,

    your component works well but I have two problems:
    – Some values exceed 255 characters: the data is truncated
    (Solution: Replace Line 121 FieldDef.DataType: = TFieldType.ftString; by FieldDef.DataType: = TFieldType.ftMemo; it’s ok !?!!)
    – the accents do not appear under windows and are displayed under Android.

    I do not know how to solve the problem. thanks for your help.

    delaio.

    • It looks like both issues lie outside the component.

      1) The solution you give looks good for strings longer than 255 characters, the field type ftString tells FireDAC that it’s dealing with a short string (max 255 characters), which I think is a vestige of it being a BDE replacement.

      2) I’m not sure what you mean by the accents appearing, but my guess is that your text has accents? In which case, it would depend on how FMX renders the font for the target platform. If you can give more information (perhaps a test app source) I’ll happily take a closer look.

      craig [dot] chapman [at] embarcadero [dot] com

      btw, have you tried placing a simple TLabel on your form and setting a caption with accents? (i.e. not just the data returned from the component)

  3. There is a case-sensitivity bug.

    Change line 105 of jsonadapt.pas from:

    n := Lowercase(Trim(p.JsonString.ToString));
    to
    n := p.JsonString.ToString;

    This should resolve the bug. Note, this component will only work in cases where the json field name is a valid SQL field name.

Leave a Reply