free objects on the server side

cypriotcalm · 2015-07-10 07:58:39

Hello,

I have a service on the server:

  IMessartService = interface(IInvokable)
    procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  end;
  
  ...
  ...
  
  procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  begin
    aMessarts := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;

Now, I want to retrieve the data per http-request: (data amount is about 300 000 rows from external database)

http://server:1234/root/messartservice/SelectByManufacturer?aManufacturer=qwerty

If I do so, the size of the mORMot-Server-Exe is getting bigger and has about 500 MB. And each request increases the EXE-size.

How should I do the memory management in this case?

My service is defined as ClientDriven.

I coudn't find any information about that in the documentation and in froum :-(

Last edited by cypriotcalm (2015-07-10 08:00:14)

zed · 2015-07-10 08:39:32

Simple call aMessarts.Free after use?

cypriotcalm · 2015-07-10 08:45:16

zed wrote:

Simple call aMessarts.Free after use?

If it would be so simple ;-) after the call .free I get an "EInvalidPointer"-error :-(

ab · 2015-07-10 10:56:17

On the server side, the aMessarts: TObjectList is released automatically, after each call:

function TServiceMethod.InternalExecute(Instances: array of pointer;
  ....
      for i := 0 to ArgsUsedCount[smvvObject]-1 do
        Objects[i].Free;
  ...

So the memory would not increase on the server side.

On the client side, you have to manage memory as usual:

var service: IMessartService;
     list: TObjectList;
begin
  ... fill service
  list := TObjectList.Create;
  try
    service.SelectByManufacturer('smith',list);
    ... use list[]
  finally
    list.Free;
  end;
end;

cypriotcalm · 2015-07-13 06:56:21

Thx for the answer, but I want to call my data per http-request, NOT in delphi. I want to retrieve the data for example in browser with uri

http://server:1234/root/messartservice/SelectByManufacturer?aManufacturer=qwerty

and I do this then I get the situation like stated in my first post. Please read it again!

ab · 2015-07-13 10:48:45

There is no reason why the direct HTTP request may consume server memory.
The TObjectList is serialized in to a JSON response, this this JSON string is passed to the client, and released as soon as it has been sent.

Your SelectByManufacturer() method sounds fine.
I guess there is a leak somewhere else in your code.

cypriotcalm · 2015-07-13 11:51:54

ab wrote:

There is no reason why the direct HTTP request may consume server memory.
The TObjectList is serialized in to a JSON response, this this JSON string is passed to the client, and released as soon as it has been sent.

I don't understand then why the server-exe is getting bigger....

ab wrote:

Your SelectByManufacturer() method sounds fine.
I guess there is a leak somewhere else in your code.

I have these methods :-)

I initialize like follows:

  // SERVER
  TMessartService = class(TInterfacedObject, IMessartService)
  private
    function fRest: TSQLRest;
  published
    procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  end;  

  function TMessartService.fRest: TSQLRest;
  begin
    Result := ServiceContext.Factory.Rest;
  end;  

  procedure TMessartService.SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  begin
    aMessarts := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;

  ...
  procedure TFrmMainServer.InitHTTPService;
  begin
    InitializeModel(Model, DBConnectionProperties);

    ServerDB := TSQLRestServerDB.Create(Model, ChangeFileExt(ExeVersion.ProgramFileName, '.db3'), False);
    ServerDB.CreateMissingTables;

    ServerDB.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

    // Messart service
    ServerDB.ServiceDefine(TMessartService, [IMessartService], sicClientDriven);

    ServerHTTP := TSQLHttpServer.Create('7979', [ServerDB], '+', useHttpApiRegisteringURI);
    ServerHTTP.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
  end;


  // CLIENT
  FModel := TSQLModel.Create([]);

  FClient := TSQLHttpClient.Create('serverhost','7979', FModel);

  FClient.ServiceDefine([IMessartService], sicClientDriven);

  if not FClient.Services['MessartService'].Get(FMessartService) then
    raise Exception.Create('Can''t get the service'); 


 procedure TFrmMainClient.Button1Click(Sender: TObject);
 var
   aMessartList: TObjectList;
 begin
   aMessartList := TObjectList.Create;
   try
     FMessartService.SelectByManufacturer('smith', aMessartList);
     ShowMessage('Done! '+IntToStr(aMessartList));
   finally
     aMessartList.Free;
   end;
 end;

This is my whole application! There is no more code!

Now, I have another problem. If I call on the client FMessartService.SelectByManufacturer('smith', aMessartList) I get an error:

   EInterfaceFactoryException: TInterfacedObjectFakeClient failed: returned object

If I change the code on the server like follows:

  procedure TMessartService.SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  var
    LocalMessartList: TObjectList;
  begin
    LocalMessartList := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;

Everything is ok, the data is retrieved from the database. But I can't pass it back to the client :-(

What am I doing wrong?! It's really frustrating to have such bugs and the code is very simple! :-(

Thank you very much for your help!!!

ab · 2015-07-13 12:35:33

How is an "exe" growing?
Please explain.
Did you try with the internal SQlite3 engine?

Register a T*ObjArray and use it instead of this TObjectList.

type
  TMessartObjArray = array of TMessart;

....
  TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(TMessartObjArray,TMessart);
...

procedure TMessartService.SelectByManufacturer(const aManufacturer: TName): TMessartObjArray;
begin
  fRest.RetrieveListObjArray(result,TMessart, 'ManufacturerID = ?', [aManufacturer]);
end;

cypriotcalm · 2015-07-14 07:56:36

"exe" memory is growing...
I call the service-function (http-request) which retrieves a big amount of data from the external database (MySQL/FireDAC). And on the server in the task manager the "exe" consumes more memory, in my case it has the size of 500MB after the first request. But in browser I get the data as JSON.

I have also rewritten the code as you have said, but now I get an access violation in TSQLRest.RetrieveListArray:

function TSQLRest.RetrieveListObjArray(var ObjArray; Table: TSQLRecordClass;
  const FormatSQLWhere: RawUTF8; const BoundsSQLWhere: array of const;
  const aCustomFieldsCSV: RawUTF8): boolean;
var T: TSQLTable;
begin
  result := false;
  if (self=nil) or (Table=nil) then
    exit;
  T := MultiFieldValues(Table,aCustomFieldsCSV,FormatSQLWhere,BoundsSQLWhere);
  if T<>nil then
  try
    result := T.ToObjArray(result,Table); <------- HERE access violation
  finally
    T.Free;
  end;
end;


function TSQLTable.ToObjArray(var ObjArray; RecordType: TSQLRecordClass=nil): boolean;
var R: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
    arr: array of TSQLRecord absolute ObjArray;
begin
  result := false;
  ObjArrayClear(ObjArray); <------- HERE access violation
  if self=nil then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  result := true;
  if fRowCount=0 then
    exit;
  R := RecordType.Create;
  try
    R.FillPrepare(self);
    SetLength(arr,fRowCount);        // faster than manual Add()
    Row := @fResults[FieldCount];   // Row^ points to first row of data
    for i := 0 to fRowCount-1 do begin
      arr[i] := RecordType.Create;
      R.fFill.Fill(pointer(Row),arr[i]);
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;


procedure ObjArrayClear(var aObjArray);
var i: integer;
    a: TObjectDynArray absolute aObjArray;
begin
  if a<>nil then begin
    for i := 0 to length(a)-1 do <------- HERE access violation
      a[i].Free;
    a := nil;
  end;
end;

ab · 2015-07-14 12:23:02

How did you write TMessartService.SelectByManufacturer ?

cypriotcalm · 2015-07-14 12:49:51

ab wrote:

How did you write TMessartService.SelectByManufacturer ?

TMessartObjArray = array of TMessart;

function TMessartService.SelectByManufacturer(const aManufacturer: TName): TMessartObjArray;
begin
  ServiceContext.Factory.Rest.RetrieveListObjArray(Result, TMessart, 'ManufacturerID = ?', [aManufacturer]);
end;

Last edited by cypriotcalm (2015-07-14 12:55:43)

Junior/RO · 2015-07-14 14:47:24

I can't get it.

function TSQLRest.RetrieveListObjArray() is Boolean, but you are using Result as a [absolute] TObjectDynArray in ObjArrayClear()

cypriotcalm · 2015-07-14 15:20:48

Junior/RO wrote:

I can't get it.
function TSQLRest.RetrieveListObjArray() is Boolean, but you are using Result as a [absolute] TObjectDynArray in ObjArrayClear()

I don't want to use the boolean result of the function RetrieveListObjArray, the result of SelectByManufacturer() should be filled by RetrieveListObjArray(Result,...)

Junior/RO · 2015-07-14 17:23:47

What happens if you change this

  try
    result := T.ToObjArray(result,Table); <------- HERE access violation
  finally
    T.Free;
  end;

to this?

  try
    result := T.ToObjArray(ObjArray,Table);
  finally
    T.Free;
  end;

ab · 2015-07-14 17:48:58

Yes, there was an issue here.

Fixed by http://synopse.info/fossil/info/f83aed8e10

Thanks for the feedback.

cypriotcalm · 2015-07-15 09:06:31

ab wrote:

Yes, there was an issue here.
Fixed by http://synopse.info/fossil/info/f83aed8e10
Thanks for the feedback.

Thx for the fixing!

But the problem with the growing size of the server-"exe" remains... after the http-request, e.g. http://serverhost:7979/root/messartservice/SelectByManufacturer?aManufacturer=SMITH, I get about 294912 records a database. It seems that the "exe" does free not everything.

ab · 2015-07-15 09:12:46

JSON content should be cached somewhere.

Please try to disable SQlite3 cache.

cypriotcalm · 2015-07-15 09:28:48

ab wrote:

JSON content should be cached somewhere.
Please try to disable SQlite3 cache.

I've tried it as follows, but the problem remains...

...
  ServerDB := TSQLRestServerDB.Create(Model, ChangeFileExt(ExeVersion.ProgramFileName, '.db3'), False);
  ServerDB.DB.UseCache := False;
  ServerDB.CreateMissingTables;
...

ab · 2015-07-15 14:07:06

Do you have the same behavior with a native SQlite3 DB?

Do you have the same behavior when connecting to MySQL with ZDBC instead of FireDAC?

Do you have the same behavior when you are NOT using services, but a plain MySQL/FireDAC request, using only a SynDB SELECT statement, not the ORM?

houdw2006 · 2015-07-18 13:10:39

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.

ab · 2015-07-19 08:38:43

Did you try with ODBC?

houdw2006 · 2015-07-19 11:40:25

ab wrote:

Did you try with ODBC?

Yes, I tried to connect the external database with ODBC, but only partially succeeded.

I Connect the MSSQL Database with ODBC successfully, as I can get all the table names in the external database. But when I try to get some rows from one table (from the server side) like this:

PeopleList := RestServer.RetrieveList(TSQLPeople, '', [], '');

I got an exception: TODBCStatement - TODBCLib error: [07009] [Microsoft][ODBC SQL Server Driver] Invalid descriptor index (0)

All the RetrieveList calls from the Client side, fails as well.

In the server side, I tried to Add some rows to the table named People, but the following call allways returns aID with 0 and without any error message. Nothing is added to the database.

aPeople := TSQLPeople.Create;
try
aID := RestServer.Add(aPeople, True);
finally
aPeople.Free;
end;

Last edited by houdw2006 (2015-07-19 13:17:48)

houdw2006 · 2015-07-19 17:04:50

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?

ab · 2015-07-19 18:20:27

I'm afraid it is not "Props" which "steal" all memory...

It is the OleDB provider which does not release its memory as expected...

houdw2006 · 2015-07-19 22:48:52

Thanks for your reply, ab.

I am sorry for the joke "Props stealed all the lost memory...", so don't feel unhappy about what I said. I am not familiar with OleDB programming by using the OleDB interface. But I think perhaps we need to Release the ICommandText somewhere after we use it, as the RefCount is not zero, the OleDB Provider will not Release the memory associated with this ICommandText reference. The Props is the Owner of OleDB Provider, it has the responsibility to do that.

If there is no solution for the "memory growing" problem in a short time, can we release the Props periodically as a workaround?

houdw2006 · 2015-07-20 03:13:59

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.

houdw2006 · 2015-07-20 03:29:45

// 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 #

//----------------------- Memory Release ---------------------------
//When I call FreeAndNil(Props);
//the process goes to:
//procedure TSQLDBConnection.Disconnect; // SynDB, line 3941
//var i: integer;
// Obj: PPointerArray;
//begin
// InternalProcess(speDisconnected);
// if fCache<>nil then begin
// InternalProcess(speActive);
// try
// Obj := fCache.ObjectPtr;
// if Obj<>nil then
// for i := 0 to fCache.Count-1 do
// TSQLDBStatement(Obj#i#).FRefCount := 0; // force clean release
// FreeAndNil(fCache); // release all cached statements
// finally
// InternalProcess(speNonActive);
// end;
//end;

Last edited by houdw2006 (2015-07-20 03:32:15)

houdw2006 · 2015-07-20 03:30:57

// 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. ：-）

Last edited by houdw2006 (2015-07-20 03:39:54)

houdw2006 · 2015-07-20 07:32:39

Still for the "memory growing" problem, this time, I want to see where the memory is allocated.
From the Task Manager, It's very clear that I call RestServer.Add(aPeople, True) 4 times, the working memory set will increase 4KB.

I traced into
procedure TOleDBStatement.ExecutePrepared; // SynOleDB, line 1560
begin
.......
SetLength(ParamsStatus,fParamCount); // line 1626
OleDBConnection.OleDBCheck(self, // line 1627, after execute this, the number of working memory set will be increased by 4KB.
(fCommand as IAccessor).CreateAccessor(
DBACCESSOR_PARAMETERDATA,fParamCount,Pointer(fParamBindings),0,
fDBParams.HACCESSOR,pointer(ParamsStatus)),ParamsStatus);
fDBParams.cParamSets := 1;
....
// 3.2 ExpectResults=false (e.g. SQL UPDATE) -> leave fRowSet=nil
OleDBConnection.OleDBCheck(self, // line 1648
fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil));
.....
end;

