RemoteDB Client Applications
The following topics provide detailed info about how to create Delphi client applications that connect to a TMS RemoteDB Server.
TRemoteDBDatabase Component
TRemoteDBDatabase component is the one you should use to configure connection settings to a RemoteDB Server. Once you have created and configured the component, you can link any TXDataset to it to execute SQL statements in the RemoteDB server. It's a regular Delphi component so you can drop it in a form or data module to use it.
Usage example:
uses
{...}, RemoteDB.Client.Database;
function CreateRemoteDBDatabase: TRemoteDBDatabase;
var
begin
Result := TRemoteDBDatabase.Create(nil);
Result.ServerUri := 'http://localhost:2001/tms/business/remotedb/';
Result.UserName := 'remotedb';
Result.Password := 'business';
Result.Connected := true;
end;
Key Properties
property ServerUri: string;
Specifies the Url of the RemoteDB server.
property Connected: boolean;
Set this property to true to establish a connection to the RemoteDB server. As most of Delphi database components, TRemoteDBDatabase component will try to automatically connect to the server when a TXDataset tries to execute an SQL statement.
property UserName: string;
property Password: string;
Defines the UserName and Password to be used to connect to the server. These properties are initially set with the default values (remotedb:business). In production environment, build a server with different values, set these properties accordingly to make a connection, and use Https to ensure user name and password are encrypted in client/server communications.
property Timeout: integer;
Defines the lifetime of inactive server-side database objects. The server-side database connection will be kept alive while the client keeps sending requests to it. If the client suddenly interrupts the requests without explicitly destroying the database, the object will remain in the server for the period specified by the server by default. You can use Timeout property to define such time at client-side in a per-database manner. Value must be in milliseconds.
property ClientID: string;
Defines a value to identity the current client. This is useful to identify the client from the server. Such information is avaliable, for example, in server-side events, or administration API, to identify the client ID associated with an existing database connection.
Key Methods
procedure BeginTransaction;
procedure Commit;
procedure Rollback;
function InTransaction: boolean;
Use the above methods to start, commit and rollback transactions, respectively. The InTransaction property allows you to check if a transaction is already active. Only a single transaction can exist per database component.
Key Events
TRemoteDBHttpClientEvent = procedure(Sender: TObject; Client: THttpClient) of object;
property OnHttpClientCreate: TRemoteDBHttpClientEvent
OnHttpClientCreate event is fired when a new Sparkle THttpClient object is created by the RemoteDB database. THttpClient is used for the low-level HTTP communication with RemoteDB server and this event is an opportunity to do any custom configuration you want in that object.
TGenericDatabase Component
TGenericDatabase component is used if you want to connect to your database server directly, in a traditional client-server architecture, using your preferred database-access component through component adapters.
By switching the Database property of a TXDataset between TGenericDatabase and TRemoteDBDatabase you can easily build one single client application that can communicate directly with the database server in traditional client-server approach (TGenericDatabase) and communicate with a RemoteDB server through http (TRemoteDBDatabase). And you can do that without needing to change the dataset component you use to access the local or remote database: just use the same TXDataset component.
TGenericDatabase doesn't actually contains the code to connect to the database. It's just a wrapper for the component adapter. To use it, you need to set its Connection property to point to an IDBConnection interface.
Usage example using FireDac to connect to the database:
uses
{...}, RemoteDB.Client.GenericDatabase, RemoteDB.Drivers.FireDac;
function CreateGenericDBDatabase: TGenericDatabase;
var
begin
Result := TGenericDatabase.Create(nil);
Result.Connection := TFireDacConnectionAdapter.Create(FDConection1, False);
Result.Connected := true;
end;
Key Properties
property Connection: IDBConnection;
Specifies the IDBConnection interface used to connect to the database.
property Connected: boolean;
Set this property to true to establish a connection to the database server. Available at runtime.
Key Methods
procedure BeginTransaction;
procedure Commit;
procedure Rollback;
function InTransaction: boolean;
Use the above methods to start, commit and rollback transactions, respectively. The InTransaction property allows you to check if a transaction is already active. Only a single transaction can exist per database component.
TXDataset Component
TXDataset is the main component you will use to perform SQL statements on the RemoteDB Server. It's a TDataset descendant so you can use it anywhere in your Delphi application that supports TDataset components.
Its usage is very similar to any TDataset component:
Set the Database property to the property TRemoteDBDatabase component to be used.
Use the SQL property to define the SQL statement.
Use Params property to define SQL param values, if any.
Create persistent TField components, if needed.
Open the dataset to retrieve results (for SELECT statements) or call Execute method to execute the SQL statement
Simple example:
uses
{...}, RemoteDB.Client.Dataset;
var
Dataset := TXDataset.Create(Self);
begin
Dataset.Database := RemoteDBDatabase1;
Dataset.SQL.Text := 'Select * from Customers';
Dataset.Open;
while not Dataset.EOF do
begin
{process}
Dataset.Next;
end;
Dataset.Close;
Dataset.Free;
end;
See additional topics about TXDataset below.
Updating Records
When you call Post or Delete methods in TXDataset, data is modified in the internal database buffer, but no modification is automatically done directly in database. The only way to modify data is by executing INSERT, UPDATE or DELETE SQL statements. You have two options here: let RemoteDB do it automatically for you, using AutoApply property, or do it manually using events.
Automatic update
You can set TXDataset.AutoApply property to true (default is false) to let RemoteDB automatically perform SQL statements to modify data when data is posted or deleted in the dataset.
There is only one thing you need to define manually: tell RemoteDB what are the key (primary key) fields of the table. This way it can build the proper WHERE clause when executing the SQL statements.
You have two ways for doing that:
- Use TXDataset.KeyFields property, providing the key field names in a semicolon-separated list.
or
- Set pfInKey flag in ProviderFlags property of dataset persistent fields.
In summary, use the following properties (you can also set at desing-time):
XDataset1.AutoApply := True;
XDataset1.KeyFields := 'Id';
RemoteDB will automatically try to retrieve the name of the table to be updated, from the SQL statement. It might not be able to do it in some more complex SQL statements. In this case you can provide the name of the table to be udpated using UpdateTableName property:
XDataset1.UpdateTableName := 'Customers';
Updating manually using events
When AutoApply is false (default), calls to Post and Delete update the in-memory cache, but do not perform any SQL update/insert/delete operation on the RemoteDB Server (and, in turn, in the SQL database server).
In this mode you have more flexibility to perform updates, but then you must manually provide the code to perform such operations, using TXData events:
OnRecordInsert
OnRecordUpdate
OnRecordDelete
Those events are called in the proper time you need to execute the SQL statement to respectively INSERT, UPDATE and DELETE a record in the database server.
property ModifiedFields: TList<TField>;
You can then use ModifiedFields property to verify which fields were modified by the dataset. It can be useful in case you want to perform UPDATE or INSERT SQL statements and only update/insert the fields modified by the user.
You also need to execute the SQL statements yourself, either using a different TXDataset component for that, or calling TRemoteDBDatabase.ExecSQL method directly.
Master-Detail Setup
You can setup a master-detail relationship between two TXDataset components using the Datasource property. That property behaves as specified in Delphi documentation. According to this source:
Setting DataSource property will automatically fill parameters in a query with field values from another dataset. Parameters that have the same name as the fields in the other dataset are filled with the field values. Parameters with names that are not the same as the fields in the other dataset do not automatically get values, and must be programmatically set. For example, if the SQL property of the TXDataset contains the SQL statement below and the dataset referenced through DataSource has a CustNo field, the value from the current record in that other dataset is used in the CustNo parameter:
SELECT *
FROM Orders O
WHERE (O.CustNo = :CustNo)
Other Methods and Properties
This topic lists some key methods and properties of TXDataset component, in addition to those inherited from TDataset component.
procedure Execute;
Executes an SQL for data modification (Insert, Delete, Update statements).
procedure FetchAllRecords;
Retrieves all remaining records for the dataset and closes the internal data provider.
property ModifiedFields: TList<TField>;
Provides a list of fields which values were modified. Useful in record update operations.
property SQL: TStrings;
Contains the SQL statement to be executed in the server.
property Params: TParams;
Use to to define the values for the params in SQL statement. Params should be declared in SQL using commands (:paramname). The list of params is updated automatically when you change the SQL property.
property DataSource: TDataSource;
Defines the datasource where param values will be automatically retrieved. Used to setup master-detail datasets.
property Database: TXDatabase;
Points to the database (usually TRemoteDBDatabase) where the SQL statements will be executed to.
property OnRecordInsert: TNotifyEvent;
property OnRecordUpdate: TNotifyEvent;
property OnRecordDelete: TNotifyEvent;
Events for updating records.
property Unidirectional: boolean;
Set Unidirectional property to true to save memory and increase performance if you are going to retrieve records from the dataset in a unidirectional way. If Unidirectional is set to true and you try to return to a previous record (using First or Prior methods for example), an error will be raised.
Batch Updates
If you want to insert, update or delete several records at the same time, using the same SQL statement, you can use the batch update feature - also known as Array DML.
In this mode, a single SQL statement is sent to the server, and multiple values are passed for each parameter. For example, consider the following SQL statement:
XDataset1.SQL.Text := 'INSERT INTO Cities (Id, Name) VALUES (:Id, :Name)';
If you want to insert three records using the same statement, this is how you should do it:
XDataset1.ParamByName('Id').DataType := ftInteger;
XDataset1.ParamByName('Name').DataType := ftString;
XDataset1.Params.ArraySize := 3;
XDataset1.ParamByName('Id').Values[0] := 1;
XDataset1.ParamByName('Name').Values[0] := 'London';
XDataset1.ParamByName('Id').Values[1] := 2;
XDataset1.ParamByName('Name').Values[1] := 'New York';
XDataset1.ParamByName('Id').Values[2] := 3;
XDataset1.ParamByName('Name').Values[2] := 'Rio de Janeiro';
XDataset1.Execute;
The advantage of this approach is that a single HTTP request containing the SQL statement and all parameters will be send to the server. This increases performance, especially on environments with high latency.
In addition to that, if the database-access component you are using server-side supports Array DML (like FireDAC or UniDAC), then it will also increase performance server-side significantly, by also using Array DML to actually save data in the database. Otherwise, a batch update will be simulated, by preparing the SQL statement and executing it for each row of parameters.
Connecting TMS Aurelius to RemoteDB Server
From the client application, you can use TMS Aurelius to retrieve objects from a RemoteDB server, instead of a regular database server. All you need is to use the proper Aurelius IDBConnection interface. Just like TMS Aurelius can connect to SQL databases using FireDac, dbExpress, etc., it also provides a RemoteDB driver adapter which you can use to perform the database connection. Once you do that, Aurelius usage is exactly the same as with any IDBConnection and you can create a TObjectManager object and use Find, Update, Delete methods, perform queries, and other operations.
Aurelius provides an adapter for a TRemoteDBDatabase component, which in turn is used to connect to a RemoteDB server. The following code illustrates how to use it, for more information please refer to the Component Adapters topic in TMS Aurelius documentation.
uses
RemoteDB.Client.Database, Aurelius.Drivers.RemoteDB;
function CreateClientConnection: IDBConnection;
var
XDB: TRemoteDBDatabase;
begin
XDB := TRemoteDBDatabase.Create(nil);
Result := TRemoteDBConnectionAdapter.Create(XDB, true);
XDB.ServerUri := 'http://localhost:2001/tms/business/remotedb/';
end;
{ Aurelius usage is exactly the same }
Connection := CreateClientConnection;
Manager := TObjectManager.Create(Connection);
Customers := Manager.Find<TCustomer>.List;
Note that in client you don't need (and shouldn't) create both TRemoteDBDatabase and IDBConnection for each manager you use. The code above is just an explanation about how to create those classes. In a real client application, you would create the IDBConnection interface and share it among different TObjectManager instances.