mORMot Delphi REST Server without ORM

Ozim16 · 2018-02-12 22:01:22

Hello everyone,

We are working on a project where we would like to have a Server and a Client. Both will be in Delphi, the Server is a VCL application (should be for Windows), and the Client is a FireMonkey application.
First we tested DataSnap, but then we found out that it wasn't very performant and scalable, and because we will need to handle a lot of connections with Clients on smartphones or tablets, we would like to use mORMot to do the job.
Our problem is we would like to use mORMot only for its REST Server and Client framework without the ORM; in fact server methods will send Datasets to the client in JSON, and filling the Datasets with TFDQuery (our DataBase is in FireBird).
Is it possible to do so ? If yes which components should we use ?
According to the examples in mORMot's repository, should we use the TServerSQLRestServerFullMemory ? Do you have any code example which fits our need ?
Thanks a lot !

emk · 2018-02-13 08:53:51

You don't need to use TFDQuery. Using TFDquery will be fine but in the end it's somehow the same approach as Datasnap (many allocations in memory -> many LOCK instructions -> slower performance). Use synDBZeos unit to access Firebird and there you have ISQLDBRows.FetchAllToJson. It is the fastest mode to return database rows to corresponding Json.

Use method based service: http://blog.synopse.info/post/2010/07/1 … phi-7-2010

ab · 2018-02-13 12:11:09

You may - and should - use interface-based services.

Define RawJSON values, and use SynDBZeos + FetchAllToJson, as emk proposed.

Check this blog article for more info:
https://tamingthemormot.wordpress.com/2 … databases/

Ozim16 · 2018-02-13 16:00:08

Hello,

Thank you for your answers. Right now, we are testing the Interface-based services, it works good but we are struggling trying to connect the TSQLDBZEOSConnectionProperties to our database.
The database is on Firebird 3.0 on Port 3055 (localhost), everytime we have an error : "Classe d'exception EZSQLException avec un message 'Requested database driver was not found'". The connection URL is :
'zdbc:firebird-3.0://localhost:3055/D:\ATOM\database\ATOM.FDB?username=SYSDBA;password=masterkey;LibLocation=C:\Firebird_3_0\WOW64\fbclient.dll'
We tested with adding the ZConnexion in a form, it connects well and its URL is exactly the same as above.
By putting breakpoints, we saw that no Driver is in FDrivers property of TZDriverManager. Is something missing ? Maybe something like 'registerDriver ?

Ozim16 · 2018-02-13 16:11:47

Finally we have found a solution for this problem.
For those who have the same problem, just add "ZConnection" in the uses statement where the TSQLDBZEOSConnectionProperties is created.

Ozim16 · 2018-02-14 15:32:15

Hi again.
We are working on the client side now and its works fine.
The service method returns a RawJSON (which was sent with DbConnection.Execute(...).FetchAllAsJSON(true)).
In order to store the data on the client side we are doing :
var values: TDocVariantData;
values.InitJSON(res, JSON_OPTIONS[true]);
DataSource1.DataSet := TDocVariantArrayDataSet.Create(Self,values.Values,[],[]);
DataSource1.DataSet.Open;

Is it the correct way to achieve it or is there a better/faster way to do ?

Thanks

mpv · 2018-02-14 16:13:23

Much better and faster is:
1) returns a RawJSON using DbConnection.Execute(...).FetchAllAsJSON(false)
2) deserialize it using mORMotMidasVCL.JSONToClientDataSet or mORMotVCL.JSONToDataSet

triguinhu · 2018-02-14 17:44:57

Hi mpv,

When work with FMX?
MormMotMidasVCL or MormotVCL don´t work

In this case, how to proceed?

Thank´s

Last edited by triguinhu (2018-02-14 17:45:13)

wloochacz · 2018-02-16 08:03:04

emk wrote:

You don't need to use TFDQuery.

Yes, indeed - you may use just just FireDAC Phys Layer.
FireDAC Phys Layer Samples

You may execute or fetch data form server without TDataSet.

OK, ISQLDBRows is really fast and really simple.

emk wrote:

Using TFDquery will be fine but in the end it's somehow the same approach as Datasnap (many allocations in memory -> many LOCK instructions -> slower performance).

I'm power user for AnyDAC/FireDAC, and I'm sorry - this is not true.

AnyDAC / FireDAC is very fast. But you have to be able to use it, which is not so obvious.
It has really extensive capabilities (macros and SQL preprocessor with the registration of its own functions? No problem!), Which can cause confusion for beginners and even experienced users.

I use my own version of FireDAC, which I have equipped with many unique features, eg built-in SQL parser/builder in TFDQuery.
Which allows me, for example, to change the WHERE clauses in this way:

    // qReport is a TFDQuery
    // Add to where clause: (DataStart >= :DataStart and DataStart <= :DataStart)
    qReport.SQLParser.Where.Clear.
      .BeginGroup()
        .Add('DateStart', coAnd, opGreaterEqual)
        .Add('DateStart', coAnd, opLessEqual)
      .EndGroup();
    // Commit changes to SQL and prepare Query
    qReport.SQLParser.CommitSQL([RepFrom, RepTo], [ftDateTime, ftDateTime]);

Or this way:

function TSopSalesOfferProdCalcLogic.GetdsTppTechRoutePattern: TboTppTechRoutePattern;
begin
  // TboTppTechRoutePattern is a strong typed TFDQuery
  if not Assigned(FdsTppTechRoutePattern) then
  begin
    FdsTppTechRoutePattern := TboTppTechRoutePattern.Create(Self);
    FdsTppTechRoutePattern.SetFastForward(True, False, 15);
    // modify Select SQL for fetch one row by Primary Key value
    FdsTppTechRoutePattern.SQLParser.Clear.Where.Add(FdsTppTechRoutePattern.PKFields);
    FdsTppTechRoutePattern.SQLParser.CommitSQL();
  end;
  Result := FdsTppTechRoutePattern;
