REFERENCE LIBRARY
LEARNING CENTER

SQL Search

To continue with this content, please log in with your Data Access ID or create a new account.
Cancel Data Access ID
You may not be authorized to see this content. Please contact Data Access Europe for more information.
Cancel Data Access Europe
You are not authorized to see this content.
Cancel Data Access Europe

DataFlex courses

Information

SQL Search
Introduction (2m 01s)
Embedded SQL using the freetexttable function (2m 42s)
Next lesson:
Search multiple tables using a stored procedure
Cancel

DataFlex – SQL Search

Lesson 3 – Embedded SQL using the freetexttable function

To use a query like this in DataFlex you need to use embedded SQL and you cannot use a data aware list, you need to manually fill the list. So we’re going to use embedded SQL to execute the query, get the results and put that in a manually filled list. In this lessen you will learn how to do that.


  1. You’ll see that the view is slightly different now, the columns are not data aware and the list is manually filled.
  2. You can do this by implementing the ‘OnManualLoadData’, the search function is now calling the grid refresh to trigger the refill of the list.

  3. The ‘OnManualLoadData’ first gets the filter value and then it determines the indexes of the columns, so you can move the objects around without this procedure having to be rewritten. It’s building the big SQL statement

  4. In the SQL statement it will insert the search value in the FREETEXTTABLE definition.
  5. Escape this search string to prevent SQL injection issues.

  6. Use an existing connection from the track table, so you don’t have to create a new SQL connection every time you do this.

  8. Execute the SQL statement by opening a statement and calling the ‘SQLExecDirect’.
  9. Now fetch the rows by using a fetch loop and getting it into an array of values.
  10. Fill the grid rows that you received by reference when ‘OnManualLoadData’ was called and put in the value for every column of the list, do this for every row.
  11. We are using the indexes that we determined in the beginning of the procedure to know where we need to put the value.
  12. When you run this sample you will see an empty list because we didn’t search for anything yet.
  13. If you type ‘body count’, you’ll see that it still provides us with the results and still have bodies in there.
  14. If for example you type ‘body count anthem’

  15. You see that ‘body count anthem’ now pops up on top because it has a higher relevance rating.
  16. This sample is just searching for tracks, in the next lesson we will extend that to albums and artists.
©2024 Data Access Worldwide, all rights reserved
Privacy policy Cookies About dataaccess.com Online Documentation