In the SQL manager you can see we are using the Chinook search database, in this database there is a full text catalogue that indexes the several fields of the database.
To use this within a query, simply use the FREETEXTFILTER in the ‘where’ class. The FREETEXTFILTER is being used to search for body count on the name and composer.
Select all the columns from the track and execute this query. The results will show up on the bottom of the screen
In the results you can see that only the tracks that have ‘Body Count’ in the name show up.
If you run it without the filter, it will show all the records.
That is how you use the FREETEXTFILTER in a SQL query.
The next step is to use this FREETEXTFILTER in DataFlex. The simplest way to use FREETEXTFILTER in Dataflex is by setting it as a filter on the data dictionary.
Open the DataFlex Studio and open the chinook search workspace.
In the chinook search workspace there are several views and the one to look at is the SQL full text search view.
Within this view you can see several objects, a DEO structure so the data dictionaries are all available. There is a search form on top and there is a list, which has several columns and these columns are simply data bound. So the track name is bound to the track name database field, it’s a data aware list.
When the user searches, we execute the search function. We’re getting the value from the field and are putting that in a property that we have defined, the ‘psFilterValue’.
We need to use this separate property first and we cannot read the value directly in the data dictionary, because that would cause the filter to be changed on the client side causing synchronization issues.
Here is the ‘OnConstrain’ procedure that constrains the data by setting a ‘psSQLFilter’ and the psSQLFilter is just generated as a string, so here you can see the free text filtering on name and composer.
Percentage 1 is replaced with the filter value. The filter values escape to make sure there is no SQL injection. This is how the list is filtered. Run the example and go to search to get a list with all the data.
Now if you search for ‘body’, you can see that it finds several results.
The language enhancement is in action here to, because it also finds ‘bodies’.
REFERENCE LIBRARY
