Delphi XE6 FireDAC

quasiceo 2017-01-26

展开全文

Delphi XE6 FireDAC - Export TFDQuery recordset to JSON

up vote 0 down vote favorite

I'm using FireDAC in Delphi XE6 to query a database (Pervasive) through ODBC. I have a TFDQuery component which runs my SELECT query and returns the records. Once the query is complete I want to export the data in the recordset as JSON. I've tried using the following code :

fdacQuery.SaveToStream(myStream, sfJSON);

This creates JSON, but only for the table definition i.e. field names, data types, constraints etc. - there is no representation of the data. Is there another method I should be using to export just the recordset data as JSON? Is there another solution?

share|improve this question

asked Jul 21 '15 at 14:42

Jonathan Wareham

1,64333069

Have you changed the ResourceOptions.StoreItems property ? – TLama Jul 21 '15 at 14:44

Hi, thanks for the tip - I've now set that property to [siData] which has changed the result but still no data. I'm now getting the following JSON : {"FDBS":{"Version":11,"Manager":{"TableList":[{"class":"Tabl??e","Name":"Customers??","SourceID":1,"RowL??ist":[]}]}}} – Jonathan Wareham Jul 21 '15 at 14:58

add a comment

2 Answers

active oldest votes

up vote 1 down vote accepted

Try this on for size then. I did it for a utility I needed yesterday. It uses SuperObject. I left all field types in the code in case you want to add other special treatments or tweak any of those I put in. It's working for me on many random datasets right now.

class procedure TTool.ExportDataSetToJson(DataSet: TDataSet; FileName: string; Append: boolean = false);
const
  SData = 'data';
var
  json : ISuperObject;
  item : ISuperObject;
  wasActive: boolean;
  fld : TField;
begin
  json := SO;
  json.O[SData] := SA([]);
  wasActive := DataSet.Active;
  try
    DataSet.Active := true;
    DataSet.First;
    while not DataSet.Eof do
    begin
      item := SO;
      for fld in DataSet.Fields do
      begin
        case fld.DataType of
//          ftUnknown: ;
          ftString,
          ftBlob,
          ftMemo,
          ftFmtMemo,
          ftBytes,
          ftVarBytes,
          ftFixedChar,
          ftFixedWideChar,
          ftWideMemo,
          ftByte,
          ftWideString: item.S[fld.FieldName] := fld.AsString;
          ftBoolean: item.B[fld.FieldName] := fld.AsBoolean;
          ftFloat,
          ftSingle,
          ftExtended,
          ftCurrency,
          ftFMTBcd,
          ftBCD: item.D[fld.FieldName] := fld.AsFloat;
          ftTime : item.S[fld.FieldName] := TimeToJson(fld.AsDateTime);
          ftDate,
          ftTimeStamp,
          ftOraTimeStamp,
          ftDateTime: item.S[fld.FieldName] := DateTimeToJson(fld.AsDateTime);
          ftSmallint,
          ftInteger,
          ftWord,
          ftAutoInc,
          ftLongWord,
          ftShortint,
          ftLargeInt: item.I[fld.FieldName] := fld.AsLargeInt;
//          ftGraphic: ;
//          ftParadoxOle: ;
//          ftDBaseOle: ;
//          ftTypedBinary: ;
//          ftCursor: ;
//          ftADT: ;
//          ftArray: ;
//          ftReference: ;
//          ftDataSet: ;
//          ftOraBlob: ;
//          ftOraClob: ;
//          ftVariant: ;
//          ftInterface: ;
//          ftIDispatch: ;
          ftGuid: item.S[fld.FieldName] := fld.AsString;
//          ftOraInterval: ;
//          ftConnection: ;
//          ftParams: ;
//          ftStream: ;
//          ftTimeStampOffset: ;
//          ftObject: ;
          else
            item.S[fld.FieldName] := fld.AsString;
        end;
      end;
      DataSet.Next;
      json.A[SData].Add(item);
    end;
    if Append then
      TFile.AppendAllText(FileName, json.AsJSon(true, true))
    else
      json.SaveTo(FileName, true, true);
  finally
    DataSet.Active := wasActive;
  end;

end;

share|improve this answer

answered Jul 22 '15 at 16:35

John Kaster

1,4141926

Looks good, many thanks! – Jonathan Wareham Jul 23 '15 at 7:16

Glad it works for you. – John Kaster Jul 23 '15 at 16:00

add a comment

up vote 0 down vote

Have you looked at the code in the tutorial at http://docwiki./RADStudio/XE8/en/Tutorial:_Using_a_REST_DataSnap_Server_with_an_Application_and_FireDAC yet?

// Create dataset list
Result := TFDJSONDataSets.Create;
// Add departments dataset
TFDJSONDataSetsWriter.ListAdd(Result, sDepartment, FDQueryDepartment);
// Add employees dataset
TFDJSONDataSetsWriter.ListAdd(Result, sEmployees, FDQueryDepartmentEmployees);

share|improve this answer

answered Jul 21 '15 at 21:58

John Kaster

1,4141926

Hi thanks for the reply. I have taken a look at that tutorial but I don't think it helps me as the TFDJSONDataSets object returns JSON data with mime encoded binary content. This means that RAD studio needs to be used at both ends, whereas I want to support all clients and platforms. – Jonathan Wareham Jul 22 '15 at 8:36

add a comment