Creating RemoteDB Server

The following topics explain how to create a new TMS RemoteDB server and how to configure it.

Ways to Create the RemoteDB Server

You have four different ways to create a RemoteDB Server app, as follows.

RemoteDB Server Wizard

The easiest and more straightforward way to get started with RemoteDB is using the wizard.

1. From Delphi IDE, choose File > New > Other and then look for the "TMS RemoteDB" category under "Delphi Projects".

2. There you find the following wizard to create a new XData Server Application:

   TMS RemoteDB VCL Server: Creates a VCL application that runs a RemoteDB server using http.sys

3. Choose the wizard you want, double-click and the application will be created.

The wizard will create the design-time components for you. You still need to drop the database-access component to be used to connect to the database - e.g., TFDConnection (FireDac), TUniConnection (UniDac), TSQLConnection (dbExpress), etc. - and then associated it to the TAureliusConnection component.

You can also create the server manually, using design-time components or from non-visual code.

Using Design-Time Components

Another way to create a TRemoteDBServer is by using the design-time components. If you want the RAD, component dropping approach, this is the way to go.

1. Drop a dispatcher component on the form (for example, TSparkeHttpSysDispatcher);

2. Drop a TRemoteDBServer component on the form;

3. Associate the TRemoteDBServer component with the dispatcher through the Dispatcher property;

