mORMot Open Source

Thank you, AB. Really  appreciate for your help, and the wonderful mORMot.  I am thinking to use a local SQLite3 database in the client side,  and then submit the change to server side by Batch.

My problem is that the HTTP Request of BatchSend seldom returns 408 (Timeout of http request). It's a little bit strange as the clients and server are located in the same computer.

After reading the TSQLRestClientURI.BatchSend, I think it is better to free the fCurrentBatch only after Connecting the HTTP Server successfully:

function TSQLRestClientURI.BatchSend(var Results: TIDDynArray): integer;
begin
  if self<>nil then
  try
    result := BatchSend(fBatchCurrent,Results);
  finally
    // conditionly free the fBatchCurrent, so we would have more chance to call BatchSend again.
    if (Result = HTTP_SUCCESS) then
      FreeAndNil(fBatchCurrent);
  end else
    result := HTTP_BADREQUEST;
end;

@AB, what do you think about this? Will it work? Thanks in advance.

Hi, @AB. I have another feature request for the WhereCondition is empty. For most back-end database, there is a TRUNCATE command to delete all the rows from a table without logging and end the task more quickly. So can you add the TRUNCATE support for back-end database? Thank you!

Thank you. @miab3.

My backend MSSQL is MSSQL2016.  I tried the above strings, and It seems not work.

Then I compiled the whole ZEOS package V7.3-alpha. By using the TZConnection, TZQuery components, I found that the value for parameter Url of function TZDriverManager.GetConnectionWithParams(const Url: string; Info: TStrings):  IZConnection;
  is a quite long string: zdbc:OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1'#9'Password=XXXX'#9'Persist Security Info=True'#9'User ID=YYYY'#9'Initial Catalog=ZZZZ'#9'Data Source=127.0.0.1?username=UUUU;password=PPPP'

So I give the following string in UI of SynDBExplore as: OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1, and found that when SynDBZeos call
GetConnectionWithParams, the Url is just about the same.
    zdbc:OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1?username=UUUU;password=PPPP;controls_cp=CP_UTF8

The TZConnection, TZQuery works in Win64. But when combined zeos with mORMot, the Zeos raised a lot of exceptions such as EZSQLException OLEDB Error 80040E14 and Can't found the datatype CLOB. I use RawUTF8 datatype for UTF-8 Strings. 

What is wrong for my usage of SynDBZeos and Zeos?

The empty where condition works well for deleting the whole table now. @AB, Thank you very much. I have no idea what happens behind the OleDB.

Thank you AB.

The modification makes some progress, but  still does NOT work.

tracing:

mORMotDB.pas  1351    if ExecuteInlined('delete from % where %',[fTableName,SQLWhere],false)=nil ....

