Pages: 1

#1 Re: mORMot 1 » how to use 'like' keyword for fuzzy query? » 2018-03-07 07:05:46

hanb
ab wrote:

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

#2 Re: mORMot 1 » how to use 'like' keyword for fuzzy query? » 2018-03-06 06:48:17

hanb
bigheart wrote:

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?

#3 Re: mORMot 1 » how to use 'like' keyword for fuzzy query? » 2018-03-06 03:00:48

hanb
ab wrote:

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?

#4 Re: mORMot 1 » how to use 'like' keyword for fuzzy query? » 2018-03-05 09:42:06

hanb

Thanks a lot! I'll try it.

#5 mORMot 1 » how to use 'like' keyword for fuzzy query? » 2018-03-05 03:32:00

hanb
Replies: 7

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!

Pages: 1

Board footer

Powered by FluxBB