Skip to content

FireDAC & Datasets ​

MARS has first-class support for FireDAC: a resource method returns a TFDDataSet (or an array of them) and MARS writes it as JSON, XML or the FireDAC native format; a Delphi client fetches the datasets into TFDMemTables, lets the user edit them and sends back only the changes (the delta), which the server applies. The Data Access page describes the model shared with the Devart integrations; this page is the complete reference for FireDAC. The FireDACDemo puts everything together.

Units ​

UnitContent
MARS.Data.FireDACTMARSFireDAC (the helper), [Connection], [SQLStatement], TMARSFDMemTable
MARS.Data.FireDAC.InjectionService[Context] for TFDConnection and TMARSFireDAC
MARS.Data.FireDAC.ReadersAndWriterswriters for TFDDataSet and TArray<TFDDataSet>, reader for TArray<TFDMemTable>
MARS.Data.FireDAC.ResourcesTMARSFDDatasetResource (GET datasets, POST deltas)
MARS.Data.FireDAC.DataModuleTMARSFDDataModuleResource (a data module as a resource)
MARS.Data.FireDAC.UtilsTFDDataSets (encoding of the native format), TMARSFDApplyUpdatesRes
MARS.Data.MessageBodyWriterswriters of plain JSON and XML for any TDataSet

MARS.Data.FireDAC uses the injection service and the readers/writers: adding it (and MARS.Data.MessageBodyWriters) to the uses of the ignition is enough. The MARS_FIREDAC define (in MARS.inc, on by default) compiles the FireDAC support; if your edition of Delphi has no FireDAC, remove it.

Enabling FireDAC ​

The ignition loads the connection definitions from the FireDAC section of the parameters and releases them at shutdown (the template already does it):

pascal
FAvailableConnectionDefs := TMARSFireDAC.LoadConnectionDefs(FEngine.Parameters, 'FireDAC');
// in the class destructor
TMARSFireDAC.CloseConnectionDefs(FAvailableConnectionDefs);

Each definition is a group of FireDAC.<name>.<parameter> keys, the parameters of a FireDAC connection definition (DriverID and the parameters of the driver):

ini
FireDAC.MAIN_DB.DriverID=FB
FireDAC.MAIN_DB.Server=localhost
FireDAC.MAIN_DB.Database=C:\Data\MARS.FDB
FireDAC.MAIN_DB.User_Name=SYSDBA
FireDAC.MAIN_DB.Password=secret
FireDAC.MAIN_DB.CharacterSet=UTF8
FireDAC.MAIN_DB.Pooled=True
FireDAC.MAIN_DB.POOL_MaximumItems=100

FireDAC.REPORTS.DriverID=SQLite
FireDAC.REPORTS.Database=C:\Data\reports.db

LoadConnectionDefs adds them to FDManager (a definition already there is left as it is) and returns the names of the ones it added. Link the FireDAC driver of each database, i.e. FireDAC.Phys.FB, FireDAC.Phys.MySQL, FireDAC.Phys.SQLite, FireDAC.Phys.MSSQL: without it the connection fails with driver not registered. MARS sets FDManager.SilentMode, so no wait cursor unit is needed. Pooled=True enables the FireDAC connection pool, recommended for a server.

Injecting a connection ​

pascal
[Path('customers')]
TCustomersResource = class
protected
  [Context] FD: TMARSFireDAC;                              // the default definition
  [Context, Connection('REPORTS')] Reports: TFDConnection; // a specific one
end;

[Connection('NAME')] can be on the field/property/parameter, on the method or on the resource; without it the definition is the FireDAC.ConnectionDefName parameter of the application (DefaultApp.FireDAC.ConnectionDefName), MAIN_DB by default. [Connection('Token_Claim_tenant', True)] takes the name from the request (see context values); FireDAC.ConnectionExpandMacros=True does the same for the parameter. The connection and the helper live as long as the request.

Class properties of TMARSFireDAC customize the connections:

pascal
// options that are not part of the definition, for every connection
TMARSFireDAC.AfterCreateConnection :=
  procedure (const AConnection: TFDConnection; const AActivation: IMARSActivation)
  begin
    AConnection.TxOptions.Isolation := xiReadCommitted;
  end;

// a TFDConnection descendant of yours
TMARSFireDAC.CustomFDConnectionClass := TMyFDConnection;

The TMARSFireDAC helper ​