I got an EOleDBException at  SynOleDB.pas 2396  procedure TOleDBConnection.OleDBCheck(aStmt:

From the SQL Server Profile, I got the following SQL statement:  delete from dbo.People where

It seems still need to distinguish the SQLWhere is empty or not before calling ExecuteInlined.

The following modification works:

      end;    // line 1348 of mORMotDB.pas
      exit;
    end else if SQLWhere = '' then begin
      if ExecuteInlined('delete from %',[fTableName],false)=nil then
        exit; end
      else if ExecuteInlined('delete from % where %',[fTableName,SQLWhere],false)=nil then
        exit;

@EMartin, This sounds better and hope ab can update the new implementation to the ThirdParty Demo in the near future. And hope that SynRestDataSet becomes a party of Synopse mORMot eventually!

Modification to the TSynRestSQLDataSet Class, implemented in unit SynRestVCL, follows:

  TSynRestSQLDataSet = class(TSynBinaryDataSet)
  private
    fRestClient: TSQLRest;  // mORMot broker for handling delete, insert, and update evnets.

    procedure SetupRestClient;
    procedure TearDownRestClient;
  protected
    .....
  public
  public
    /// initialize the instance
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;

    function Execute(const aSQL: RawUTF8): boolean; overload;
    function RetrieveBlob(Table: TSQLRecordClass; aID: TID;
      const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean; overload;
    function UpdateBlob(Table: TSQLRecordClass; aID: TID;
      const BlobFieldName: RawUTF8; BlobData: TStream): boolean; overload;

    property DeletedID: TID read fDeletedID write fDeletedID;
    property InsertedID: TID read fInsertedID;
    .....
  end;

constructor TSynRestSQLDataSet.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);

  fRestClient := nil;
end;

destructor TSynRestSQLDataSet.Destroy;
begin
  TearDownRestClient;

  inherited;
end;

procedure TSynRestSQLDataSet.SetCommandText(const Value: string);
begin
  if (Value <> fCommandtext) then
  begin
    fCommandText := Value;
    ParseCommandText;
    SetupRestClient;     // create fRestClient, an instance of TSQLRest;
  end;
end;

The implementation of these three methods of TSynRestSQLDataSet quite simple, just delegate the call to the broker, fRestClient.
function TSynRestSQLDataSet.Execute(const aSQL: RawUTF8): boolean;
begin
  Result := fRestClient.Execute(aSQL);
end;

function TSynRestSQLDataSet.RetrieveBlob(Table: TSQLRecordClass; aID: TID;
  const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean;
begin
  Result := fRestClient.RetrieveBlob(Table, aID, BlobFieldName, BlobData);
end;

function TSynRestSQLDataSet.UpdateBlob(Table: TSQLRecordClass; aID: TID;
  const BlobFieldName: RawUTF8; BlobData: TStream): boolean;
begin
  Result := fRestClient.UpdateBlob(Table, aID, BlobFieldName, BlobData);
end;

The most significant part of TSynRestSQLDataSet, is function TSynRestSQLDataSet.PSExecuteStatement.
{$ifdef ISDELPHIXE3} // note: I moved the original local functions to the private section of TSynRestSQLDataSet
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
  AParams: TParams): Integer;
var DS: TDataSet;
begin
  DS := nil;
  result := PSExecuteStatement(ASQL,AParams,DS);
  DS.Free;
end;

function TSynRestSQLDataSet.PSExecuteStatement(const ASQL:
    string; AParams: TParams; var ResultSet: TDataSet): Integer;
{$else}
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
    AParams: TParams; ResultSet: Pointer): Integer;
{$endif}
var
  lJSON: SockString;
  lOccasion: TSQLOccasion;

  fID: TID;
  fFieldNames: RawUTF8;
  fRec: TSQLRecord;
begin // only execute writes in current implementation
  Result := -1;
  if IsTableFromService then
    DatabaseError('Cannot apply updates from a service');
  lOccasion := GetSQLOccasion(aSQL);
  case lOccasion of
  soDelete:
    begin
      fID := fDeletedID;  // fDeletedID is set by instance of TSynRestDataSet
      if fRestClient.Delete(GetSQLRecordClass, fID) then
        Result := 1;
    end;
  soInsert:
    begin
      lJSON := SQLFieldsToJSON(soInsert, fFieldNames, aSQL, '(', ') ', aParams);
      fRec := GetSQLRecordClass.CreateFrom(lJSON);
      try
        fInsertedID := fRestClient.Add(fRec, fRec.RecordProps.FieldBitsFromCSV(fFieldNames), True);
        if fInsertedID > 0 then
        begin
          Result := 1;
          AfterInsert(Self);  // Update the ID field in the instance of TSynRestDataSet
        end;
      finally
        fRec.Free;
      end;
    end;
  soUpdate:
    begin
      lJSON := SQLFieldsToJSON(soUpdate, fFieldNames, aSQL, 'set ', 'where ', aParams);
      fRec := GetSQLRecordClass.CreateFrom(lJSON);
      try
        fID := aParams.ParamByName('ID').Value;
        fRec.IDValue := fID;  // fRec.ID is readonly, fRec.IDValue is writable
        if fRestClient.Update(fRec, fRec.RecordProps.FieldBitsFromCSV(fFieldNames)) then
          Result := 1;
      finally
        fRec.Free;
      end;
    end
  end;