4. Specify the BaseUrl property of the server (for example, http://+:2001/tms/remotedb);

5. Set the Active property of the dispatcher component to true;

6. Drop a TAureliusConnection on the form and configure it so that it connects to your database (you will need to drop additional database-access components, e.g. TFDConnection if you want to use FireDac, and then associate it to the TAureliusConnection.AdaptedConnection).

7. Associate the TRemoteDBServer component to the Aurelius connection through the Connection property.

That is enough to have your RemoteDB server up and running!

Legacy Wizard for RemoteDB Server

There is a legacy wizard which don't use design-time components but you can still use.

To create a new RemoteDB Server using the legacy wizard:

1. Choose File > New > Other and then look for "TMS Business" category under "Delphi Projects". Then double click "TMS RemoteDB Server".

2. Chose the kind of applications you want to server to run on, then click Next. Available options are VCL, FMX, Service and Console. You can choose multiple ones (for example, you can have a VCL project for quick test the server and a Service one to later install it in production, both sharing common code.

3. Chose the Host Name, Port and Path for the server, then click Next. Usually you don't need to change the host name, for more info check URL namespace and reservation. Port and Path form the rest of base URL where your server will be reached.

4. Select the Driver (component to access database) and the SQL Dialect (type of database server), then click Create.

The new server will be created and ready to run.

Creating the Server Manually

If you don't want to use the RemoteDB Server wizard and do not want to use design-time components, you can create a server manually, from code. This topic describes how to do it, and it's also a reference for you to understand the code used "behind-the-scenes" by the design-time components.

TMS RemoteDB is based on TMS Sparkle framework. The actual RemoteDB Server is a Sparkle server module that you add to the Sparkle Http Server.

Please refer to the following topics to learn more about TMS Sparkle servers:

• Overview of TMS Sparkle Http Server

• Creating an Http Server to listen for requests

• TMS Sparkle Server Modules

To create the RemoteDB Server, just create and add a RemoteDB Server Module (TRemoteDBModule object, declared in unit RemoteDB.Server.Module) to the Sparkle Http Server. To create the RemoteDB Module, you just need to pass the base URL address of the server, and an IDBConnectionFactory interface so that the server can create connections to the actual SQL database server. Here is an example (try..finally blocks removed to improve readability):

uses
  {...}, 

  Sparkle.HttpSys.Server, RemoteDB.Drivers.Base, 
  RemoteDB.Drivers.Interfaces, RemoteDB.Server.Module;

function CreateNewIDBConnection: IDBConnection;
var
  SQLConn: TSQLConnection;
begin
  // Create the IDBConnection interface here
  // Be sure to also create a new instance of the database-access component here
  // Two different IDBConnection interfaces should not share the same database-access component

  // Example using dbExpress
  SQLConn := TSQLConnection.Create(nil);


  { Define SQLConn connection settings here, the server
     to be connected, user name, password, database, etc. }
  Result := TDBExpressConnectionAdapter.Create(SQLConn, true);
end;

var
  Module: TRemoteDBModule;
  Server: THttpSysServer;
begin
  Server := THttpSysServer.Create;
  Module := TRemoteDBModule.Create('http://localhost:2001/tms/business/remotedb',
    TDBConnectionFactory.Create(
      function: IDBConnection
      begin
        Result := CreateNewIDBConnection;
      end
    ));
  Server.AddModule(Module);
  Server.Start;
  ReadLn;
  Server.Stop;
  Server.Free;
end;

The code above will create a new RemoteDB server which base address is http://localhost:2001/tms/business/remotedb. That's the address clients should use to connect to the server. The server will use dbExpress to connect to the database, and the TSQLConnection component must be properly configured in the CreateNewIDBConnection function.

There are many other ways to create the IDBConnection interface, including using existing TDataModule. You can refer to the following topics for more info.

IDBConnectionFactory Interface

The IDBConnectionFactory interface is the main interface needed by the RemoteDB server to work properly. As client requests arrive, RemoteDB Server might need to create a new instance of a database-access component in order to connect to the database. It does that by calling IDBConnectionFactory.CreateConnection method to retrieve a newly created IDBConnection interface, which it will actually use to connect to database.

To create the factory interface, you just need to pass an anonymous method that creates and returns a new IDBConnection interface each time it's called.

uses 
  {...}, RemoteDB.Drivers.Base;

var
  ConnectionFactory: IDBConnectionFactory;
begin
  ConnectionFactory := TDBConnectionFactory.Create(
      function: IDBConnection
      var
        SQLConn: TSQLConnection;
      begin
        // Create the IDBConnection interface here
        // Be sure to also create a new instance of the database-access component here
        // Two different IDBConnection interfaces should not share the same database-access component

        // Example using dbExpress
        SQLConn := TSQLConnection.Create(nil);
        { Define SQLConn connection settings here, the server
           to be connected, user name, password, database, etc. }
        Result := TDBExpressConnectionAdapter.Create(SQLConn, true);
      end
    ));

  // Use the ConnectionFactory interface to create a RemoteDB Server
end;

It's possible that you already have your database-access component configured in a TDataModule and you don't want to create it from code. In this case, you can just create a new instance of the data module and return the IDBConnection associated to the component. But you must be sure to destroy the data module (not only the database-access component) to avoid memory leaks:

var
  ConnectionFactory: IDBConnectionFactory;
begin
  ConnectionFactory := TDBConnectionFactory.Create(
      function: IDBConnection
      var
        MyDataModule: TMyDataModule;
      begin
        MyDataModule := TMyDataModule.Create(nil);
        // The second parameter makes sure the data module will be destroyed
        // when IDBConnection interface is released
        Result := TDBExpressConnectionAdapter.Create(MyDataModule.SQLConnection1, MyDataModule);
      end
    ));

  // Use the ConnectionFactory interface to create a RemoteDB Server
end;

IDBConnection Interface

The IDBConnection interface represents a connection to a database in RemoteDB. Every connection to a database in the server is represented uses this interface to send and receive data from/to the database.

IDBConnection wraps the data access component you are using, making it transparent for the framework. Thus, regardless if you connect to the database using dbExpress, ADO, IBX, etc., you just need an IDBConnection interface.

To obtain an IDBConnection interface you use existing adapters (drivers) in RemoteDB. The adapters just take an existing data access component (TSQLConnection, TADOConnection, etc.) and give you back the IDBConnection interface you need to use. To create database connections it's important to know the available:

• Component Adapters

• SQL Dialects

In summary, to obtain an IDBConnection interface:

1. Create and configure (or even use an existing one) component that makes a connection to your database.

If you use dbExpress, for example, you need to create a TSQLConnection component, and create the adapter that wraps it:

function CreateDBExpressConnection: TSQLConnection;
begin
  Result := TSQLConnection.Create(nil);
  // Configure Result with proper connection settings
  // Don't forget setting LoginPrompt to false
end;

2. Instantiate an adapter passing the connection component.

function CreateIDBConnection: IDBConnection;
var