Memory is allocated in
(fCommand as IAccessor).CreateAccessor( // line 1628
or
fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil) // line 1649

to be continued...

ab · 2015-07-20 08:33:20

I've made the TSQLDBConnectionProperties.UseCache property read/write.
See http://synopse.info/fossil/info/6dc6464869

I suspect that if you disable statement cache, it would release all resources ASAP.

houdw2006 · 2015-07-21 03:01:43

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).

ab · 2015-07-21 09:41:35

How is you query like?

I guess there is a JOIN in it, or some complex expression.

The ORM is able to process most simple requests, but fallback to SQLite3 virtual tables when the request is more complex.
In this context, performance may degrade a lot, and a lot of data is retrieved by the SQlite3 virtual table engine.

Complex queries should better by-pass the ORM, and run directly against the external DB.

houdw2006 · 2015-07-21 10:14:39

It's a simple query, just like this expected T-SQL, SELECT TOP 200 * FROM People WHERE DepartNo=30 AND WorkingStatus=3

do the query from the Client side, I get the wrong T-SQL:

....
AppServer: TSQLHttpClient;
fWorkingPeople: TObjectList;
....

fWorkingPeople := AppServer.RetrieveList(TSQLPeople,
'DepartNo=? AND WorkingStatus=? LIMIT ?',
[30, 3, 200]);

