13. Searching by Example (DBQuery)

Expression Constants
Field Names
Intrinsic Functions and Operators
Constructing Search Expressions
Clearing a Search Condition

The DBQuery function (formerly DBLocate) allows you to set up complex queries (i.e., this is greater than that and that is less than this). The power in DBQuery is that it allows you to very tightly narrow in on what you're looking for. However, DBQuery operates by iteratively scanning the entire database, which means that it takes much longer to find something that's near the end of the database file than something that's near the beginning. In general, you should use DBQuery when your dataset size is relatively small. For much larger databases, consider turning to indexed searches (DBSeek).

The DBQuery function requires a single argument, which is a search expression string enclosed in quotation marks. This string must evaluate to a logical expression (i.e., interpreting it must lead to a "TRUE" or "FALSE"conclusion). For example, this line:

  DBQuery("SALES > 50000")

will set up a search condition which tells FileFlex that you only want to see records where the value in the database field called "SALES"exceeds $50,000. The DBQuery function will move you to the first matching record, assuming one exists.

The DBQuery logical expression can consist of constants, field names, and functions, joined together by operators. In the example above, there is a field name ("SALES"), a constant (50000) and an operator (">", meaning greater than). The key to using DBQuery is learning to construct a query expression.

Expression Constants

Constants in FileFlex DBQuery expressions can be numeric, character strings, or logical values:

A numeric constant contains a number (e.g., 65, 7.9, or -2932).
A character constant is any string of letters, numbers, or special symbols enclosed in single quotation marks (e.g., 'FileFlex', 'comedy', or 'R'). Take considerable care to surround the DBQuery expression character constants in a single quotation marks, rather than the double quotation marks common the the typical string definition of C, Lingo, or HyperTalk.
A logical constant is one of these values: .TRUE., .FALSE., .T., or .F. Notice that these constants are surrounded by periods; these are essential for FileFlex to understand your constant as the logical value, rather than a string.

If a field in your database file contains a logical value and you want to test for it, you must be sure to provide the constant correctly. For example, the field called IN_STOCK in a video database file could be designed to contain a TRUE or FALSE value. To locate only videos that are in stock, you could set up the logical expression this way:

  put DBQuery("IN_STOCK = .T.") into dbResult

but if you did this instead:

  put DBQuery("IN_STOCK = 'T'") into dbResult

you would not find any records because FileFlex would look for the expression string 'T' in the field IN_STOCK rather than the logical value ".T."(meaning, of course, "TRUE").

Field Names

You must insure that field names in your DBQuery logical expressions match the database file's field names exactly. As always, case doesn't matter but punctuation does. Thus if the database field is called IN_STOCK and you attempt to define a value for a field called INSTOCK, you will produce an error. Be sure to remember that field names are NOT enclosed in quotes.

Intrinsic Functions and Operators

DBQuery allows you to use certain intrinsic functions (functions built into the expression analyzer) that can aid your search. One of the most useful is UPPER. UPPER converts the string being compared to upper case. By placing UPPER in your query expression, you can be sure that you'll find a string, regardless of the case stored in the database. For example:

  put DBQuery("UPPER(FIRSTNAME) = 'DAVID'") into searchResult

Since we don't know whether the field FIRSTNAME contains "David"or "david" or "DAVID", the UPPER function allows us to find without regard to case. All of the intrinsic functions are defined in the Intrinsic Function Reference.

Constructing Search Expressions

Search expressions are pretty easy to construct. Even so, we get many technical support calls from users who get confused about what goes into the various strings. It gets even more interesting when a user wants to construct a search expression containing the value of a host-environment variable. So let's break it down for those using Lingo or HyperCard.

First, the typical search expression begins with the function call, followed by an open parenthesis. Everything between the open parenthesis and the closing parenthesis is the search expression:

              search expression goes below
              ----------------------------
  put DBQuery(                            ) into searchResult

To your host programming language, the search expression is simply a string. Therefore, you could put the string into a variable and call pass the variable to DBQuery, as in the following example:

  put "UPPER(FIRSTNAME) = 'DAVID'" into queryString
  put DBQuery(queryString) into searchResult

Notice that the query string, like most normal strings, is bounded by double quotes and placed into the variable queryString. Again, the host environment only knows this as a string and does not parse the contents of the string.

But what if you wanted to replace the literal 'DAVID' with a string of your own choosing? Let's assume that you wanted to find the record matching the contents of the variable myFirstName. What many people do, and what WILL NOT WORK is the following:

  -- the following won't work
  put "UPPER(FIRSTNAME) = 'myFirstName'" into queryString
  put DBQuery(queryString) into searchResult

All the above does is ask DBQuery to find a record where the contents of the field FIRSTNAME contains the literal string 'myFirstName'. What you need to do is construct a complex string. This is just pure Lingo or HyperTalk, there's no FileFlex magic here. The easiest way to see this is to construct a string in the following way:

  -- store the first half of the query expression string
  put "UPPER(FIRSTNAME) = '" into firstHalf
  -- build the string properly
  put firstHalf & myFirstName & "'" into queryString
  put DBQuery(queryString) into searchResult

Take extra care to notice that the single quote (') is contained in the double quotes and is passed to DBQuery. By making sure that the myFirstName variable is passed outside the double quotes, you're ensuring that it's evaluated by your host environment before it's passed to DBQuery.

Confusion evaluating strings has got to be one of the most common technical support calls. So read and study the descriptions above, learn about strings in your host language, and please make sure you understand how strings work in your host language before calling us up. We'll just tell you to read this section anyway!

Clearing a Search Condition

Once you have set up a search condition with DBLocate, it stays in effect until you invoke another one. DBSkip will follow the database sequence looking for records that match the criterion in the last DBLocate command. This chain is broken by use of the command:

  DBLocate("")

Thereafter, DBSkip will revert to its previous mode of operation (sequential or sort-order movement through the file).

  [Previous Chapter]    [Table of Contents]    [Next Chapter]