begin
  MyConnection := TDBExpressConnectionAdapter.Create(CreateDBExpressConnection, True);
  // return the newly created IDBConnection to the caller
  Result := MyConnection;
end;

Note the second parameter when calling Create constructor. It indicates that when IDBConnection interface is destroyed, the wrapped TSQLConnection component is also destroyed.

For more information about how to create adapters, see Component Adapters.

Component Adapters

There is an adapter for each data-access component. For dbExpress, for example, you have TDBExpressConnectionAdapter, which is declared in unit RemoteDB.Drivers.dbExpress. All adapters are declared in unit RemoteDB.Drivers.XXX where XXX is the name of data-access technology you're using. You can create your own adapter by implementing IDBConnection interfaces, but RemoteDB already has the following adapters available:

Technology	Adapter class	Declared in unit	Adapted Component	Vendor Site
Advantage	TAdvantageConnectionAdapter	RemoteDB.Drivers.Advantage	TAdsConnection	http://www.sybase.com
dbExpress	TDBExpressConnectionAdapter	RemoteDB.Drivers.dbExpress	TSQLConnection	Delphi Native
dbGo (ADO)	TDbGoConnectionAdapter	RemoteDB.Drivers.dbGo	TADOConnection	Delphi Native
ElevateDB	TElevateDBConnectionAdapter	RemoteDB.Drivers.ElevateDB	TEDBDatabase	http://elevatesoftware.com/
FireDac	TFireDacConnectionAdapter	RemoteDB.Drivers.FireDac	TFDConnection	Delphi native
NexusDB	TNexusDBConnectionAdapter	RemoteDB.Drivers.NexusDB	TnxDatabase	http://www.nexusdb.com
SQL-Direct	TSQLDirectConnectionAdapter	RemoteDB.Drivers.SqlDirect	TSDDatabase	http://www.sqldirect-soft.com
UniDac	TUniDacConnectionAdapter	RemoteDB.Drivers.UniDac	TUniConnection	http://www.devart.com/unidac

You can also use native database drivers:

Database	Driver Name	Connection class	Declared in unit
Microsoft SQL Server	MSSQL	TMSSQLConnection	Aurelius.Drivers.MSSQL

For more information on using native drivers, please refer to TMS Aurelius documentation.

Creating the adapter

To create the adapter, you just need to instantiate it, passing an instance of the component to be adapted. In the example below, a dbExpress adapter constructor receives a TSQLConnection component.

MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, True);

The adapter usually detects the SQL Dialect automatically, but you can force the adapter to use a specific dialect, using one of the following overloaded constructors.

Note that for RemoteDB, the SQLConnection1 cannot be shared between different IDBConnection interfaces. Thus, you must create one database-access component for each IDBConnection interface you create.

Overloaded constructors

There are some overloaded versions of the constructor for all adapters:

constructor Create(AConnection: T; AOwnsConnection: boolean); overload; virtual;

constructor Create(AConnection: T; ASQLDialect: string; AOwnsConnection: boolean); overload; virtual;

constructor Create(AConnection: T; OwnedComponent: TComponent); overload; virtual;

constructor Create(AConnection: T; ASQLDialect: string; OwnedComponent: TComponent); overload; virtual;

• AConnection: specify the database-access component to be adapted.

• AOwnsConnection: if true, the component specified in AConnection parameter will be destroyed when the IDBConnection interface is released. If false, the component will stay in memory.

• ASQLDialect: defines the SQL dialect to use when using this connection. If not specified, Aurelius will try to discover the SQL Dialect based on the settings in the component being adapted.

• OwnedComponent: specifies the component to be destroyed when the IDBConnection interface is released. This is useful when using data modules (see below).

Memory Management

Note the second boolean parameter in the Create constructor of the adapter. It indicates if the underlying connection component will be destroyed when the IDBConnection interface is destroyed. This approach is borrowed from TMS Aurelius, but for RemoteDB, you should not keep the component alive after the IDBConnection interface is released. Always destroy the component with the interface (parameter must be true).

In the example above ("Creating the adapter"), the SQLConnection1 component will be destroyed after MyConnection interface is out of scope and released. Quick examples below:

var
  MyConnection: IDBConnection;
