You will find in the SQLite3\Sample\16 - Execute SQL via
services folder of mORMot source code a Client-Server sample
able to access any external database via JSON and HTTP.
It is a good demonstration of how to use an interface-based service between
a client and a server.
It will also show how our SynDB classes have a quite abstract
design, and are easy to work with, whatever database provider you need to
use.
The corresponding service contract has been defined:
TRemoteSQLEngine = (rseOleDB, rseODBC, rseOracle, rseSQlite3, rseJet, rseMSSQL);
IRemoteSQL = interface(IInvokable) ['{9A60C8ED-CEB2-4E09-87D4-4A16F496E5FE}'] procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); function GetTableNames: TRawUTF8DynArray; function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON; end;
Purpose of this service is:
- To Connect() to external databases, given the parameters of a
standard TSQLDBConnectionProperties. Create() constructor;
- Retrieve all table names of this external database as a list;
- Execute any SQL statement, returning the content as JSON array, ready to be
consumed by AJAX applications (if aExpanded is true),
or a Delphi client (e.g. via a TSQLTableJSON and the
mORMotUI unit).
Of course, this service will be define as sicClientDriven mode,
that is, the framework will be able to manage a client-driven
TSQLDBProperties instance life time.
Benefit of this service is that no database connection is required on the
client side: a regular HTTP connection is enough.
No need to install nor configure any database provider, and full SQL access to
the remote databases.
Due to our optimized JSON serialization, it will probably be faster to work with such plain HTTP / JSON services, instead of a database connection through a VPN. In fact, database connections are made to work on a local network, and do not like high-latency connections, which are typical on the Internet.