end;

And I have added an extra parameter, aFieldNames, to function SQLFieldsToJSON in order to return the related simple column names.
I can't show the code here because of the restriction of post regulation of this forum. (prompt: i was opened within itself, this is not allowed). The related simple column names are returned in the CSV form.

function TSynRestSQLDataSet.SQLFieldsToJSON(const aSQLOccasion: TSQLOccasion;
  var aFieldNames: RawUTF8;
  const aSQL, aAfterStr, aBeforeStr: string; aParams: TParams): SockString;

@zed, yes, I have read that as well, thank you. I met the truncation problem when I try the third party demo, FishFactSyn, given by EMartin.

Stores 4 bytes or 8 bytes to the Blob field, it actually depends on the server is a 32-bit or 64-bit program, that is the sizeof(Pointer). So it seems to me that EvaF's solution is better.

Here I just want ab to pay attention to this problem and give it a solution, that's it. :=)

@EMartin,  finally goes to the problem why the demo of SynRestMidasVCL, FishFactSyn, does not work properly.

In TSynRestSQLDataSet (SynRestVCL),  the ID is got by the following statement:

      lID := aParams[0].Value;    // soDelete:  about line 750
             or
      lID := aParams.ParamByName('ID').Value;  // soUpdate: about line 769

In both state, the lID is not the real row ID of TSQLBiolife, but the value of Species_No. The reason is that the ID column is not exist in the SELECT statement. So we should add the ID as the first column of the SELECT statement, like this:

  SynRestDataset.CommandText := 'http://LocalHost:8080/root/BioLife?'
      + 'select=ID,Species_No,Category,Common_Name,Species_Name,'
      + 'Length_cm,Length_in,Graphic,Notes,Som&sort=Species_No';

The memory leakage still exists in the demo FishFactSyn, because of there is not a destructor for the unit FFactWin, like this one
(declared fSQLModel as a private variable of TForm1):

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FreeAndNil(fSQLModel);
  FreeAndNil(SynRestDataset);
end;

@EMartin,  I have found the reason why TSynRestSQLDataSet does not work under XE3 and above.  The commented function PSExecuteStatement SHOULD BE IMPLEMENTED, just like in TSynDBDataSet.  After the modification, The demo FishFactSyn can do insert, delete, and update, though it still does not work correctly. I will give the reasons in the following post.

  TSynRestSQLDataSet = class(TSynBinaryDataSet)
  protected
    fBaseURL: RawUTF8;
    ......
    {$ifdef ISDELPHIXE3}
    function PSExecuteStatement(const ASQL: string; AParams: TParams): Integer; overload; override;
    function PSExecuteStatement(const ASQL: string; AParams: TParams; var ResultSet: TDataSet): Integer; overload; override;
    {$else}
    function PSExecuteStatement(const ASQL: string; AParams: TParams; ResultSet: Pointer=nil): Integer; overload; override;
    {$endif}
    ......
  end;

  ......

{$ifdef ISDELPHIXE3}
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
  AParams: TParams): Integer;
var DS: TDataSet;
begin
  DS := nil;
  result := PSExecuteStatement(ASQL,AParams,DS);
  DS.Free;
end;

...........

Is this a typo in the document (several lines before 21.2.2. Additional safety)?

Be aware than this per-table access rights depend on the table order as defined ...

I think it should be read:

Be aware that this per-table access rights depend on the table order as defined ...

There are many mistakes in my last post. Sorry for the wrong bug report!  This time, I have done the test both for MSSQL and SQLite3, results are listed below.

Test MSSQL DataSet: Total 15 rows, with 13 rows satisfy the query condition
(I read the note in mORMot ducumentation 9.2.4, Note that parenthesis and mixed AND OR expressions are not handled yet.)

1. Query from Server:

