FTS indexes are searched with DBFIND. The DBFIND item parameter specifies
the FTS index and the argument parameter the query.
DBFIND (db, set, mode, status, item, argument)
The DBFIND search argument consists of keywords (or ranges), separated by
operators. A leading operator relates the search to previous search results.
[op] word [[op] word...]
For the image3k DBFIND the search argument must be terminated by either a
semicolon or a NUL character.
- word is a keyword or a range. Words are separated by operators.
Double quotes may be used to designate a literal word.
- and op indicates a boolean operator
The keywords are searched in the FTS dictionary index and operators define
the relationship of the keywords. For example:
mike AND wuppertal
This consists of the keywords mike and wuppertal. As the boolean AND
operator is used only records qualify having both mike and wuppertal.
Keywords (and operators) are case insensitive by default, except for keywords
that belong to an FTS index designated specifically to be case sensitive.
A range may be specified by providing a minimum value, the range operator (:)
and a maximum value. Either the minimum value or the maximum value may be omitted
to specify an open range.
A:B, 10:15, X:
indicates all words between A and B (inclusive), any values between
10 and 15 or any words larger than X.
Pattern matching and generic words
Partial words, such as MAN@ (matching MANAGER, MANAGED, ...) may be
used. In addition, match characters, such as "?" or "#" may be used.
MAN@ or AB#D
This matches any words that start with "MAN" or words that start
with "AB", followed by a digit and the character "D".
The boolean operators AND, OR and NOT may be used to define keyword
relationships. For example,
mike AND NOT wuppertal
would only qualify records with "mike" but not wuppertal. The second
example would qualify any records NOT having the keyword test.
A space is considered an implied AND operator. Consequently, the following
examples are equivalent:
mike AND wuppertal
The operator precedence is: ":" (range) > NOT > AND > OR
A AND B OR C -> (A AND B) OR C
A AND NOT B -> A AND (NOT B)
A:B NOT AB -> (A:B) AND (NOT AB)
Parentheses may be used to explicitly specify the precedence of boolean
Keywords are words or ranges, separated by operators or spaces.
When searching numeric fields, only numbers (or numeric ranges)
are valid. In numbers a dot (.) is understood as the decimal
Use double quotes around a keyword value to prevent it from being
parsed. This is useful when searching for keywords that contain
special characters or spaces or when a keyword could be interpreted
as an operator (for example, "OR" in a search for a state code).
A keyword range may be specified by supplying a start value, the
range operator and a stop value. Either the start value or the
stop value may be omitted.
If both the start and stop values are present, any keywords between
(and including) the specified values qualify.
If either the start or stop value is not present then a range specifies
an open range qualifying any value from the start or up to the stop
The first example qualifies all keywords between "a" and "z", the
second example qualifies any keywords between 2011 and 2012.
The third example qualifies any keyword larger than or equal to 2011.
The fourth example qualifies any keyword smaller than or equal to 2012.
Ranges can also use partial keywords by appending an at sign (@).
||Range with start and stop value
||Same using relational operators to specify a range
||Open range with a stop value specified (<= relation)
||Same using relational operator
||Open range with start value specified (>= relation)
||Same using relational operator
||Range using partial keywords
Relational operators are an alternate means for specifying ranges.
||Equal to (redundant)
||Greater than or equal
||Less than or equal
A relational operator is specified with the keyword (no separating
space allowed). For example, >=A@ has the same meaning as A@:
indicating an open range starting with A.
Two relational terms may also combined to specify a range
with both a lower an upper boundary.
For example, >=A@<=B@ is equivalent to A@:B@.
Please note that no space is allowed between the two relational
dbfind supports the following wildcard characters anywhere in a
character keyword argument:
In relational expressions, wildcards may only be used at the end of an
- ? - represents any single printable character
- # - represents any single digit (0-9) of an ASCII number
- @ - represents any number of ASCII characters, including spaces
Soundex phonetic searches
To specify a phonetic or "Soundex" search on an FTS field add the Soundex
operator ( ! ) to a keyword(s). For example, ALAN! AND CHRIS!.
If the FTS field does not support Soundex searches (the SX option
must be specified for the FTS field) a non-zero database status is returned.
If a Soundex field is grouped with other fields, be sure to specify the
Soundex field name in the field parameter when calling dbfind for a
Applying search results to other sets
FTS search results may be applied to another set. In this case item values
from the previous FTS search results are used as search criteria for the
FTS field passed in the DBFIND item parameter.
To use this function, an "&" character must be specified as the first
character in the DBFIND argument parameter. An item name may be specified
as an option.
The specified FTS field is then searched for item values obtained from
a previous FTS search.
The target field (passed in the DBFIND item parameter) must be an FTS index
in the specified data set.
If the no item name is present, the master set search item is used implicitly.
Consequently, this mode cannot be used for detail sets (unless the previous
FTS search was on an aggregated FTS field).
If the optional item is present, the given FTS field is searched for values
of the specified item. The FTS search results are consolidated if the item
is in a master set to avoid duplicate values.
Referencing previous search results
Previous search results may be refined with subsequent searches by starting
the argument with a leading AND or OR operator.
After performing an FTS DBFIND a leading AND or OR operator may be used to
reference the previous search result. In this case the FTS search is applied
to the previous FTS result. This allows to refine the search results, add to
it or subtract from it by specifying additional keywords in Boolean operations.
A leading AND operator and any keywords that follow it intersects the previous
and current search results. A leading OR operator and any keywords that follow
it add the current search results. A leading AND NOT operator and any keywords
that follow it filters the previous search results by the current search.
For example, an initial search could locate all customers in the state California.
A subsequent search could then be used to remove any customers in San Francisco
from the results.
To reference previous search results without further qualifying, place
an asterisk ( * ) alone in the argument parameter.
When no records qualify
If a search does not qualify any records a database status 17 is returned.
Even if all keywords in the search argument exist no entries may qualify
based on the boolean conditions. For example,
mike AND wuppertal
does not qualify any entries if either keyword does not exist or do not
apply to the same record (or the same master record for an aggregated
By default, the FTS result is reset if no records are qualified and a
subsequent DBGET will return an end of chain condition. As an option
though, it is possible to retain previous FTS search results so a search
may be re-tried by the user.
For the Eloquence language this behaviour is specified with the DBFIND mode.
For the image3k DBFIND, DBCONTROL may be used to configure this behavior.
Undo previous search
After every FTS DBFIND (except those that qualify no records) the currently
qualified and previously qualified results are saved in memory.
Two results are maintained:
This allows to undo the most recent dbfind keyword search.
For example, if an FTS search qualifies 100 customers initially
and the result was subsequently limited to customers in Wuppertal
it is possible to undo the last search and re-instate the previous
result reflecting 100 customers. One may then retry the search with
- The dbfind search result just performed
- The dbfind search results that immediately preceded the search
To back out of the most recent search, call DBFIND in "undo"
Note that only the last search performed may be backed out. Any attempt to
perform several undo operations in succession, will result in a non-zero database status.