begin
  MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, True);
  // ...
  MyConnection := nil;
  { MyConection is nil, the TDBExpressConnectionAdapter component is destroyed, 
    and SQLconnection1 is also destroyed}    
end;

Alternatively, you can inform a different component to be destroyed when the interface is released. This is useful when you want to create an instance of a TDataModule (or TForm) and use an adapted component that is owned by it. For example:

MyDataModule := TConnectionDataModule.Create(nil);
MyConnection := TDBExpressConnectionAdapter.Create(MyDataModule.SQLConnection1, MyDataModule);

The previous code will create a new instance of data module TConnectionDataModule, then create a IDBConnection by adapting the SQLConnection1 component that is in the data module. When MyConnection is released, the data module (MyDataModule) will be destroyed (and in turn, the SQLConnection1 component will be destroyed as well). This is useful if you want to setup the connection settings at design-time, or have an existing TDataModule with the database-access component already properly configured. Then you just use the code above in RemoteDB server to create one IDBConnection for each instance of the data module.

dbGo (ADO) Adapter

Currently dbGo (ADO) is only officially supported when connecting to Microsoft SQL Server databases. Drivers for other databases might work but were not tested.

SQL Dialects

When creating an IDBConnection interface using an adapter, you can specify the SQL dialect of the database server that RemoteDB server connects to.

Currently this is only used by TMS Aurelius and does not affect RemoteDB behavior if you are just using TXDataset and not using any Aurelius clases. But RemoteDB might need it in future for some operations, so we suggest you create the server passing the correct SQL Dialect.

When you create an IDBConnection interface using a component adapter, usually the adapter will automatically retrieve the correct SQL dialect to use. For example, if you are using dbExpress components, the adapter will look to the DriverName property and tell which db server you are using, and then define the correct SQL dialect name that should be used.

Note that in some situations, the adapter is not able to identify the correct dialect. It can happen, for example, when you are using ODBC or just another data access component in which is not possible to tell which database server the component is trying to access. In this case, when creating the adapter, you can use an overloaded constructor that allows you to specify the SQL dialect to use:

MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, 'MSSQL', False);

The following table lists the valid SQL Dialect strings you can use in this case.

SQL dialect	String identifier	Database Web Site
Advantage	Advantage	http://www.sybase.com
DB2	DB2	http://www.ibm.com
ElevateDB	ElevateDB	http://www.elevatesoftware.com
Firebird	Firebird	http://www.firebirdsql.org
Interbase	Interbase	http://www.embarcadero.com
Microsoft SQL Server	MSSQL	http://www.microsoft.com/sqlserver
MySQL	MySQL	http://www.mysql.com
NexusDB	NexusDB	http://www.nexusdb.com
Oracle	Oracle	http://www.oracle.com
PostgreSQL	PostgreSQL	http://www.postgresql.org
SQLite	SQLite	http://www.sqlite.org

TRemoteDBModule settings

Before creating the RemoteDB Server by adding the TRemoteDBModule object to the Http Server, you can use some of its properties to configure the server module.

Basic authentication properties

property UserName: string;
property Password: string;

Use these properties to specify UserName and Password required by the server, using Basic authentication. By default, the values are: UserName = remotedb, Password = business. Since basic authentication is used, be sure to use Http secure (Https) if you don't want your user name/password to be retrieved by middle-man attack. If you don't use Http secure, user name and password are transmitted in plain text in http requests.

Instance Timeout

property InstanceTimeout: integer;

TMS RemoteDB Server keeps database-access components in memory while clients are connected. It will destroy everything when client is normally closed. However, there might be situations where the client crashes and never notifies the server to destroy the database-access component. In this case, the server will eventually destroy the component after the instance timeout is reached, i.e., the time which the component didn't receive any request from the client.

This value must be specified in milliseconds, and default value is 60000 (1 minute).

TRemoteDBServer Component

TRemoteDBServer component wraps the TRemoteDBModule module to be used at design-time.

Properties

Name	Description
Connection: TAureliusConnection	Contains a reference to a TAureliusConnection component. This will be used as the connection factory for the TRemoteDB server-side database instances.
DefaultInstanceTimeout: Integer	Defines for how long a connection component should stay alive in the server without any contact from the client. After such time, the instance will be destroyed and any further request from the client will recreate a new component.
UserName: string; Password: string;	Defines user name and password to be used for Basic authentication.

