Table of Contents

TMS FNC Cloud Stellar Data Store Guides

TTMSFNCCloudStellarDataStore reads and writes structured records in a TMS Stellar Data Store backend. The data model is three levels deep — a project holds tables, a table holds metadata (its field definitions) and entities (its records) — and every call is scoped by the ProjectID and TableID you resolve after connecting. Reach for it when you want a hosted table without running a database server yourself; reach for a REST client instead when the backend is not Stellar. This page covers connecting and scoping, inserting single records and batches, and querying with filters, sorting, field selection and de-duplication.

Authentication and connecting

Set the OAuth credentials of your registered app on Authentication, then call Connect. OnConnected fires once the session is usable — resolve the project and table from there, because ProjectID and TableID scope every later request and a call made without them raises an exception rather than failing silently. GetProjects reports through OnGetProjects, GetTables through OnGetTables, and TableByName resolves a table from a listing.

procedure TForm1.FormCreate(Sender: TObject);
begin
  TMSFNCCloudStellarDataStore1.Authentication.ClientID := 'your-app-client-id';
  TMSFNCCloudStellarDataStore1.Authentication.Secret := 'your-app-secret';
  TMSFNCCloudStellarDataStore1.Authentication.CallBackURL := 'http://127.0.0.1:8000';

  TMSFNCCloudStellarDataStore1.OnConnected := StoreConnected;
  TMSFNCCloudStellarDataStore1.OnGetProjects := StoreProjectsListed;
  TMSFNCCloudStellarDataStore1.OnGetTables := StoreTablesListed;
  TMSFNCCloudStellarDataStore1.Connect;
end;

procedure TForm1.StoreConnected(Sender: TObject);
begin
  // ProjectID and TableID scope every later call, so resolve them first.
  TMSFNCCloudStellarDataStore1.GetProjects;
end;

procedure TForm1.StoreProjectsListed(Sender: TObject;
  const AInfo: TTMSFNCCloudStellarDataStoreProjects;
  const ARequestResult: TTMSFNCCloudBaseRequestResult);
begin
  if not ARequestResult.Success or (AInfo.Count = 0) then
    Exit;

  TMSFNCCloudStellarDataStore1.ProjectId := AInfo[0].ID;
  TMSFNCCloudStellarDataStore1.GetTables;
end;

procedure TForm1.StoreTablesListed(Sender: TObject;
  const AInfo: TTMSFNCCloudStellarDataStoreTables;
  const ARequestResult: TTMSFNCCloudBaseRequestResult);
var
  Table: TTMSFNCCloudStellarDataStoreTable;
begin
  if not ARequestResult.Success then
    Exit;

  Table := TMSFNCCloudStellarDataStore1.TableByName('Contacts');
  if Assigned(Table) then
  begin
    TMSFNCCloudStellarDataStore1.TableId := Table.ID;
    TMSFNCCloudStellarDataStore1.GetMetaData;
  end;
end;

Requests are asynchronous by default. Setting Mode to moSync makes each call block until the service answers, which is what the setup path above would use in a batch or startup routine; switch it back to moAsync for UI-driven work.

Inserting records

Insert is overloaded, and which overload you call decides which event reports the outcome:

Overload Inserts Reports through
Insert(AValues: TStringList) one record from name/value pairs OnInsert
Insert(AEntity: TTMSFNCCloudStellarDataStoreEntity) one entity OnInsert
Insert(AEntities: TTMSFNCCloudStellarDataStoreEntities) every entity in the collection, in one request OnInsertAll

Build records by adding to the Entities collection and assigning through Values['<field>'], which accepts any TValue — string, integer, float, boolean or TDateTime — as long as the field exists in the table metadata.

Batch inserts and OnInsertAll

Use the collection overload when importing more than a handful of records: one HTTP round trip instead of one per record, which matters as much for rate limits as for speed. OnInsertAll receives the whole inserted set with the IDs the service assigned, so it is where you refresh a list or map the new IDs back onto your local data. Note that OnInsert does not fire for a batch — wire the event that matches your overload.

