REFERENCE LIBRARY
LEARNING CENTER

Build your own Search Engine

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

Build your own Search Engine
Introduction (2m 51s)
Building a Search Engine (15m 29s)
Next lesson:
Cancel

Build Your Own Search Engine

Lesson 6: Building a Search Engine

This lesson focuses on building a search engine with DataFlex. What is a search engine? A search engine basically combines a number of result sets and presents them as a single, searchable result set.  This is advantageous because it allows the developer to set the criteria for searches that comb through all of an applications data whether it is user types, customers, and invoices, or album names, tracks, and artist like the example used in this course.


The steps to creating a search engine:

  1. Determine the tables to search, this can be determined ahead of time or added incrementally as needed
  2. Build a database view for searching, use SQL Server Management Studio to build and test
  3. Create a cWebView object with List in your application
  4. Provide Drilldowns to cWebViews, a drill down should be assigned for each type of information that is available


In this lesson it is assumed that step one, determining the tables to search, has already been completed. Moving on to the second step, a view needs to be created that is going to concatenate multiple result sets. For each area that is going to be searched, a result set needs to be created and they need to be concatenated. 


Important: The first result set that is created determines the names of the columns. Each of the result sets must have the same number of columns. Each of the defined columns must have the same data type in every result set. The following example will continue using the Chinook music library database that was used in Lesson 1. Several steps have been prepared in preparation for this lesson. Start by building a view that is going to concatenate several result sets. In this example, the search engine view created will have three columns: the source, the id of the row that is being shown and the description of the row that is being shown. 


  1. In “SQL Server Management Studio” create a view that displays the source, ID, and description. For this example, the source will be [Track], the ID is [TrackID], and the description is [Track].[Name].
  2. Again, this first result set determines the column names. 
  3. Use UNION to concatenate all desired result sets.        
  4. Note that in the example:
  5. A conversion to nvarchar (255) was made for the ID, so that this view can produce an integer’s ID, text IDs and others. 255 is the maximum length.
  6. Fields can also be concatenated. In this example, FirstName was concatenated with LastName.
  7. The view is brought into DataFlex by using the method DATABASE >>>CONNECT TO DATABASE TABLES found in the top toolbar.
  8. Right click on the desired table from the Table Explorer on the left (vwDataFlexFind in this example) and select VIEW TABLE to view the results.
  9. As was shown in Lesson 1, a select has been created for this example called “TestSelect.wo.” The only difference with TestSelect.wo is that when viewed there is only one column with the first row showing the description, and the second row showing the source.
  10. Regarding the constraint in this this example…
  11. The value is entered into a form, and then it is trimmed.
  12. Important: an extra line has been added, SQLEscapedStr, to remove any possible SQL characters that the user may mistakenly enter into the search field. This sanitizes the input, so that the characters entered cannot be mistaken as part of an SQL statement.
  13. Following that, it shows that if a value is greater than “”, the SplitString function is called.
  14. This causes a search with multiple keywords to be split into an array or strings. For example, a search of “Queen Hits” will be split into “queen” and “hits.”
  15. To connect a zoom to an action such as an ‘on click’…
  16. In the OnRowClick procedure, determine what is the DataSource of the current row that has been clicked on and what is the zoom. The zoom also needs to be activated.
  17. A NavigateForward command needs to be added to activate the zoom. The zoom also needs to know what record is to be shown. To get the record, the activated zoom is going to call back with OnGetNavigateForwardData. The ID of the table it needs to show needs to be defined as well as the record identifier/ID that needs to be shown.
  18. In this example, the zoom being connecting is for tracks. The data source is track, and the zoom is oTrackZoom.
  19. IMPORTANT
  20. Register the zoom at the top of the OnRowClick section, Register_Object oTrackZoom
  21. Declare the data file at the top of the code, Declare_Datafile vwTrack
  22. To test, run it by clicking the COMPILE icon on the top toolbar, select the SEARCH tile, and then write “Queen Bohemian” into the search bar.


Conclusion

What was just shown is that a complete search engine can be created in DataFlex by simply building a view. The view combined multiple data sources together, and then brought it together in a select window in the DataFlex Framework. 

Points to remember:

  • SQL can really speed up search functions
  • Sanitize SQL input to protect against SQL injection
  • Use views like tables; really powerful for finding data
©2024 Data Access Worldwide, all rights reserved
Privacy policy Cookies About dataaccess.com Online Documentation