jsondelphidelphi-xedelphi-10.1-berlinfiredac

Delphi FDQuery to Json


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;

Solution

  • 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