Developers

Omnis Technical Note TNSQ0022 October 2008

Intelligent SQL Search

For Omnis Studio 3/4
By Andreas Pfeiffer

Omnis developers ask me quite often why in SQL you cannot fetch backwards - after all, it seems a reasonable question! In ANSI SQL, a function to browse backwards has not been implemented because with large amounts of data this would not make much sense. Imagine a user would have to browse through tens of thousands of records. It would be much more sensible to implement a search that displays a particular set of search results, and then allow the user to browse within the results to further refine the search results.

To provide such functionality, you must first enable the user to insert search criteria, for example, in an address details window. Using such a window, a user could enter "Miller" in a name field and "London" in a city field. When clicking on a search button, all Millers located in London would be listed. It is also important in this case that not only all instances of "Miller" are found, but also all instances of "Millerton", "Miller-Brown", etc, are listed, that is, all records that start with the string "Miller". The same is true of course for the city, therefore we want to find all instances of "Miller" located in London-Hampstead, London-Highgate etc., should be listed as well.

Let's assume you are using table classes, therefore it's easy to place a method in a table super class, to make sure it is available globally for all instances of any derived table classes. You could name the method $createWhereClauseFromCols.

Such a method would go through all columns of the current row variable and put a where clause together that our SQL server can understand. Using a parameter, we can fill a local variable which will either be an exact match or a search with Like:

If pExact
  Calculate comparison as '='
Else
  Calculate comparison as 'like'
End If

The following loop will go through all columns and you can access the appropriate column with the reference "colRef":

Set reference colRef to $cinst.$cols.$first()
While colRef
  Set reference colRef to $cinst.$cols.$next(colRef)
End While

colRef().$name returns the name of the respective column. If we use this with a reference to the current row variable, we can also access the particular content: $cinst.[colRef().$name]

colRef.$coltype returns the type of a particular column. We can use this result, e.g. in a Switch statement, if we want to achieve different behaviour for different types of columns:

Switch colRef.$coltype
Case kCharacter
  ; do this
Case kDate
  ; do this
Case kInteger,kBoolean,kNumber
End Switch

In the case of Boolean or numeric values, a comparison with "Like" does not make much sense. Therefore, we can hard code the equals sign. It is a similar case with date values. (Please note the use of single and double quote marks.)

Case kInteger,kBoolean,kNumber,kDate
If $cinst.[colRef.$name]>0
  Calculate whereClause as con(whereClause," and ",colRef().$name," = '",$cinst.[colRef.$name],"'")
End If
...

In the case of Character columns, it looks a little bit different:

Case kCharacter
Calculate whereClause as con(whereClause," and ",colRef().$name,"",comparison,"'",$cinst.[colRef.$name],pick(pExact,"%'","'"))

Such a method composes a string that we can use as a Where clause for the SQL request, taking into account the type for each column. Finally, the method has to return the string. If the local variable "whereClause" contains the string we will cut out the first "and" and replace it with a "where":

If len(whereClause)>0
  Quit method con("where",mid(whereClause,5,len(whereClause)))
Else
  Quit method ''
End If

Now we have a method that we can re-use many times to create a Where clause, but the question is, what is the best way to use it? The call on our row variable works as follows:

Do myResultList.$select(myRow.$createWhereClauseFromCols())
Do myResultList.$fetch(kFetchAll)
If myResultList.$linecount()=1
  ; load myRow
Else
  ; open selected window and pass over list
End if

The row variable myRow contains the sample data (in our example "Miller" and "London"). The search will be executed with "myResultList". If the search returns only one record, you can load the row variable directly by initiating another $select to load the details. If you have several matches in the result list, you could open a window which displays the data found.

Therefore you only have to implement an appropriate messaging system to make sure that the selected record will be loaded in the main window. To do this, include a method $load with the record ID as a parameter. This method reads the record into the row variable. The window that contains the result list only needs a reference of the main window to communicate with the main window.

Alternatively you could also use a $sendall(), as explained in my tech tip "Polymorphism in Omnis", see: TNOO0002