MemberPurpose
Query(sql)Opens a TFDQuery owned by the request (freed after the response): return it from the method.
Query(sql, transaction, contextOwned, beforeOpen)The same, with a transaction, the ownership and a callback before Open (i.e. to set parameters).
Query(sql, transaction, beforeOpen, onReady)Opens, calls onReady and frees the query.
CreateQuery(sql, transaction, contextOwned, name)A TFDQuery not opened yet.
CreateCommand(sql, transaction, contextOwned)A TFDCommand.
ExecuteSQL(sql, transaction, before, after)Executes a command and returns the number of affected rows.
CreateTransaction(contextOwned), InTransaction(proc)Transactions (see below).
ApplyUpdates(datasets, deltas, onBeforeApplyUpdates)Applies the deltas of the client, returns one result for each delta.
InjectParamValues, InjectMacroValues, InjectMacroAndParamValuesFill parameters and macros from the request (CreateQuery and CreateCommand do it already).
SetName<T>(component, name)Names a dataset.
Connection, ConnectionDefName, ActivationThe connection (created on first use), its definition, the request.
GetContextValue(name, activation) (class)The value of a context value.
AddContextValueProvider(proc) (class)Context values of your own.

Returning datasets ​

pascal
[GET, Produces(TMediaType.APPLICATION_JSON), Produces(TMediaType.APPLICATION_XML), Produces(TMediaType.APPLICATION_JSON_FireDAC)]
function List([QueryParam('city')] const ACity: string): TFDQuery;
begin
  if ACity = '' then
    Result := FD.Query('select * from customers order by name')
  else
    Result := FD.Query('select * from customers where city = :QueryParam_city order by name');
end;
json
[{"ID":1,"NAME":"Ada Lovelace","CITY":"London","CREDIT":1500.0}, …]

Several datasets in one response: return TArray<TFDDataSet>, named with SetName (the name is the member of the response):

pascal
[GET, Path('summary')]
function Summary: TArray<TFDDataSet>;
begin
  Result := [
    FD.SetName<TFDQuery>(FD.Query('select * from customers'), 'Customers')
  , FD.SetName<TFDQuery>(FD.Query('select city, count(*) as customers from customers group by city'), 'Cities')
  ];
end;
json
{"Customers": [{…}, …], "Cities": [{…}, …]}

Field values follow the serialization options (dates, JSON.UseDisplayFormatForNumericFields); a null field is null, a field named with dots a nested object (see Writing datasets).

Parameters, macros and commands ​

Parameters and macros named after the request are filled automatically (context values): :PathParam_id, :QueryParam_city, :Token_UserName, !QueryParam_orderBy (a macro). Set the other ones in the before callback; read output parameters in the after one:

pascal
[POST, Consumes(TMediaType.APPLICATION_JSON)]
function Add([BodyParam] const ACustomer: TCustomer): TFDQuery;
var
  LId: Integer;
begin
  // Firebird: RETURNING ... {INTO :id} puts the new id in the id output parameter
  FD.ExecuteSQL('insert into customers (name, city) values (:name, :city) returning id {into :id}', nil,
    procedure (ACommand: TFDCommand)
    begin
      ACommand.ParamByName('name').AsString := ACustomer.name;
      ACommand.ParamByName('city').AsString := ACustomer.city;
      ACommand.ParamByName('id').ParamType := ptOutput;
      ACommand.ParamByName('id').DataType := ftInteger;
    end,
    procedure (ACommand: TFDCommand)
    begin
      LId := ACommand.ParamByName('id').AsInteger;
    end
  );
  Result := FD.Query('select * from customers where id = :id', nil, True,
    procedure (AQuery: TFDQuery)
    begin
      AQuery.ParamByName('id').AsInteger := LId;
    end
  );
end;

[DELETE, Path('{id}')]
procedure Delete;
begin
  if FD.ExecuteSQL('delete from customers where id = :PathParam_id') = 0 then
    raise EMARSHttpException.Create('Customer not found', 404);
end;

Without a transaction the statements run in auto-commit mode.

Stored procedures ​

CreateStoredProc, OpenStoredProc and ExecStoredProc work as CreateQuery, Query and ExecuteSQL for stored procedures. The procedure is prepared, so its parameters come from the database, and its input parameters named after the request are filled automatically, as for queries; output parameters are left alone.

pascal
// a procedure that returns rows (Firebird: a selectable one, with SUSPEND): a dataset, written
// as any other (JSON, the native formats of FireDAC)
[GET, Path('{id}/orders')]
function GetOrders: TFDStoredProc;
begin
  Result := FD.OpenStoredProc('CUSTOMER_ORDERS', nil,
    procedure (AProc: TFDStoredProc)
    begin
      AProc.ParamByName('CUSTOMER_ID').AsInteger := FId;
    end);
end;

// an executable procedure: its output parameters after the execution
[POST, Path('{id}/discount')]
function Discount: Double;
var
  LDiscount: Double;
begin
  FD.ExecStoredProc('APPLY_DISCOUNT', nil,
    procedure (AProc: TFDStoredProc)
    begin
      AProc.ParamByName('CUSTOMER_ID').AsInteger := FId;
    end,
    procedure (AProc: TFDStoredProc)
    begin
      LDiscount := AProc.ParamByName('DISCOUNT').AsFloat;
    end);
  Result := LDiscount;
end;