end;

emk · 2018-02-16 11:07:14

Indeed, FireDAC is really powerful an I use it extensively on client app(memory tables, cloning, ...), but on server side, where scaling is critical, using FireDAC as TDataset as most people will think to do it, has some drawbacks: TDataset if it's not unidirectional will fetch all rows in memory (which will allocate memory, which will LOCK any other CPU cores from processing,..), will fire all/any events related to TDataset and then make not so optimized conversion to Json. Instead, ISQLDBRows will fetch only one record at time and convert it to Json row directly from ISQLDBRows row-buffer.

I didn't use FireDAC Phys. Seems to me, to be one layer below TDataset (with speed advantages), but still doesn't have direct conversion to Json from ISQLDBRows row-buffer.

The both scenario will work, but ISQLDbRows is more optimized for server side and FireDAC is best at client side.

Ozim16 · 2018-02-16 11:20:13

Hello everyone,

The server and the client are working fine. We made some stress tests with it and we encountered some problems.
For the tests, first we ran the server and the client on the same computer. The server sends to the client results from the database (arount 40 000 rows) as JSON (the size is around 8,6Mo).
Then the client sends to the server a String and the server inserts a new row in a table of the database (for log reasons)
The client is running the request for 10 times. 40 clients are running at the same time.

The server stops the connections with the error :

 ECrtSocket --> 'SndLow 10054 WSAECONNRESET [Une connexion existante a dû être fermée par l'hôte distant]'

Then we also tested running the client on a distant computer, and with only 20 clients, we have the following error :

 EWebSockets --> 'SockInPending() Error 10053 on :'
EcrtSocket --> 'SndLow 10053 WSAECONNABORTED [Une connexion établie a été abandonnée par un logiciel de votre ordinateur hôte]'

So we would like to know what is the reason of such behaviour and how to have a stack of requests (if the server is struggling with too much requests) in order to have every client waiting for its answer.
Maybe the test is not really realistic because no client would ask for as much data.

Here is the Server definition :

fModel := TSQLModel.Create([], 'service');
fRestServer := TSQLRestServerFullMemory.Create(fModel, false);
ServiceFactoryServer := fRestServer.ServiceDefine(TRestMethods, [IRestMethods], sicSingle);
ServiceFactoryServer.SetOptions([], [optErrorOnMissingParam]);

fHTTPServer := TSQLHttpServer.Create(AnsiString(Port), [fRestServer], '+', useBidirSocket, 32, secSynShaAes);
fHttpServer.AccessControlAllowOrigin := '*'; // Allow cross-site AJAX Queries
TWebSocketServerRest(fHTTPServer.HttpServer).ServerKeepAliveTimeOut := 3000;
{ WebSocketServerRest := } fHTTPServer.WebSocketsEnable(fRestServer, '', True);

And for the Client definition :

fModel := TSQLModel.Create([], ROOT_NAME);
fClient := TSQLHttpClientWebsockets.Create(
              ServerName,      // Server
              Port,            // Port
              fModel,          // Model
              5000,     // Send Timeout
              5000,  // Receive Timeout
              10000); // Connect Timeout

TSQLHttpClientWebsockets(fClient).KeepAliveMS := 3000;
TSQLHttpClientWebsockets(fClient).Compression := [hcSynShaAes]; // Cryptage AES
(fClient as TSQLHttpClientWebsockets).WebSocketsUpgrade('', True);

// Service initialization
fClient.ServiceDefine([IRestMethods], sicSingle);
Result := fClient.Services.Resolve(IRestMethods, RestMethods); // same result, but no chance to make mistake with service name

Thank you very much for your help.

Ozim16 · 2018-02-28 13:45:31

Hello again, we have another problem unfortunately...

The code works great on Windows, but with Android... In fact, we used SynCommons, mORMot and mORMotVCL in order to have the RawJSON type and the JSONToDataSet function.
With Android and other mobile devices, we should use SynCrossPlatform*. But all our functions do not work anymore. For example we had :

function ...(int id) : TDataSet;
begin
  JSON   := Methods.GetTopics(id);
  Result  := JSONToDataSet(Nil, JSON);
end

What should we use now ? Is it still possible to keep our IMethods interface, with functions returning RawJSON ?

Our server is a web services which returns results of SQL requests with datasets, in fact the client needs data from the database and the server gives him the datasets as JSON (with SQLToJSON).

Thanks for your help.

mORMot Open Source

#1 2018-02-12 22:01:22

mORMot Delphi REST Server without ORM

#2 2018-02-13 08:53:51

Re: mORMot Delphi REST Server without ORM

#3 2018-02-13 12:11:09

Re: mORMot Delphi REST Server without ORM

#4 2018-02-13 16:00:08

Re: mORMot Delphi REST Server without ORM

#5 2018-02-13 16:11:47

Re: mORMot Delphi REST Server without ORM

#6 2018-02-14 15:32:15

Re: mORMot Delphi REST Server without ORM

#7 2018-02-14 16:13:23

Re: mORMot Delphi REST Server without ORM

#8 2018-02-14 17:44:57

Re: mORMot Delphi REST Server without ORM

#9 2018-02-16 08:03:04

Re: mORMot Delphi REST Server without ORM

#10 2018-02-16 11:07:14

Re: mORMot Delphi REST Server without ORM

#11 2018-02-16 11:20:13

Re: mORMot Delphi REST Server without ORM

#12 2018-02-28 13:45:31

Re: mORMot Delphi REST Server without ORM

Board footer