mORMot Open Source

houdw2006

Member

Registered: 2015-05-23

Posts: 48

TSQLRestServerDB.MainEngineList does not work as expected

Hi,  everybody!

I am a newbie of mORMot user. I am trying to use mORMot to retrieve data stored in the MSSQL database as such:

var
   fSmsListMT: TObjectList;
begin
      fSmsListMT := RestServer.RetrieveList(TSQLSMS_SubmitMT,
        '(GatewayType=?) AND (SubmitStatus=?) LIMIT ?',
          [SubmitManager.GatewayType, Ord(ssReady4Submit), StrToIntDef(edtSubmitCount.Text, 50)]); 

      .....
end;

     If there are plenty of rows in the table, there will be no problem. Bu after the last available row in the target table is consumed, the call to RestServer.RetrieveList will be always return empty from the table, even I added quite a lot of rows into it by a third party tool (such as SQL script).

        I traced into the calling sequence, and  I finally reached here: TSQLRestServerDB.MainEngineList in file mORMotSQLite3.pas (Thanks for the Open Source again!), and after exeucte the
         "result := DB.LockJSON(SQL,ReturnedRowCount);", I checked the var result and ReturnedRowCount, even there is no record in the cache table, the var result is not empty(actually it contains the header of the target table, and with the property rowCount equals to 0.); while the another var, ReturnedRowCount, is nil.

    When I changed the code as following,  the TSQLRestServerDB.MainEngineList works as expected.

    result := DB.LockJSON(SQL,ReturnedRowCount); // lock and try from cache
    // if result='' then // Execute request if was not got from cache
    if ReturnedRowCount = nil then // Execute request if was not got from cache
    try
      try
        GetAndPrepareStatement(SQL,false);
        MS := TRawByteStringStream.Create;
     ...........

    Thanks in advance.

    Best Regards,

    Dewen

Last edited by houdw2006 (2015-05-26 03:29:57)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: TSQLRestServerDB.MainEngineList does not work as expected

First get rid the parenthesis in the where clause. It will bypass the virtual table access.
See http://synopse.info/files/html/Synopse% … ml#TITL_27

I guess your mssql table is modified outside mORMot. This is why the cache is not accurate.
See http://synopse.info/files/html/Synopse% … l#TITL_106

houdw2006

Member

Registered: 2015-05-23

Posts: 48

Re: TSQLRestServerDB.MainEngineList does not work as expected

Yes, after get rid of the parenthesis, it works. Thank you!

houdw2006

Member

Registered: 2015-05-23

Posts: 48

Re: TSQLRestServerDB.MainEngineList does not work as expected

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

Last edited by houdw2006 (2015-05-31 12:49:27)