JSON format for RtcDataSet

[joepasquariello]

Hello,

I'm using RTC to build my first REST/JSON server, and it's working very well. RTC is an amazing product.

Most of the data I want to serve are query results in TDataSet, displayed in the browser as tables and charts. On the browser side, I'm using D3 and Dimple for data visualization. The D3 helper method d3.json() method expects a "flat" json array of records, as shown below.

[
    { "Brand":"Coolio", "Region":"Hipsville", "Sales Volume":1000 },
    { "Brand":"Uncoolio", "Region":"Hipsville", "Sales Volume":500 },
    { "Brand":"Coolio", "Region":"Dullsville", "Sales Volume":100 },
    { "Brand":"Uncoolio", "Region":"Dullsville", "Sales Volume":2000 }
]

TRtcDataSet::toJSON() creates a JSON string with a record for "dsfields" and a separate record for "dsrows". Is there any way in RTC to create JSON as shown above from a Delphi TDataSet? I can see that TRtcRecord creates JSON like one row lf the "flat" JSON table, but I can't see any way to transform TRtcDataSet --> array of TRtcRecord --> JSON.

{
  "\/dsfields":[
    {"name":"rowid","type":"L"},
    {"name":"utcDateTime","type":"F"},
    {"name":"TurbMW","type":"F"}
  ],
  "\/dsrows":[
    [1,2456075.28732639,425],
    [2,2456075.28733796,426],
    [3,2456075.28734954,427]
  ]
}

A second question about TRtcDataSet::toJSON() is why the "dsfields" and "dsrows" have "\/" characters? If the JSON above is assigned to a javascript object, what is the syntax for accessing "dsfields" and "dsrows"? What I am doing now is extracting the "\/" characters, but I would rather not have to do that.

Thanks very much.

Joe

[D.Tkalcec (RTC)]

ToJSON method on the TRtcDataSet class only supports one format. If you need your data to be serialized in a different format, you can either create a TRtcValue object containing the JSON data (what you are looking for is an Array of Records), or you can enumerate through the data and manually create a JSON string for the output you want.

Best Regards,
Danijel Tkalcec

[Kevin Powick]

Danijel,

I too am interested in understanding why the "\/" characters are prefixed to the JSON keys dsfields and dsrows.  Technically, it's an escaped solidus (forward slash), but I don't see its purpose wrt what I think would be desired JSON output.

As alluded to by Joe, it would make the JavaScript for accessing those key/value pairs a little awkward on the client (web browser) side.

[D.Tkalcec (RTC)]

The special characters in front of "dsfields" and "dsrows" arrays are used to distinguish between data generated by a RTC DataSet class, which should be deserialized back into a TRtcDataSet when received, and custom user structures containig "dsfields" or "dsrows" arrays, which should be deserialized into separate TRtcArray instances.

Best Regards,
Danijel Tkalcec

[Kevin Powick]

Can the injection of such characters be made optional (default: true) for dataset-to-JSON transformation in a future version of the RTC SDK?

Or maybe it would be better to have user-defined properties for the JSON keys exported for dsfields and dsrows?

[D.Tkalcec (RTC)]

These special escape characters are used only for RTC-specific types which are NOT part of the JSON specification. The names of the fields are fixed to enforce a RTC standard. If you do not want to use RTC-specific types, you can use types which are natively supported by JSON, in which case the JSON generator will produce exactly the results you want.

Best Regards,
Danijel Tkalcec

[joepasquariello]

Danijel,

Thanks for your reply. Is there any way built into RTC to transform a TDataSet to an array of RtcRecord, or a single TDataSet row to a TRtcRecord? If I could do either one of those, then I could construct the JSON string I need.