procedure TForm1.ImportContacts;
var
  Entity: TTMSFNCCloudStellarDataStoreEntity;
begin
  // OnInsert reports a single-record insert; OnInsertAll reports a batch, so
  // wire the one that matches the overload you call.
  TMSFNCCloudStellarDataStore1.OnInsertAll := StoreRecordsInserted;

  Entity := TMSFNCCloudStellarDataStore1.Entities.Add;
  Entity.Values['FirstName'] := 'Isaac';
  Entity.Values['LastName'] := 'Tailor';
  Entity.Values['Email'] := 'isaac@contacts.com';
  Entity.Values['Admin'] := True;

  Entity := TMSFNCCloudStellarDataStore1.Entities.Add;
  Entity.Values['FirstName'] := 'Laura';
  Entity.Values['LastName'] := 'Schneider';
  Entity.Values['Email'] := 'laura@contacts.com';
  Entity.Values['Admin'] := False;

  // One request for the whole collection instead of one request per record.
  TMSFNCCloudStellarDataStore1.Insert(TMSFNCCloudStellarDataStore1.Entities);
end;

procedure TForm1.StoreRecordsInserted(Sender: TObject;
  const AInfo: TTMSFNCCloudStellarDataStoreEntities;
  const ARequestResult: TTMSFNCCloudBaseRequestResult);
var
  I: Integer;
begin
  if not ARequestResult.Success then
  begin
    ShowMessage('Batch insert failed: ' + ARequestResult.ResultString);
    Exit;
  end;

  // AInfo carries the inserted entities with the IDs the service assigned.
  for I := 0 to AInfo.Count - 1 do
    ListBox1.Items.Add(AInfo[I].ValueAsString['FirstName'] + ' #' + AInfo[I].ID);
end;

Querying

Query reports its result through OnQuery, which receives the matching entities. Read a field with ValueAsString['<field>'] for display, or Values['<field>'] for the typed value.

Important

A where-clause, sort order, field selection or Distinct flag is only sent when it is passed into the query. Table.Query forwards the table's own Filters, SortOrder, JoinQuery, SelectQuery and Distinct, and the Query(AFilters, ASortOrder, AJoinQuery, ASelectQuery, ADistinct) overload takes them as arguments. The parameterless Query and the Query(APageSize, APageIndex) overload send none of them — so setting the component's own Filter, SelectQuery or Distinct property and then calling Query returns an unfiltered result. Configure the query on the table, and run it with Table.Query.

Filter operators

A filter is a field, a value, a comparison operator (TTMSFNCCloudComparisonOperator) and a logical operator (TTMSFNCCloudLogicalOperator, loAnd / loOr / loNone) that joins it to the preceding filter. Beyond the basic coEqual, coLike, coLarger, coSmaller and coContains, the operator set covers inclusive range bounds, negation and null tests:

Operator Matches
coEqual / coNotEqual field equals / does not equal the value
coLarger / coSmaller strictly greater / less than the value
coLargerThanOrEqual / coSmallerThanOrEqual greater / less than or equal to the value — use these for inclusive range bounds
coLike / coNotLike pattern matches / does not match the value
coContains the value is one of a set of values
coNull the field has no value stored 
procedure TForm1.QueryActiveTopAccounts;
var
  Table: TTMSFNCCloudStellarDataStoreTable;
begin
  TMSFNCCloudStellarDataStore1.OnQuery := StoreQueried;

  Table := TMSFNCCloudStellarDataStore1.TableByName('Contacts');
  if not Assigned(Table) then
    Exit;

  Table.Filters.Clear;

  // Range bounds: coLargerThanOrEqual / coSmallerThanOrEqual include the
  // boundary value, where coLarger / coSmaller exclude it.
  Table.Filters.Add('Sales', 1000, coLargerThanOrEqual, loAnd);
  Table.Filters.Add('Sales', 50000, coSmallerThanOrEqual, loAnd);
  // coNotEqual excludes a single value; coNotLike negates a pattern match.
  Table.Filters.Add('Country', 'XX', coNotEqual, loAnd);
  Table.Filters.Add('Email', '%@example.com', coNotLike, loAnd);
  // coNull matches records where the field was never filled in.
  Table.Filters.Add('ClosedOn', '', coNull, loAnd);

  Table.Query;
