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?

json delphi delphi-xe6 firedac

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.embarcadero.com/RADStudio/XE8/en/Tutorial:_Using_a_REST_DataSnap_Server_with_an_Application_and_FireDAC yet?

// Create dataset listResult := TFDJSONDataSets.Create;// Add departments datasetTFDJSONDataSetsWriter.ListAdd(Result, sDepartment, FDQueryDepartment);// Add employees datasetTFDJSONDataSetsWriter.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