Delphi FDQuery to Json

Question

I'm trying to convert the result of my Sqlite query into a Json, to use the same procedures I use with remote binding to Sql Server by php.

The code works, but do you think it's a better solution?

Anyone there do that?

function TLogin.RetornaRegistros(query:String): String;
var
  FDQuery : TFDQuery;
  field_name,nomeDaColuna,valorDaColuna : String;
  I: Integer;
begin
    FDQuery := TFDQuery.Create(nil);
    try
      FDQuery.Connection := FDConnection1;
      FDQuery.SQL.Text := query;
      FDQuery.Active := True;
      FDQuery.First;

      result := '[';
      while (not FDQuery.EOF) do
      begin

        result := result+'{';
        for I := 0 to FDQuery.FieldDefs.Count-1 do
        begin
          nomeDaColuna  := FDQuery.FieldDefs[I].Name;
          valorDaColuna := FDQuery.FieldByName(nomeDaColuna).AsString;
          result := result+'"'+nomeDaColuna+'":"'+valorDaColuna+'",';
        end;
        Delete(result, Length(Result), 1);
        result := result+'},';

        FDQuery.Next;
      end;
      FDQuery.Refresh;

      Delete(result, Length(Result), 1);
      result := result+']';

    finally
      FDQuery.Free;
    end;
end;

Which **specific** Delphi version are you using? Delphi XE did not come with FireDAC, so it would be helpful if you replace the `delphi-xe` tag on your q. — MartynA, May 26 '17 at 17:11
Have you looked at http://docwiki.embarcadero.com/RADStudio/XE8/e/Tutorial:_Using_a_REST_DataSnap_Server_with_an_Application_and_FireDAC? E.g. the section function TServerMethods1.GetDepartmentNames — MartynA, May 26 '17 at 17:54

score 4 · Answer 1 · edited Aug 23 '20 at 10:14

That is not a good approach. I really suggest consider at least three options:

Use the power of System.JSON unit.

Uses {...} System.JSON;
        
    Var    
    FDQuery : TFDQuery;
    field_name,Columnname,ColumnValue : String;
    I: Integer;

    LJSONObject:TJsonObject;
    begin
        FDQuery := TFDQuery.Create(nil);
        try
          FDQuery.Connection := FDConnection1;
          FDQuery.SQL.Text := query;
          FDQuery.Active := True;
          FdQuery.BeginBatch;//Don't update external references until EndBatch;
          FDQuery.First;
          LJSONObject:= TJSONObject.Create;
          while (not FDQuery.EOF) do
          begin
                for I := 0 to FDQuery.FieldDefs.Count-1 do
                begin
                  ColumnName  := FDQuery.FieldDefs[I].Name;
                  ColumnValue := FDQuery.FieldByName(ColumnName).AsString;
                  LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONString.Create(ColumnValue)));
                FDQuery.Next;
              end;
              //FDQuery.Refresh; that's wrong
             FdQuery.EndBatch;
            finally 
              FDQuery.Free;
              Showmessage(LJSonObject.ToString);
            end;
        end;

https://www.youtube.com/watch?v=MLoeLpII9IE&t=715s

Second approach, use FDMemTable.SaveToStream; The same works for FDMemTable.SaveToFile; Put a TFDMemTable on Datamodule (Or form, as well).

        fMStream:TMemoryStream;
        Begin       
        FDQuery := TFDQuery.Create(nil);
           try
              FDQuery.Connection := FDConnection1;
              FDQuery.SQL.Text := query;
              FDQuery.Active := True;
              //fdMemTable1.Data:=fdQuery.Data; {note *2}
              fdMemTable1.CloneCursor(FdQuery,true,true);{note *3}
              fMStream:=TMemoryStream.Create;
              FdMemTable1.SaveToStream(fMStream,sfJson);
           finally
               FDQuery.Free;
               FdMemTable.Close;
           end;

Now you can Read the JSON content

For example, following answer Converting TMemoryStream to 'String' in Delphi 2009

function MemoryStreamToString(M: TMemoryStream): string;
begin
      SetString(Result, PChar(M.Memory), M.Size div SizeOf(Char));
end;

and you have the json as String

The BatchMove suggeted by @VictoriaMarotoSilva

You can use BatchMove components, which provides an interface to move data between datasets, but it works better for backup and importation when you want to save data in drive, XML or json format. I didn't find examples yet, using data moving in memory; if somebody else has an example, please comment.