end;

Selecting fields and removing duplicates

SelectQuery restricts a query to a comma-separated list of fields instead of the whole record, which is the cheapest way to populate a lookup list or a single-column picker. Distinct collapses duplicate rows of that selection — it is only sent when SelectQuery is non-empty, so Distinct on its own has no effect.

procedure TForm1.ListCountries;
var
  Table: TTMSFNCCloudStellarDataStoreTable;
begin
  TMSFNCCloudStellarDataStore1.OnQuery := StoreQueried;

  Table := TMSFNCCloudStellarDataStore1.TableByName('Contacts');
  if not Assigned(Table) then
    Exit;

  // SelectQuery narrows the request to the listed fields instead of every
  // column, which keeps the payload small.
  Table.SelectQuery := 'Country';
  // Distinct only takes effect together with a SelectQuery - it collapses
  // duplicate rows of the selected fields.
  Table.Distinct := True;

  // Table.Query forwards the table's own SelectQuery, Distinct, Filters,
  // SortOrder and JoinQuery; the parameterless DataStore.Query does not.
  Table.Query;
end;

procedure TForm1.StoreQueried(Sender: TObject;
  const AInfo: TTMSFNCCloudStellarDataStoreEntities;
  const ARequestResult: TTMSFNCCloudBaseRequestResult);
var
  I: Integer;
begin
  if not ARequestResult.Success then
    Exit;

  ListBox1.Items.Clear;
  for I := 0 to AInfo.Count - 1 do
    ListBox1.Items.Add(AInfo[I].ValueAsString['Country']);
end;

Combining filters, sorting, selection and paging

The query parts compose: the same table can carry a filter set, a sort order, a field selection, the Distinct flag and a page window, and Table.Query sends them together.

procedure TForm1.QueryDistinctActiveCountries;
var
  Table: TTMSFNCCloudStellarDataStoreTable;
begin
  TMSFNCCloudStellarDataStore1.OnQuery := StoreQueried;

  Table := TMSFNCCloudStellarDataStore1.TableByName('Contacts');
  if not Assigned(Table) then
    Exit;

  // Filters, SortOrder, SelectQuery and Distinct all travel together on the
  // table, so one Table.Query call sends the complete where/sort/select
  // clause set.
  Table.Filters.Clear;
  Table.Filters.Add('Sales', 1000, coLargerThanOrEqual, loAnd);
  Table.Filters.Add('ClosedOn', '', coNull, loAnd);

  Table.SortOrder.Clear;
  Table.SortOrder.Add('Country', soAscending);

  Table.SelectQuery := 'Country';
  Table.Distinct := True;

  // Page size and index are optional; pass them to cap a large result set.
  Table.Query(50, 0);
end;

Common mistakes

  • Calling the parameterless Query after configuring filters. It sends no where/sort/select clause at all. Use Table.Query, or the full Query overload.
  • Setting SelectQuery or Filter on the component. Those properties are stored but never read by a query. The table-level SelectQuery, Distinct and Filters are the ones that reach the service.
  • Setting Distinct without SelectQuery. The flag is only appended alongside a field selection.
  • Handling OnInsert for a batch insert. The collection overload reports through OnInsertAll only.
  • Querying before ProjectID and TableID are resolved. A missing project or table ID raises an exception; resolve both from OnConnected / OnGetProjects / OnGetTables first.
  • Assigning a value for a field that is not in the metadata. Add the field with AddMetaData before inserting records that use it.

See also