HTTP remote access for SynDB SQL execution
For mORMot, we developed a fully feature direct access layer to any RDBMS, implemented in the SynDB.pas unit.
You can use those
SynDB classes to execute any SQL statement,
without any link to the framework ORM.
At reading, the resulting performance is much higher than using the standard
TDataSet component, which is in fact a true performance
It has genuine features, like column access via late-binding, an innovative
ISQLDBRows interface, and ability to directly access
the low-level binary buffers of the database clients.
We just added a nice feature to those classes: the ability to access
remotely, via plain HTTP, to any
SynDB supported database!
To define a HTTP server, you may write:
uses SynDB, // RDBMS core SynDBSQLite3, SynSQLite3Static, // static SQLite3 engine SynDBRemote; // for HTTP server ... var Props: TSQLDBConnectionProperties; HttpServer: TSQLDBServerAbstract; ... Props := TSQLDBSQLite3ConnectionProperties.Create('data.db3','','',''); HttpServer := TSQLDBServerHttpApi.Create(Props,'remote','8092','user','pass');
The above code will initialize a connection to a local
SQlite3 database (in the
Props variable), and then
publish it using the
http.sys kernel mode HTTP server to the
http://184.108.40.206:8092/remote URI - if the server's IP is
On the client side, you can then write:
uses SynDB, // RDBMS core SynDBRemote; // for HTTP server ... var Props: TSQLDBConnectionProperties; ... Props := TSQLDBWinHTTPConnectionProperties.Create('220.127.116.11:8092','root','user','pass');
As you can see, there is no link to
SynSQLite3Static.pas on the client side.
Just the HTTP link is needed.
No need to deploy the RDBMS client libraries with your application, nor setup the local network firewall.
We defined here a single user, with 'user' / 'pass' credentials, but you may
manage more users on the server side, using the
Note that in our remote access, user management does not match the RDBMS user rights: you should better have your own set of users at application level, for higher security, and a better integration with your business logic. If creating a new user on a RDBMS could be painful, it is pretty easy on our
Then, you execute your favorite SQL using the connection just as usual:
procedure Test(Props: TSQLDBConnectionProperties); var Stmt: ISQLDBRows; begin Stmt := Props.Execute('select * from People where YearOfDeath=?',); while Stmt.Step do begin assert(Stmt.ColumnInt('ID')>0); assert(Stmt.ColumnInt('YearOfDeath')=1519); end; end;
You may even use this remote connection e.g. using a stand-alone shared SQLite3 database as high performance but low maintenance client-server database engine.
The transmission protocol uses an optimized binary format, which is
compressed and digitally signed on both ends, and the remote user
authentication will be performed via a challenge validation scheme.
You could publish your server over HTTPS, if needed.
Even if you may be tempted to use such remote access to implement a
n-Tier architecture, you should rather use mORMot's
Client-Server ORM instead. Our little mORMot is not an ORM on
which we added a data transmission layer: it is a full RESTful system, with a
true SOA design.
But for integrating some legacy SQL code into a new architecture,
SynDBRemote.pas may have its benefits, used in conjunction with
mORMot's higher level features.