DataFlex – SQL Search
Lesson 4 – Search multiple tables using a stored procedure
In the previous lesson we only searched for tracks, in this lesson you will learn how to search for tracks, albums and artist.
To do this with the SQL FREETEXTTABLE function you would get a complicated query and you don’t want to have this complicated query all in DataFlex code, because that’s always is a bit clumsy.
We’re going to use a stored procedure, to create a stored procedure on a SQL database:
1. Use the create procedure function.
2. Give the function a name.
3. Define a set of parameters.
4. Define the query you want to execute.
You can call it by using embedded SQL, it has a special API for calling stored procedure functions. We’re passing in the filter as a parameter and the stored procedure is going to return the table that we want show with the results.
- Back in the SQL manager open the ‘chinook search database’.
- On the left side expand the ‘Programmability’ which has stored procedures. Find the ‘dno.SearchMultiTable’ stored procedure.
- Selecting ‘Modifiy’ will show the SQL script that is being stored. In the SQL script you see several select statements that we’re joining together using ‘UNION ALL’. This will provide one big result set that is the result of multiple queries.
- Executing this is a lot simpler. In SQL you type ‘EXECUTE <the name of the stored procedure> @SearchString’ that executes it and provides the big table.
- After executing this, you see a lot of results. ID: The primary key on that specific table.FtsRank: The full text rank. Type: Data source which can be, track, album or artist.
- Going into DataFlex, you can see that there is a third sample.
- If you open this view, you can see it’s almost the same as the other ones.
- There are different columns, it has description and source. Also, the ‘OnManualLoadData’ now doesn’t have a query in it, but it’s going to execute the stored procedure. In DataFlex you can do this by doing a ‘SendSQLSetProcedureName’ and then ‘Send SQLSetArgument’. In this case set the first argument to the filter text. Encoding here is not needed because the ‘SQLSetArgument’ automatically makes sure that you cannot use a SQL injection to hack into the database. A SQLCall executes the stored procedure.
- Fetching the results works nearly the same.
- In this fetch loop we call ‘SQLFetchRowValues’ to get the different rows and we put that into the results row, the web grid rows, and they are shown in the list.
- When running this sample, you should now find multiple results including artists and everything else. So for example searching for Queen we get the artist Queen, tracks from Queen, and albums from Queen.
- If you search for ‘Queen greatest hits’ instead you’ll get the greatest hits from Queen, and all the tracks on ‘The Greatest Hits’ album.
- On the right you can see the relevance rating. ‘Killer Queen’ from ‘Queen’ is on top of the list because it has ‘Queen’ twice in the name.
- In this course you have learned how to use embedded SQL. You’ve learned about full text search index and stored procedures. These are some great tools to build search functions for your own applications. For more courses like this one visit: learning.dataaccess.eu
REFERENCE LIBRARY
SQL Search
