mORMot Open Source

igors233

Member

Registered: 2012-09-10

Posts: 241

FormatUTF8 and LIKE condition

I'm using SQLite as backend database and trying to filter records with CreateAndFillPrepare using LIKE condition, raw SQL condition I'm after is:
"...WHERE Field1 LIKE '%value%'"
So I would like to find text anywhere within field and search to be case insensitive.

Problem is how to generate that condition with FormatUTF8, if I try to use bind syntax as in
FormatUTF8('Field1 LIKE ?', [], ['value']), I get "Field1 LIKE ''value''):" and as result no records are returned.

So, I've skipped bind and tried to directly place % in result, but with FormatUTF8 that task is kind of complicated
As an example consider these samples:
FormatUTF8('Field1 LIKE "%%%" OR Field2 LIKE "%%%"', ['%', 'Value1', '%', '%', 'Value2', '%'], []));
Format('Field1 LIKE "%%%s%%" OR Field2 LIKE "%%%s%%"', ['Value1', 'Value2']);

They both will return same (desired) result, standard Format is a bit easier to follow, so I would suggest to add some special two char combination that would instruct FormatUTF8 that % is part of string, not argument placeholder.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: FormatUTF8 and LIKE condition

The correct way is to use ? which would create parameters, not inlining the value, which is slower and unsafe.

FormatUTF8('Field1 LIKE ?', [], ['value']) is therefore the expected way of using it.

Use the debugger (or at least enable logs) to see which SQL is executed, and which JSON is returned.

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: FormatUTF8 and LIKE condition

> The correct way is to use ? which would create parameters, not inlining the value, which is slower and unsafe.
> FormatUTF8('Field1 LIKE ?', [], ['value']) is therefore the expected way of using it.
> Use the debugger (or at least enable logs) to see which SQL is executed, and which JSON is returned.

Sorry, I don't understand, do you mean to write FormatUTF8('Field1 LIKE ?', [], ['%' + 'value' + '%'])?
That is to include % chars in bind params?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: FormatUTF8 and LIKE condition

yes