Case 1:
   mORMot Query(Complex Query Syntax with Parenthesis):
      fPeopleList := RestServer.RetrieveList(TSQLPeople,
      '(DepartNo=?) AND (WorkingStatus=?) LIMIT ?',
        [20, 1, 10]);

   MSSQL Profiler: (note: ID is the last column, without TOP Clause in T-SQL)
   exec sp_executesql N'select ID,DepartNo,BirthDate,Name,WorkingStatus from dbo.People where DepartNo=@P1
             and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1
     
  ResultSet: 10 rows (The LIMIT Parameter seems worked, mORMot applied a local filter?)

Case 2:
   mORMot Query (Simple Query Syntax):
      fPeopleList := RestServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [20, 1, 10]);

   MSSQL Profiler: (note: ID is the first column, without TOP Clause in T-SQL)
     exec sp_executesql N'select DepartNo,BirthDate,Name,WorkingStatus,ID from dbo.People where DepartNo=@P1
                and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1

   ResultSet: 13 rows  (The LIMIT Parameter does NOT work).
   
   
2. Query from Client:

Case 3:
   mORMot Query(Complex Query Syntax with Parenthesis):
      fPeopleList := AppServer.RetrieveList(TSQLPeople,
      '(DepartNo=?) AND (WorkingStatus=?) LIMIT ?',
        [20, 1, 10]);

   HTTP Server InContent:
   SELECT ID,DepartNo,BirthDate,BirthDate,Name,WorkingStatus FROM People WHERE (SmsProxyNo=:(20):) AND (SmsStatus=:(1):) LIMIT :(10):

   MSSQL Profiler: (note: NO T-SQL at all)

     
  ResultSet: no ResultSet

Case 4:
   mORMot Query (Simple Query Syntax):
      fPeopleList := AppServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [20, 1, 10]);

   HTTP Server InContent:
   SELECT ID,DepartNo,BirthDate,BirthDate,Name,WorkingStatus FROM People WHERE DepartNo=:(20): AND WorkingStatus=:(1): LIMIT :(10):

   MSSQL Profiler: (note: ID is the first column, NO TOP Clause)
     exec sp_executesql N'select ID,DepartNo,BirthDate,Name,WorkingStatus,ID from dbo.People where DepartNo=@P1
                and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1

   ResultSet: 13 rows  (The LIMIT Parameter does NOT work).
   
   
-------------------------------------------------------------------------------------------------------------------------------------------
Test SQLite3 DataSet: Total 15 rows, with 13 rows satisfy the query condition

Redo the last 4 cases,

ResultSet: 10 rows, no problem with SQLite3.

Tank you so much, ab.

There is a good news and a bad news. The good news is that it does work for inserting. I test Inserting from both server and client side, after inserting more than 3 million rows into the table, the memory comsumed by the server just about 35MB. Terrific!

The bad news is that for the retrieving from a table with massive rows,  the memory consumed by the server is still dramatically large. when I want to retrieve the top 200 rows from a table with more than 1 million rows, the server consumed more than 400MB memory. I have monitored from the Profiler of MSSQL and found that, when the retrieve command is send from the client, the LIMIT parameter of RetrieveList does not work(MSSQL receive a query command without the expected "TOP 200" parameter), I think this is another reason why the server consuming so much memory (and the memory will not release automatically after the retrieve completed).

// I met an post error, says something like: "i" was opened with itself, this is not allowed!
// Even I Comment all the code, I can't pass the error check. :-(   I changed the [ into #

//and when FreeAndNil(fCache); is executed, the process goes here:
//
//destructor TRawUTF8List.Destroy;   // SynCommons, line 43571
//begin
//  Capacity := 0;
//  inherited;
//end;
//
//when Capacity := 0; is called, magic things happened here:
//
//procedure TRawUTF8List.SetCapacity(const Value: PtrInt);  // SynCommons, line 43832
//var i: integer;
//begin
//  if self<>nil then begin
//    if Value<=0 then begin
//      fList := nil;
//      if fObjects<>nil then begin
//        if fObjectsOwned then
//          for i := 0 to fCount-1 do
//            fObjects#i#.Free;    // when i = fcount - 1, magic happens!
//        fObjects := nil;
//      end;
//      fCount := 0;
//      ......
//    end;
//  end;
//end;
//
//// when i = fCount - 1, fObjects#i#.Free will release all the lost memory!
//
//I just do not know how fObjects[fCount-1] becomes a big monster!  I hope this can give you some help to catch the "memory growing" bug.  ：-）

