mORMot Open Source

larand54

Member

Registered: 2018-12-25

Posts: 96

ORMSelectAll('Name_Last LIKE ?',[aName+'%'],(aName='')) does not work

I run the regressiontest of TUser in the DDDExample but found that there was no test with the 'LIKE' operator.
The function "SelectByLastName" exists though and it takes an extra boolean parameter "StartsWith" that controls whether you want exact(false) or something that starts with..
Added some code that exercise that function.

    test.Check(Rest.Services.Resolve(IDomUserCommand,cmd));
    CQRS := cmd.SelectByLastName('Last1', false);                  // Exact - this one works
    CQRS := cmd.SelectByLastName('Last', True);                    // Using "LIKE" and will not find anything.

Tried with debugging and finally entering: function TSQLRestStorageInMemory.GetJSONValues
where it checked the type of operator used.
Following cleaned code shows the execution flow..

function TSQLRestStorageInMemory.GetJSONValues(Stream: TStream;
    case Stmt.Where[0].Operator of
    opEqualTo:
opIsNull, opIsNotNull:
    else begin
err:  W.CancelAll;
      result := 0;
      exit;

When using the "LIKE" operator, the value of "Stmt.Where[0].Operator" is "opLike" and there's no entry for that and that causes this select to end with "W.CancelAll".

What's wrong here? Shouldn't it be an entry for "opLike".

---

Delphi-11, WIN10

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: ORMSelectAll('Name_Last LIKE ?',[aName+'%'],(aName='')) does not work

I guess this storage engine does NOT support many SQL commands .
See:
https://synopse.info/files/html/api-1.1 … GEINMEMORY

larand54

Member

Registered: 2018-12-25

Posts: 96

Re: ORMSelectAll('Name_Last LIKE ?',[aName+'%'],(aName='')) does not work

You are right, it works when running using external tables, at least against SQLite3.
Thank's for the help!
But isn't this a limit to this framework? I've thought that it should be transparent to any storage? Or am I wrong?

---

Delphi-11, WIN10

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: ORMSelectAll('Name_Last LIKE ?',[aName+'%'],(aName='')) does not work

But isn't this a limit to this framework? I've thought that it should be transparent to any storage? Or am I wrong?

Its not a limit. Its intentional.
It makes no sense to write a SQL engine if there are so many available out there. And sqlite3 is a perfect candidate for (static) embedding).
The TSQLRestStorageInMemory has its (dedicated) uses. If you know its limitations, you know when to use it.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: ORMSelectAll('Name_Last LIKE ?',[aName+'%'],(aName='')) does not work

Yes, this is by design.
TSQLRestStorageInMemory is a minimal in-memory engine, with only basic CRUD support over JSON or Binary persistence - which is fine if it is only what you need.
If you want true SQL support, use a real engine, e.g. SQlite3.
One purpose of TSQLRestStorageInMemory is e.g. as the storage engine for a front-end TSQLRestServer instance. You don't publish any CRUD ORM endpoints over it, just SOA endpoints. If you don't store anything, then TSQLRestStorageInMemory is pretty much ideal.