Convert table to JSON array
lazarus / 63-Database

----------------------------------------------------------------------
LemonParty · 2025-11-08 17:55

Hello.

Is there a procedure that allow to convert a whole SQL table into JSON
array?

----------------------------------------------------------------------
bytebites · 2025-11-08 21:08

With Sqlite it might work?
https://forum.lazarus.freepascal.org/index.php?topic=52027.0

----------------------------------------------------------------------
paweld · 2025-11-09 05:58

Something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses 
fpjson, DateUtils, base64; function TForm1.DataSetToJson(ds: TDataSet;
FormatJson: Boolean): String;const  boolarr: array[Boolean] of String
= ('false', 'true');var  jarr: TJsonArray;  jobj: TJsonObject;  i,
idx: Integer;  stream: TStream;  s: String;begin  Result := '[]';  if
not ds.Active or (ds.RecordCount = 0) then    exit;  jarr :=
TJsonArray.Create;  idx := ds.RecNo;  ds.DisableControls;  ds.First; 
while not ds.EOF do  begin    jobj := TJsonObject.Create;    for i :=
0 to ds.FieldCount - 1 do    begin      if ds.Fields[i].IsNull then  
    jobj.Nulls[ds.Fields[i].FieldName] := True      else      begin  
    case ds.Fields[i].DataType of          ftString, ftFixedChar,
ftMemo: jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsString);     
   ftSmallint, ftInteger, ftWord, ftLargeint, ftAutoInc:
jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsLargeInt);         
ftFloat, ftCurrency{$IF FPC_FULLVERSION>30202}, ftSingle{$ENDIF}:
jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsFloat);         
ftBoolean: jobj.Add(ds.Fields[i].FieldName,
boolarr[ds.Fields[i].AsBoolean]);            ftDateTime:
jobj.Add(ds.Fields[i].FieldName,
DateToISO8601(ds.Fields[i].AsDateTime));          ftDate:
jobj.Add(ds.Fields[i].FieldName, FormatDatetime('yyyy-mm-dd',
ds.Fields[i].AsDateTime));          ftTime:
jobj.Add(ds.Fields[i].FieldName, FormatDatetime('hh:nn:ss',
ds.Fields[i].AsDateTime));          ftBlob:            begin         
   stream := ds.CreateBlobStream(ds.Fields[i], bmRead);              s
:= '';              if stream.Size > 0 then              begin       
       SetLength(s, stream.Size);                stream.Position :=
0;                stream.Read(s[1], stream.Size);                s :=
EncodeStringBase64(s);              end;              stream.Free;   
         jobj.Add(ds.Fields[i].FieldName, s);            end;       
end;      end;    end;    jarr.Add(jobj);    ds.Next;  end;  ds.RecNo
:= idx;  ds.EnableControls;  if FormatJson then    Result :=
jarr.FormatJSON([], 2)  else    Result := jarr.AsJSON; 
jarr.Free;end;and then you use it like this, for example:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ShowMessage(DataSetToJson(TSQLQuery1, True);
Edit: Compliance with FPC 3.2.2

----------------------------------------------------------------------
dsiders · 2025-11-09 06:58

Quote from: paweld on November 09, 2025, 06:58:32 amSomething like
this:

There is no TFieldType called ftSingle in FPC 3.2.2.

----------------------------------------------------------------------
paweld · 2025-11-09 07:43

@dsiders Indeed. I am using 3.2-fixes.
According to the footer, OP using FPC 3.3.1, so will be OK too.
I edited my previous post

----------------------------------------------------------------------
LemonParty · 2025-11-09 12:55

Yes, paweld. This is what I need. Thank you.

----------------------------------------------------------------------
dsiders · 2025-11-09 16:25

Quote from: paweld on November 09, 2025, 08:43:51 am@dsiders Indeed. I
am using 3.2-fixes.
According to the footer, OP using FPC 3.3.1, so will be OK too.
I edited my previous post

Sorry, I didn't notice the footer.

----------------------------------------------------------------------
paweld · 2025-11-09 16:56

No problem. I think you're right, because most people use the stable
version, so it would be good if the code didn't cause any errors in
it.