Hi, ab.

I have discovered some interesting thing, but I can not understand what happen to the fCache.  Here is the tracing story. This time, I payed attention to the  fCache member in class TSQLDBConnection.

TSQLDBConnection = class    // SynDB, line 1604
    fCache: TRawUTF8ListHashed;
end;

(1) when connecting to the external database server, the RestServer try to get the datetime from the database Engine:
function TSQLDBConnection.GetServerDateTime: TDateTime; // SynDB.pas 
Added 'select GETDATE()' into fCache;

(2) the first time call RestServer.Add(aPeople, True),  the RestServer is trying to get the max ID in the related table:
function TSQLRestStorageExternal.EngineLockedNextID: TID; // mORMot.pas, line 980
Added 'select max(ID) from dbo.People' into fCache;

(3) still the first time call RestServer.Add(aPeople, True);
function TSQLRest.Add(aTable: TSQLRecordClass; const aSimpleFields: array of const; // mORMot, line 30358
function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8; ....); // SynDB, line 4029
Added 'insert into dbo.People (...) values (...)' into fCache.

after this time, no matter how many times I call RestServer.Add, no more objects are added to the fCache.

Conclusion: there are only 3 prepared statements are stored in the fCache in my cases.

Hi, ab,

    After try the next few lines of code, I found the secrete is in TOleDBConnection.

    Props: TSQLDBConnectionProperties;

    ......

    After connect to the external database by this Props, I just do the simplest thing, call RestServer.Add to add massive rows to the external database (the memory consumed increasely), then I intentially free this Props

procedure TFormRestServer.btnFreeConnectionPoolClick(Sender: TObject);
begin
    Props.ClearConnectionPool;  // line 1
    Props.CleanupInstance;       // line 2
    FreeAndNil(Props);              // line 3
end;

    After the first two lines are executed, the memory consumed by the server has no change. But when line 3 is executed, all the lost memory are released!

    So I traced into here: (line 5987 of unit SynDB).

destructor TSQLDBConnectionPropertiesThreadSafe.Destroy;
begin
  inherited Destroy;                                      // line 1
  fConnectionPool.Free;                                //  line 2
  DeleteCriticalSection(fConnectionCS);         //  line 3
end;

   After call the second line: fConnectionPool.Free; All the comsumed memory are released. (in the Task Manager, you can see it clearly).

   So it's very clear that Props stealed all the lost memory and kept track of them very well.

   Some hints: after call Props.NewThreadSafeStatementPrepared, do we need to do some thing like unprepare?
     or Query.ExecutePrepared then unprepared?

I have done some test with this problem ( with the mORMot version 1.18.1597, 1.18.1632 and 1.18.1655). I did not retrieve data rows instead of inserting massive rows to the database.

On the Server side:
  Props: TSQLDBConnectionProperties;

  // get the Props like in sample 12 - SynDB Explorer
  .....
  if (Props.DBMS <> dSQLite) then
      VirtualTableExternalRegisterAll(Model, Props);  // Register all tables of the model to be external if we don't use SQLite3
   //Props.UseCache := False;  // It's READONLY! How can I disable the Cache??

  fDbFileName := ChangeFileExt(paramstr(0),'.db3');
  try
    RestServer := TSQLRestServerDB.Create(Model, fDbFileName, False);
    with RestServer do
    begin
      CreateMissingTables;
      Cache.Clear;
      DB.UseCache := False;
      AcquireWriteMode := amBackgroundThread;
      AcquireWriteTimeOut := 30;
    end;

    // Create the HTTP Server for Client Connection
    HttpServer := TCustomHttpServer.Create(AppServerPort, [RestServer], '+', useHttpApiRegisteringURI);
    HttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
    ....

