Convert table to JSON array

[LemonParty]

Hello.

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

[bytebites]

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

[paweld]

Something like this:

Code: Pascal  [Select][+][-]

1. uses
2.   fpjson, DateUtils, base64;
3.  
4. function TForm1.DataSetToJson(ds: TDataSet; FormatJson: Boolean): String;
5. const
6.   boolarr: array[Boolean] of String = ('false', 'true');
7. var
8.   jarr: TJsonArray;
9.   jobj: TJsonObject;
10.   i, idx: Integer;
11.   stream: TStream;
12.   s: String;
13. begin
14.   Result := '[]';
15.   if not ds.Active or (ds.RecordCount = 0) then
16.     exit;
17.   jarr := TJsonArray.Create;
18.   idx := ds.RecNo;
19.   ds.DisableControls;
20.   ds.First;
21.   while not ds.EOF do
22.   begin
23.     jobj := TJsonObject.Create;
24.     for i := 0 to ds.FieldCount - 1 do
25.     begin
26.       if ds.Fields[i].IsNull then
27.         jobj.Nulls[ds.Fields[i].FieldName] := True
28.       else
29.       begin
30.         case ds.Fields[i].DataType of
31.           ftString, ftFixedChar, ftMemo: jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsString);
32.           ftSmallint, ftInteger, ftWord, ftLargeint, ftAutoInc: jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsLargeInt);
33.           ftFloat, ftCurrency{$IF FPC_FULLVERSION>30202}, ftSingle{$ENDIF}: jobj.Add(ds.Fields[i].FieldName, ds.Fields[i].AsFloat);
34.           ftBoolean: jobj.Add(ds.Fields[i].FieldName, boolarr[ds.Fields[i].AsBoolean]);  
35.           ftDateTime: jobj.Add(ds.Fields[i].FieldName, DateToISO8601(ds.Fields[i].AsDateTime));
36.           ftDate: jobj.Add(ds.Fields[i].FieldName, FormatDatetime('yyyy-mm-dd', ds.Fields[i].AsDateTime));
37.           ftTime: jobj.Add(ds.Fields[i].FieldName, FormatDatetime('hh:nn:ss', ds.Fields[i].AsDateTime));
38.           ftBlob:
39.             begin
40.               stream := ds.CreateBlobStream(ds.Fields[i], bmRead);
41.               s := '';
42.               if stream.Size > 0 then
43.               begin
44.                 SetLength(s, stream.Size);
45.                 stream.Position := 0;
46.                 stream.Read(s[1], stream.Size);
47.                 s := EncodeStringBase64(s);
48.               end;
49.               stream.Free;
50.               jobj.Add(ds.Fields[i].FieldName, s);
51.             end;
52.         end;
53.       end;
54.     end;
55.     jarr.Add(jobj);
56.     ds.Next;
57.   end;
58.   ds.RecNo := idx;
59.   ds.EnableControls;
60.   if FormatJson then
61.     Result := jarr.FormatJSON([], 2)
62.   else
63.     Result := jarr.AsJSON;
64.   jarr.Free;
65. end;

and then you use it like this, for example:

Code: Pascal  [Select][+][-]

1. ShowMessage(DataSetToJson(TSQLQuery1, True);

Edit: Compliance with FPC 3.2.2

[dsiders]

Something like this:

There is no TFieldType called ftSingle in FPC 3.2.2.

[paweld]

@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]

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

[dsiders]

@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]

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.