Notes

Using FdMemTable, don't forget drag TFDStanStorageJSONLink component for datamodule
method .Data just works for FiredacDatasets (Datasets with prefix FD). To assign data for memTable in old Datasets use method .Copydata instead.
Sorry guys, I change .Data to .CloneCursor to share the same Memory Space with both datasets.

I think Solution 2 is very good, because you can control to Save only the Delta if you want, and later to Load with original updateStatus for each record. — Roberto Novakosky, Jan 20 '21 at 23:38

score 3 · Accepted Answer · edited Aug 23 '20 at 10:13

I just modified my first answer below to comport different type of field to convert number, date and boolean in appropriate json format. I comment the Types I didn't test. Look

Uses {...} System.JSON;

Var    
FDQuery : TFDQuery;
field_name, Columnname, ColumnValue : String;
I: Integer;

LJSONObject:TJsonObject;
begin
    FDQuery := TFDQuery.Create(nil);
    try
      FDQuery.Connection := FDConnection1;
      FDQuery.SQL.Text := query;
      FDQuery.Active := True;
      FdQuery.BeginBatch;//Don't update external references until EndBatch;
      FDQuery.First;
      LJSONObject:= TJSONObject.Create;
      while (not FDQuery.EOF) do
      begin
            for I := 0 to FDQuery.FieldDefs.Count-1 do
            begin
              ColumnName  := FDQuery.FieldDefs[I].Name;

              Case FDQuery.FieldDefs[I].Datatype of
                  ftBoolean: 
                    IF FDQuery.FieldDefs[I].Value=True then   LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONTrue.Create)) else 
                      LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONFalse.Create)); 
                  ftInteger,ftFloat{,ftSmallint,ftWord,ftCurrency} :
                    LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONNumber.Create(FDQuery.FieldDefs[I].value)));   
                  ftDate,ftDatetime,ftTime:
                   LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONString.Create(FDQuery.FieldDefs[I].AsString)));
//or TJSONString.Create(formatDateTime('dd/mm/yyyy',FDQuery.FieldDefs[I].Value));
                  else LJSONObject.AddPair(TJSONPair.Create(TJSONString.Create( ColumnName),TJSONString.Create(FDQuery.FieldDefs[I].AsString)));
              End;

            FDQuery.Next;
          end;
         FdQuery.EndBatch;
        finally 
          FDQuery.Free;
          Showmessage(LJSonObject.ToString);
        end;
    end;

More about dataset.DataType http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/DB_TFieldType.html

More about JSONTypes https://community.embarcadero.com/blogs/entry/json-types-for-server-methods-in-datasnap-2010-4

Yes, your answer is the most correct. Thank you for calling attention to the different types. — João Rodrigues, May 30 '17 at 14:29

score 0 · Answer 3 · answered May 26 '17 at 17:03

0

Consider using TFDBatchMove component. It's for direct transferring of data between two databases with additional mappings support. As a source and target can be a text, dataset or an SQL query to any of the FireDAC's supported database engines.

answered May 26 '17 at 17:03

Victoria

7,822
2
21
44

score 0 · Answer 4 · answered May 27 '17 at 09:46

The Delphi MVC Framework contains a powerful mapper to map json to objects and datasets to objects. The Mapper is a sub project. It's independent code that can also be used in other kind of projects. It is open-source!

Advantage is that boolean values for example, are converted to TJSONBool type and not a string. I suggest take a look at the samples.

https://github.com/danieleteti/delphimvcframework/tree/master/samples/objectsmapperssamples

score 0 · Answer 5 · answered Jan 27 '18 at 23:20

Probably not the best solution, and you can modify this to how you would like the JSON to be formatted... here is a quick sample solution:

function GetDataSetAsJSON(DataSet: TDataSet): TJSONObject;
var
  f: TField;
  o: TJSOnObject;
  a: TJSONArray;
begin
  a := TJSONArray.Create;
  DataSet.Active := True;
  DataSet.First;
  while not DataSet.EOF do begin
    o := TJSOnObject.Create;
    for f in DataSet.Fields do
      o.AddPair(f.FieldName, VarToStr(f.Value));
    a.AddElement(o);
    DataSet.Next;
  end;
  DataSet.Active := False;
  Result := TJSONObject.Create;
  Result.AddPair(DataSet.Name, a);
end;

Delphi FDQuery to Json

5 Answers5