On the Client Side:

    Model := CreateSampleDataModel(ConstHttpRoot);
    AppServer := TSQLHttpClient.Create(AppServerIP, AppServerPort, Model);

    .......
    // Use BatchStart, BachAdd, BatchSend to Insert data rows to mORMot Server.
    .......

Test Results: (Watched the memory allocated by the Server side by the TaskManager of Win7-64bit when programs were running)
(1) If the Props is connected with the Internal SQLite3 Engine,  after insert more than 2 millions row into 2 tables, the total memory allocated by the server is about 75000KB (Maxium);
    The mORMot server works absolutely OK,   and with the FASTMM4 memory manager running in the FullDebuging mode, there is no memory leakage reported.

(2) If the selected Props is connected with the external MSQL Engine, the memory consumed by the server, seems proportional to the rows added to the external MSSQL database. Here are some test data listed below:
          Rows Inserted            Memory Consumed(in KB)
                   0                                  6180
            25200                                57160
          118620                              186256
          253576                              346664
          421526                              609227
          563164                              817312
          657664                              948559
          711214                            1017168
          .... Eventually, the Server goes with no response with the Client Request, all the remaining data will be lost.

      And with the FASTMM4 memory manager running in the FullDebuging mode, there is still no memory leakage reported.

      (It's not bleeding. It seems the problem is related with the TOleDBMSSQLConnectionProperties, and I tried to replace it with TOleDBMSSQL2008ConnectionProperties to connect with MSSQL2008, but nothing changed.)

      By the way, It seems there is a bug in 1.18.1655. When  exiting the Server program, there is an AV in SynDB:TSQLDBConnectionPropertiesThreadSafe.EndCurrentThread, ant it will not happen in previous versions of mORMot.

Thank you so much, ab!

That's the answer! The real problem is that I did NOT register the URI. This puzzled me more than one week and I couldn't find the reason by myself!

After I run the server (which runs the service) with administrator user rights once, after that the RemoteIP is correct now.

Thanks for you reply, ab.

I registered the Service function in the client side by means of the TSQLRestServerDB, and the client which connect to another remote AppServer through TSQLHttpClient, and runs in still another remote server. 

When the AppServer and Client both are running, I call the service function by entering the GET command in the Address box of Firefox which running in my laptop.

  //code fragments of the client side:

  TCustomHttpServer = class(TSQLHttpServer)
  protected
    /// override the server response - must be thread-safe
    function Request(Ctxt: THttpServerRequest): cardinal; override;
  end;

  ....................

    Model: TSQLModel;
    AppServer: TSQLHttpClient;
    RestServer: TSQLRestServerRemoteDB;
    HttpServer: TCustomHttpServer;

    .......................
    Model := CreateDataModel(AppServerHttpRoot);     // AppServerHttpRoot, AppServerIP, AppServerPort and HttpServicePort are Properties.

    AppServer := TSQLHttpClient.Create(AppServerIP, AppServerPort, aModel);

    RestServer := TSQLRestServerRemoteDB.Create(AppServer);
    RestServer.ServiceRegister(TAddService, [TypeInfo(IAddService)], sicShared);

    HttpServer := TCustomHttpServer.Create(HttpServicePort, [RestServer], '+', useHttpApiRegisteringURI);
    HttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries

Dear ab,

    This time, I generated all the tables without any modification(you are right, for the problem reported in the first level, I do have changed some bigint to smallint manually).

    After I get rid of the parenthesis in the where clause, I meet another problem,  the LIMIT clause has no effect anymore.

    And still another problem, when I connect the MSSQL database with the TSQLRestServerDB directly, the RetrieveList can return rows. But when I add a Client side and connect it to the server side by means of TSQLHttpClient, and do the same task use RetrieveList, there are more strange things happened: when I get rid of the  parenthesis in the where clause, I still can get some rows (and the LIMIT clause has no effect as well); when I keep the parenthesis in the where clause, I get nothing at all, the fSmsList is nil.

    I am sorry to bother you again and I cannot jump to the links your provided in your last answer.

    Thanks for your wonderful work and help!

    Dewen