Synopse Open Source

TSQLTableJSON will expect some JSON content as input, will parse it in rows and columns, associate it with one or more optional TSQLRecord class types, then will let you access the data via its Get* methods.

You can use this TSQLTableJSON class as in the following example:

procedure WriteBabiesStartingWith(const Letters: RawUTF8; Sex: TSex);
var aList: TSQLTableJSON;
    Row: integer;
begin
  aList := Client.MultiFieldValues(TSQLBaby,'ID,BirthDate',
    'Name LIKE ? AND Sex = ?',[Letters+'%',ord(Sex)]);
  if aList=nil then
    raise Exception.Create('Impossible to retrieve data from Server');
  try
    for Row := 1 to aList.RowCount do
      writeln('ID=',aList.GetAsInteger(Row,0),' BirthDate=',aList.Get(Row,1));
  finally
    aList.Free;
  end;
end;

For a record with a huge number of fields, specifying the needed fields could save some bandwidth. In the above sample code, the ID column has a field index of 0 (so is retrieved via aList.GetAsInteger(Row,0)) and the BirthDate column has a field index of 1 (so is retrieved as a PUTF8Char via aList.Get(Row,1)). All data rows are processed via a loop using the RowCount property count - first data row is indexed as 1, since the row 0 will contain the column names.

The TSQLTable class has some methods dedicated to direct cursor handling, as such:

procedure WriteBabiesStartingWith(const Letters: RawUTF8; Sex: TSex);
var aList: TSQLTableJSON;
begin
  aList := Client.MultiFieldValues(TSQLBaby,'ID,BirthDate',
    'Name LIKE ? AND Sex = ?',[Letters+'%',ord(Sex)]);
  try
    while aList.Step do
      writeln('ID=',aList.Field(0),' BirthDate=',aList.Field(1));
  finally
    aList.Free;
  end;
end;

By using the TSQLTable.Step method, you do not need to check that aList<>nil, since it will return false if aList is not assigned. And you do not need to access the RowCount property, nor specify the current row number.

We may have used not the field index, but the field name, within the loop:

      writeln('ID=',aList.Field('ID'),' BirthDate=',aList.Field('BirthDate'));

You can also access the field values using late-binding and a local variant, which gives some perfectly readable code:

procedure WriteBabiesStartingWith(const Letters: RawUTF8; Sex: TSex);
var baby: variant;
begin
  with Client.MultiFieldValues(TSQLBaby,'ID,BirthDate'
    'Name LIKE ? AND Sex = ?',[Letters+'%',ord(Sex)]) do
  try
    while Step(false,@baby) do
      writeln('ID=',baby.ID,' BirthDate=',baby.BirthDate);
  finally
    Free;
  end;
end;

In the above code, late-binding will search for the "ID" and "BirthDate" fields at runtime. But the ability to write baby.ID and baby.BirthDate is very readable. Using a with ... do statement makes the code shorter, but should be avoided if it leads into confusion, e.g. in case of more complex process within the loop.

See also the following methods of TSQLRest: OneFieldValue, OneFieldValues, MultiFieldValue, MultiFieldValues which are able to retrieve either a TSQLTableJSON, or a dynamic array of integer or RawUTF8. And also List and ListFmt methods of TSQLRestClient, if you want to make a JOIN against multiple tables at once.

Feedback is welcome in our forum, as usual.