Kevin, the code below shows how I remove the "\/" characters before accessing dsfields and dsrows.
   
    responseText = responseText.replace( /\\\//g, "" );
    var myobj = JSON.parse( responseText );
    nDataRows = myobj.dsrows.length;
    nDataCols = myobj.dsfields.length;

I think you can avoid using replace() by addressing the elements like this, but I haven't tried it.

    var myobj = JSON.parse( responseText );
    nDataRows = myobj["\/dsrows"].length;
    nDataCols = myobj["\/dsfields"].length;

Joe

[D.Tkalcec (RTC)]

Try this:

Code:

uses DB, rtcInfo, rtcDB;

{$include rtcDefs.inc}
...

procedure DelphiDataSetToArray(DelphiDS:TDataSet; rtcArr:TRtcArray; OnlyDataFields:boolean=False);
  var
    flds:integer;
    fldname:string;
    field:TField;
    fstream:TStream;
    idx:integer;
    rtcDS:TRtcRecord;
  begin
  idx:=0;
  DelphiDS.First;
  while not DelphiDS.Eof do
    begin
    rtcDS:=rtcArr.newRecord(idx); 
    Inc(idx);
    for flds:=0 to DelphiDS.Fields.Count-1 do
      begin
      field:=DelphiDS.Fields[flds];
      if assigned(field) then
        begin
        fldname:=field.FieldName;
        if not field.IsNull then
          if (OnlyDataFields=False) or (field.FieldKind=fkData) then
            if field.isBlob then
              begin
              fstream:=DelphiDS.CreateBlobStream(field,bmRead);
              try
                if {$IFNDEF FPC} TBlobField(field).GraphicHeader and {$ENDIF}
                  ( (field.DataType = ftGraphic) or
                    (field.DataType = ftTypedBinary) ) then
                  RtcSkipGraphicFieldHeader(fstream);
                rtcDS.NewByteStream(fldname).CopyFrom(fstream,fstream.Size-fstream.Position);
              finally
                fstream.Free;
                end;
              end
            else
              case RTC_FIELD2VALUE_TYPES[RTC_DB2FIELD_TYPE(field.DataType)] of
                rtc_Currency: rtcDS.asCurrency[fldname]:=field.AsCurrency;
                rtc_DateTime: rtcDS.asDateTime[fldname]:=field.AsDateTime;
                rtc_String: rtcDS.asString[fldname]:=RtcString(field.AsString);
              {$IFDEF UNICODE}rtc_Text: rtcDS.asText[fldname]:=field.AsWideString;{$ENDIF}
                else rtcDS.Value[fldname]:=field.Value;
                end;
        end;
      end;
    DelphiDS.Next;
    end;
  end;

PS. Sorry, the 1st version wasn't right. I had to do it in Delphi to check if it compiles.

Best Regards,
Danijel Tkalcec

[joepasquariello]

Thank you, Danijel. I am one of your (few?) C++ Builder users, so I am testing by inserting this function into your rtcDB module and rebuilding. I'm getting errors, starting on this line:

              case RTC_FIELD2VALUE_TYPES[rtcDS.FieldType[fldname]] of

[DCC Error] rtcDB.pas(967): E2003 Undeclared identifier: 'FieldType'

[D.Tkalcec (RTC)]

Please copy/paste the whole code again. The 1st version I've posted had a bug (I was writing it in the web browser). I've revised the code since then and modified the post. The version above works (I've tested it now). Sorry about that.

Best Regards,
Danijel Tkalcec

[joepasquariello]

Oh, good, thanks! I will try again in a couple of hours. I'm looking forward to it.

[joepasquariello]

Danijel,

I tried the latest code, and it works perfectly. Thank you so much. I will report back again after I have tried updating a D3 object via this format.

Joe

[joepasquariello]

Danijel,

I am using the new DelphiDataSetToArray() function, then calling RtcArray->toJSON().

Thank you very much. It's working perfectly. The javascript object created from this JSON string can be assigned directly to the data field of a D3 object.

Joe

[D.Tkalcec (RTC)]

Thank you for your feedback. I've added the procedure DelphiDataSetToRtcArray to the rtcDB unit in the latest RTC SDK update (6.31), so you don't need to create a separate unit or manually modify the code to use it.

Best Regards,
Danijel Tkalcec

[joepasquariello]

Thank you again. I installed 6.31, and I'm using it now.