// In the MSSQL Profiler, I get: SELECT * FROM People WHERE DepartNo=30 AND WorkingStatus=3
.......

do the same query from the Server side, I get the correct T-SQL:
....
TCustomHttpServer = class(TSQLHttpServer)
....
end;

RestServer: TSQLRestServerDB;
HttpServer: TCustomHttpServer;
fWorkingPeople: TObjectList;
.......

// do the query from the server side, the T-SQL is correct :
fWorkingPeople := RestServer.RetrieveList(TSQLPeople,
'DepartNo=? AND WorkingStatus=? LIMIT ?',
[30, 3, 200]);
.......
// In the MSSQL Profiler, I get: SELECT TOP 200 * FROM People WHERE DepartNo=30 AND WorkingStatus=3

houdw2006 · 2015-07-22 21:44:13

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.

mingda · 2015-07-22 23:58:24

I think you can make a test case for anyone else to trace the problem, let ab debug it easy.

ab · 2015-07-23 06:04:13

1. Write the requests WITHOUT parenthesis.

2. LIMIT should not be parametrized, but have a direct number (no ?).

houdw2006 · 2015-07-23 14:02:22

It really works. Thank you! I am really enjoy using mORMot!

mORMot Open Source

#1 2015-07-10 07:58:39

free objects on the server side

