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;

Answer 1

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