Events

OnModuleCreate: TRemoteDBModuleEvent

Fired when the TRemoteDBModule instance is created.

TRemoteDBModuleEvent = procedure(Sender: TObject; Module: TRemoteDBModule) of object;

Module parameter is the newly created TRemoteDBModule instance.

Server-Side Events

TRemoteDBServer (and TRemoteDBModule) publish several events that you can use to monitor what's going on server-side. The only difference between the two is that TRemoteDBServer includes a "Sender" parameter of type TObject, which is the standard for design-time events. All the other parameters are the same for TRemoteDBServer and TRemoteDBModule. Of course, you can define event-handlers for TRemoteDB server from the IDE (since it's a design-time component), and for TRemoteDBModule you need to set it from code.

The events are mostly used for logging purposes.

RemoteDB events

OnDatabaseCreate and OnDatabaseDestroy

OnDatabaseCreate event is fired right after a database-access component is created in the server (from a call to IDBConnectionFactory interface). On the other hand, OnDatabaseDestroy is called right before the component is about to be destroyed. Event signature is the following:

procedure(Sender: TObject; Info: IDatabaseInfo)

Look below to see what's available in IDatabaseInfo interface.

BeforeStatement and AfterStatement

BeforeStatement and AfterStatement events are fired right before (or after) an SQL statement is executed server-side. Event signature is the following:

procedure(Sender: TObject; Info: IStatementInfo)

Look below to see what's available in IStatementInfo interface. It's worth noting that when the SQL statement execution raises an exception, BeforeStatement event is fired, but AfterStatement is not.

IDatabaseInfo interface

Represents a database connection in the server. The following properties are available.

Name	Description
Id: string	The internal Id for the database connection.
LastAccessed: TDateTime	The last time (in server local time zone) the connection was requested (used) by the client.
ClientID: string	The ID of the client which created the connection.
ClientIP: string	The IP address of the client which created the connection.
Connection: IDBConnection	The underlying IDBConnection interface used to connect to the database.

IStatementInfo interface

Represents the SQL statement being executed. The following properties are available.

Name	Description
Database: IDatabaseInfo	The IDatabaseInfo interface (database connection) associated with the statement being executed.
Sql: string	The SQL statement to be executed.
Params: TEnumerable<TDBParam>	The parameters to be bound to SQL statement. TDBParam is a TMS Aurelius object which contain properties ParamName, ParamType and ParamValue.
Operation: TStatementOperation	The type of operation being performed with the statement. It can be one of the these values. Note that depending on client behavior, statement-related events can be fired more than once: one with FieldDefs operation (to retrieve SQL fields) and then a second one with Open operation, to return actual data. Sometimes, a single operation that does both will be executed (FieldDefsOpen).
Dataset: TDataset	The underlying TDataset component used to retrieve data. Note Dataset can be nil (in the case of Execute operation, for example).

TStatementOperation

• Open: Execution of a statement that returns data (SELECT).

• Execute: Execution of a statement that does not return data (INSERT, UPDATE, DELETE).

• FieldDefs: Retrieval of field definitions of a statement that returns data. The SQL will not be actually executed.

• FieldDefsOpen: Retrieval of field definitions and data return.

Administration API

RemoteDB provides an administration API that helps you to know status of existing database connections in the server and drop existing connections, if needed. The API is disabled by default. To enable, you have to set EnableAPI property to true (in either TRemoteDBServer or TRemoteDBModule):

Module.EnableApi := True;

The API provides the following endpoints (relative to server base URL):

Retrieve database connections

GET api/databases

Returns a JSON array with information about the existing database connections. For example:

[
    {
        "Id": "8FFDF133-286E-4C04-94D0-4479342FE389",
        "LastAccess": "2019-07-04T18:50:41.068Z",
        "ClientId": "Client A",
        "ClientIP": "::1",
        "Connected": true,
        "InTransaction": false
    }
]

Drop existing connection

DELETE api/databases/{id}

Drops an existing database connection, identified by is Id.