#2 2015-07-10 08:39:32

Re: free objects on the server side

#3 2015-07-10 08:45:16

Re: free objects on the server side

#4 2015-07-10 10:56:17

Re: free objects on the server side

#5 2015-07-13 06:56:21

Re: free objects on the server side

#6 2015-07-13 10:48:45

Re: free objects on the server side

#7 2015-07-13 11:51:54

Re: free objects on the server side

#8 2015-07-13 12:35:33

Re: free objects on the server side

#9 2015-07-14 07:56:36

Re: free objects on the server side

#10 2015-07-14 12:23:02

Re: free objects on the server side

#11 2015-07-14 12:49:51

Re: free objects on the server side

#12 2015-07-14 14:47:24

Re: free objects on the server side

#13 2015-07-14 15:20:48

Re: free objects on the server side

#14 2015-07-14 17:23:47

Re: free objects on the server side

#15 2015-07-14 17:48:58

Re: free objects on the server side

#16 2015-07-15 09:06:31

Re: free objects on the server side

#17 2015-07-15 09:12:46

Re: free objects on the server side

#18 2015-07-15 09:28:48

Re: free objects on the server side

#19 2015-07-15 14:07:06

Re: free objects on the server side

#20 2015-07-18 13:10:39

Re: free objects on the server side

#21 2015-07-19 08:38:43

Re: free objects on the server side

#22 2015-07-19 11:40:25

Re: free objects on the server side

#23 2015-07-19 17:04:50

Re: free objects on the server side

#24 2015-07-19 18:20:27

Re: free objects on the server side

#25 2015-07-19 22:48:52

Re: free objects on the server side

#26 2015-07-20 03:13:59

Re: free objects on the server side

#27 2015-07-20 03:29:45

Re: free objects on the server side

#28 2015-07-20 03:30:57

Re: free objects on the server side

#29 2015-07-20 07:32:39

Re: free objects on the server side

#30 2015-07-20 08:33:20

Re: free objects on the server side

#31 2015-07-21 03:01:43

Re: free objects on the server side

#32 2015-07-21 09:41:35

Re: free objects on the server side

#33 2015-07-21 10:14:39

Re: free objects on the server side

#34 2015-07-22 21:44:13

Re: free objects on the server side

#35 2015-07-22 23:58:24

Re: free objects on the server side

#36 2015-07-23 06:04:13

Re: free objects on the server side

#37 2015-07-23 14:02:22

Re: free objects on the server side

Board footer