mORMot Open Source

hanb

Member

Registered: 2018-03-05

Posts: 5

how to use 'like' keyword for fuzzy query?

Example:
    In 'SQLite3\Samples\01 - In Memory ORM', such as below is OK:

procedure TForm1.FindButtonClick(Sender: TObject);
var Rec: TSQLSampleRecord;
begin
  Rec := TSQLSampleRecord.Create(Database,'Name=?',[StringToUTF8(NameEdit.Text)]);
  try
    if Rec.ID=0 then
      QuestionMemo.Text := 'Not found' else
      QuestionMemo.Text := UTF8ToString(Rec.Question);
  finally
    Rec.Free;
  end;
end;

   
    But, replace with 'Rec := TSQLSampleRecord.Create(Database,'Name like ?',[StringToUTF8(NameEdit.Text)]);', it doesn't work.

    And, Rec := TSQLSampleRecord.CreateAndFillPrepare(Database,'Name like ?',[StringToUTF8(NameEdit.Text)]);
    Then use 'while Rec.FillOne do' clause to query records, also there no records return. Why? How to use 'like' keyword for fuzzy query?
   
    Please help me!

Last edited by hanb (2018-03-05 03:33:22)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: how to use 'like' keyword for fuzzy query?

Because the engine used is an "In memory ORM", i.e. a TSQLRestServerFullMemory:

/// a REST server using only in-memory tables
  // - this server will use TSQLRestStorageInMemory instances to handle
  // the data in memory, and optionally persist the data on disk as JSON or
  // binary files
  // - so it will not handle all SQL requests, just basic CRUD commands on
  // separated tables

"LIKE" is not handled by TSQLRestStorageInMemory.

You will have to use TSQLRestServerDB, and SQLite3 engine, to have full SQL support.

hanb

Member

Registered: 2018-03-05

Posts: 5

Re: how to use 'like' keyword for fuzzy query?

Thanks a lot! I'll try it.

hanb

Member

Registered: 2018-03-05

Posts: 5

Re: how to use 'like' keyword for fuzzy query?

Because the engine used is an "In memory ORM", i.e. a TSQLRestServerFullMemory:

/// a REST server using only in-memory tables
  // - this server will use TSQLRestStorageInMemory instances to handle
  // the data in memory, and optionally persist the data on disk as JSON or
  // binary files
  // - so it will not handle all SQL requests, just basic CRUD commands on
  // separated tables

"LIKE" is not handled by TSQLRestStorageInMemory.

You will have to use TSQLRestServerDB, and SQLite3 engine, to have full SQL support.

Hi, i'm coming too. Thanks again your help!
I've tried it with yours, as follow:

var
    Model: TSQLModel;
    db: TSQLRestServerDB;

In FormCreate:

   Model := TSQLModel.Create([TOrders]);
   db := TSQLRestServerDB.Create(Model, ChangeFileExt(paramstr(0), '.db3'));
   TSQLRestServerDB(db).CreateMissingTables;

    I use:
        Orders := TOrders.CreateAndFillPrepare(db, 'SSDNo like ?', [Trim(edtQueryParam.Text)]);
    Still, there's no records return. But I check it in DBManager, the record exists.

    Use:
        Orders := TOrders.CreateAndFillPrepare(db, 'SSDNo like ?', ['%']);
    All records return.

    So now, I have no choice but to use pos funtion to find the record in all returned records inefficiently.

    What's the problem?  I miss some important clause or settings?
   
    You said 'SQLite3 engine' yestoday. I missed setting SQLite3 engine?  How to use?

bigheart

Member

Registered: 2014-08-01

Posts: 53

Re: how to use 'like' keyword for fuzzy query?

Try as below:

Orders := TOrders.CreateAndFillPrepare(db, 'SSDNo like ?', ['%'+Trim(edtQueryParam.Text)+'%']);

hanb

Member

Registered: 2018-03-05

Posts: 5

Re: how to use 'like' keyword for fuzzy query?

Try as below:

Orders := TOrders.CreateAndFillPrepare(db, 'SSDNo like ?', ['%'+Trim(edtQueryParam.Text)+'%']);

Successfully done! Thanks so much! And there's  the last question: '?' is a wildcard, why  adding extra '%' to the begin and the end?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: how to use 'like' keyword for fuzzy query?

? is a place-holder for the parameter, not a wildchar.
It will replace ? by the value supplied as parameter, and bind it to the (prepared) SQL query.
So you need to follow the standard LIKE syntax by adding % characters.

hanb

Member

Registered: 2018-03-05

Posts: 5

Re: how to use 'like' keyword for fuzzy query?

? is a place-holder for the parameter, not a wildchar.
It will replace ? by the value supplied as parameter, and bind it to the (prepared) SQL query.
So you need to follow the standard LIKE syntax by adding % characters.

My question comes from '\SQLite3\Samples\35 - Practical DDD\04\infra\InfraConferenceRepository.pas  line 56.

rec := TSQLBooking.Create(fRest, 'Name like ? and FirstName like ?',
     [Attendee.Name, Attendee.FirstName]);

There's no '%' when use 'like'. So I'm puzzled.