[SOLVED] Tsqlite3dataset locate fails on memo fields

[JanRoza]

In my program I've build in a search function on a tsqlite3dataset which works okay on all fields except on memo fields.
So if memo field contains "My shoes are new" and I search for "shoes" it is not found whereas if memo contains "Shoes are made for walking" it is found.
In the sqlite4fpc website in tutorial 3 I see a reference made to soWildCardKey which should make possible what I want.
Is this correct?
And if so how do I set this option on runtime via coding, I tried:

Code: [Select]

tblVluchten.Options := [soWildcardKey];

Code: [Select]

tblVluchten.Options := '[soWildcardKey]';

Code: [Select]

tblVluchten.Options := soWildcardKey;
but none of this compiles.
Can anyone tell me the correct syntax to achieve this?

Thanks!

Jan
(Lazarus 0.9.29 and fpc 2.4.3)

[LuizAmérico]

this would work:

tblVluchten.Options := [soWildcardKey];

(It's necessary the CustomSqliteDS in uses clause)

or set Options.soWildkardKey to true in Lazarus object inspector

BTW: i dont know if the version of 2.4.3 has this feature. Search soWildcardKey in CustomSqliteDS.pas. If not you must use the one found in http://sqlite4fpc.yolasite.com/ (read the instructions)

After that do:

Locate('Name', '*shoes*', [loCaseInsensitive]);

[JanRoza]

I checked my version of CustomSqliteDS and it already had the soWildcardKey in it.
But your tip to include CustomSqliteDS in the uses clause was the golden tip, it works like a charme now.
Never knew of the existence of CustomSqliteDS so hadn't include it.
Would it not be better to automatically include it if a tsqlite3dataset is selected and place on a form?
Anyway I've learned something again tonight and won't forget this one!

Thanks Luiz 

[LuizAmérico]

Would it not be better to automatically include it if a tsqlite3dataset is selected and place on a form?

I will take a look to see if is possible

[anderson5420]

First, I would really like to thank Luiz for the Sqlite3Dataset component, it is both the easiest to use and most reliable database component going!

Everything works in my little paper file indexing program except the wildcard locates. I do have my FilesDataset.Options set to [soWildcardKey] (happens in the form's OnCreate event), have CustomSqliteDS in my main unit Uses clause, and am using the *wildcards in the SearchEdit, but it still is not working.  Some questions:

1 - Is there something else I could be overlooking with the wildcard locate?  This is really the last functionality that I want to implement.

2 - Just a confirmation, Locate and LocateNext only accept a single field, they do not accept a comma separated list of fields, correct?

3 - As an alternate, how would you build a select...where Field1, Field 2 like [SearchEdit.Text] order by Field1, Field2 to assign to the FilesDatast.SQL property?  I have tried everything I can think of, I know the literal ' character is '', but no she go...

Thanks!

[anderson5420]

OK, answered question 3 this afternoon - this seems much more powerful than Locate / LocateNext because it selects the records that have the search text in any fields you specify, with each field taking a separate where [Field] like '%[SearchText]%'  Here is what works:

Code: [Select]

SQLText := 'select * from files where Filename like '''+'%'+SearchText+'%'
      + ''' or Reference like '''+'%'+SearchText+'%'+''' or Comments like '''+'%'
      + SearchText+'%'+''' order by Reference, Filename';    

When the user clicks the "Go" button, the select statement above returns all the records containing the SearchText in either the Reference, the Filename or the Comments fields.  The '%' is the wildcard character for the 'like' statement, matching any text before or after the SearchText. There is a "Clear Search" button that sets the SearchEdit.Text to '' and executes a 'select * from files order by Reference, Filename' - next up, add in the error checking I skipped over while trying to get the basic functionality working!  Fun!

  

[totya]

In my program I've build in a search function on a tsqlite3dataset which works okay on all fields except on memo fields.
So if memo field contains "My shoes are new" and I search for "shoes" it is not found whereas if memo contains "Shoes are made for walking" it is found.
In the sqlite4fpc website in tutorial 3 I see a reference made to soWildCardKey which should make possible what I want.

This topic doesn't solved really, because this problem is stay, soWildCardKey doesn't work:

http://sqlite4fpc.yolasite.com/dataset-tutorial-3.php

It's also possible to use wildcards in the search key, e.g., searching for '*Paul*' will match any value that has 'Paul' regardless of the position. To enable this option set soWildCardKey in ContactsDataset.options.

I played a bit around this bug, and I have found the solution, if loPartialKey removed from "Tutorial 03" code:

Code: [Select]

Options := [loCaseInsensitive, loPartialKey];
then wildcard search is working.

[LuizAmérico]

I adjusted the tutorial to remark that wild and partial search are mutually exclusive

[totya]

I adjusted the tutorial to remark that wild and partial search are mutually exclusive

Hi!

It's a good idea, but I think the best, if something Exception come from the source code (Locate/Locatenext), if these two options selected both.

Edit.:

Wait a moment, first, if you are the developer of Tsqlite3dataset then thank you, the second, I think the downloadable component on this site is older than recent Lazarus source (I compared them).

[LuizAmérico]

Thanks. I updated the site.

Having both loPartialKey and soWildCard options has valid use cases (think that you mostly use wild search, but in specific cases wants to use partial search), so no need to throw an error