DWScript now has database support [1] classes built-in, these are based on a thin, interface-based layer, and can be used Delphi-side as well to get automatic memory management for simple DB access.
It currently supports mORMot [2] SynDB and Universal InterBase [3], meaning it gets high performance native connectivity to SQLite, FireBird, Oracle, ODBC and OleDB (MySQL, MS SQLServer, MS Jet, AS400…).
Usage
Usage is very simple, for instance to connect to a local SQLite database and print two fields for a table:
var db := DataBase.Create('SQLite', ['d:\db\base.sql3']);
var query := db.Query('select fld1, fld2 from mytable where fld3=?', ['filter']);
while query.Step do
PrintLn(query.AsString(0)+', '+query.AsString(1));
query.Close; // optional, only required if you need it closed ASAP
and to perform several queries in a transaction
db.BeginTransaction;
db.Exec('delete from mytable');
db.Exec('insert into mytable (fld1, fld3) values (?, ?)', ['hello', 'world']);
db.Commit;
The DataSet [4] class supports classic EOF/Next iteration as well as the simpler Step iteration as in the previous example.
There are also JSON [5] generation helpers, you can get a whole data set or a single record as JSON. This makes building ajax requests or even database “middle-ware” services simple.
Supporting other DB connectivity layers is quite simple, as you’ll see if you look in the source. SynDB & UIB were picked initially because together they offer high coverage of the usual suspects, and bring what are among, and maybe “the”, best in class performance and stability for Delphi DB connectivity these days.
Exemple: minimal web service
Those classes will be (are) used in leveraging the new “DWScript-returns-to-its-web-roots” Web Server (based on Synopse server [6]). For instance in the DWS WebServer demo, you can make a minimal database “middle-ware” service with Windows domain authentication with just the following code:
case WebRequest.Authentication of
WebAuthentication.None :
WebResponse.RequestAuthentication(WebAuthentication.Negotiate);
WebAuthentication.NTLM .. WebAuthentication.Kerberos :
Print(DataBase.Create('UIB', ['dbServer:d:\db.fdb', 'login', 'password'])
.Query(WebRequest.QueryString)
.StringifyAll);
else
WebResponse.StatusCode := 401;
end;
The above code will expect http requests with the sql as query string (ie. after the ‘?’ in the url). If the connection isn’t authenticated, it’ll ask for authentication. Note that Windows domain Single Sign On is supported by Chrome, Internet Explorer and FireFox (if you enabled it [7]).
The query is run against a FireBird [8] database hosted on another server, and the result returned as JSON. If the query fails for a reason or another, the client will get automatically get a 500 error code with the exception or error message. Oh, and the reply is compressed automatically if it’s larger than a couple hundred bytes.
And if you want to only serve on a secure connection, just add a check for WebRequest.Security, and you can then safely use extra or alternative authorization tokens or credentials.