OpenStoredProc returns a stored procedure owned by the request (freed after it), as Query; ExecStoredProc frees its own and returns the rows affected, as the driver reports them. A TFDStoredProc is a FireDAC dataset: it is written (JSON, the native formats) as a TFDQuery.

Transactions ​

InTransaction runs a procedure in a new transaction: commit when it ends, rollback when it raises an exception (the exception reaches the client, i.e. an EMARSHttpException with its status):

pascal
[POST, Path('transfer')]
function Transfer: string;
begin
  FD.InTransaction(
    procedure (ATransaction: TFDTransaction)
    begin
      if FD.ExecuteSQL('update customers set credit = credit - :QueryParam_amount'
        + ' where id = :QueryParam_from and credit >= :QueryParam_amount', ATransaction) = 0 then
        raise EMARSHttpException.Create('Customer not found or insufficient credit', 409);
      if FD.ExecuteSQL('update customers set credit = credit + :QueryParam_amount'
        + ' where id = :QueryParam_to', ATransaction) = 0 then
        raise EMARSHttpException.Create('Customer not found', 404);
    end
  );
  Result := 'Transfer done';
end;

CreateTransaction gives a TFDTransaction to manage yourself (owned by the request unless AContextOwned = False); pass it to Query, CreateCommand, ExecuteSQL.

Editing data from the client (delta) ​

A Delphi client with TMARSFDResource receives datasets in the native format, keeps track of the changes in its TFDMemTables and sends back only the changed records, the delta. The server applies it with ApplyUpdates, which generates the insert/update/delete statements from the query of each dataset.

TMARSFDDatasetResource ​

Derive a resource from it and declare its datasets: GET returns them, POST applies the deltas.

pascal
[ Path('customersdata')
, SQLStatement('Customers', 'select * from customers order by name')
, SQLStatement('Cities', 'select city, count(*) as customers from customers group by city')
]
TCustomersDataResource = class(TMARSFDDatasetResource)
end;

Or override SetupStatements to build them in code:

pascal
procedure TOrdersResource.SetupStatements;
begin
  inherited; // the [SQLStatement] attributes, if any
  Statements.Add('Orders', 'select * from orders where customer_id = :QueryParam_customer');
  Statements.Add('Items', 'select * from order_items');
end;
  • GET …/customersdata returns the datasets (application/json or application/json-firedac).
  • POST …/customersdata with the deltas (application/json-firedac, the body TMARSFDResource sends) applies them and returns one TMARSFDApplyUpdatesRes for each delta:
json
[{"dataset": "Customers", "result": 0, "errorCount": 0, "errors": []}]

result is the value returned by ApplyUpdates (the number of errors), errors describes each rejected record. Virtual methods BeforeOpenDataSet and AfterOpenDataSet customize each query; the Connection and FD fields are available to derived classes.

Records added on the client with no value for an auto-incremental key (an identity column, i.e. generated by default as identity in Firebird) are inserted without it: the database assigns it.

Applying deltas in your own method ​

pascal
[POST, Consumes(TMediaType.APPLICATION_JSON_FireDAC)]
function Save([BodyParam] const ADeltas: TArray<TFDMemTable>): TArray<TMARSFDApplyUpdatesRes>;
begin
  Result := FD.ApplyUpdates(
    [ FD.SetName<TFDQuery>(FD.CreateQuery('select * from orders'), 'Orders') ]
  , ADeltas
  , procedure (ADataSet: TFDDataSet; ADelta: TFDMemTable)
    begin
      // before each dataset: checks, defaults...
    end
  );
end;

Each delta is matched by name to a dataset. MARS frees the deltas after the method.

TMARSFDDataModuleResource ​

A data module as a resource: design the queries in the IDE (with ConnectionName set to the name of the definition) and derive the data module from TMARSFDDataModuleResource. GET returns its published TFDDataSet fields, POST applies the deltas to them; BeforeApplyUpdates is called for each dataset. [RESTInclude], [RESTExclude] (on the fields) and [RESTIncludeDefault(False)] (on the class) choose the datasets.

Wire formats ​

Media typeConstantFormat
application/jsonTMediaType.APPLICATION_JSONan array of records (any client); an object with one array per dataset for TArray<TFDDataSet>
application/xmlTMediaType.APPLICATION_XML<dataset><row>…</row></dataset>
application/json-firedacTMediaType.APPLICATION_JSON_FireDACan object with one member per dataset: the FireDAC binary format (data, metadata, changes), zipped and Base64 encoded
application/xml-firedacTMediaType.APPLICATION_XML_FireDACthe FireDAC XML format of a single dataset
application/octet-streamTMediaType.APPLICATION_OCTET_STREAMthe FireDAC binary format of a single dataset

TFDDataSets (MARS.Data.FireDAC.Utils) encodes and decodes the native JSON format: ToJSON, FromJSON, DataSetToEncodedBinaryString, EncodedBinaryStringToDataSet.

See also ​

Released under the Mozilla Public License 2.0.