|
<< Click to Display Table of Contents >> Navigation: Apollo API Listing > sx_Query |
Declare Function sx_Query Lib "Apollo9.dll"
(ByVal cpExpression As Any)
As Long
LONG FAR PASCAL sx_Query
(BYTEP cpExpression);
Sets an ultra high speed filter. sx_Query uses open indexes to determine whether a filter can be constructed based upon index keys rather than the evaluation of data extracted from the table.
The query optimization algorithms used may result in filter setting and data extraction hundreds of times faster than standard xBase filters. If query optimization cannot be performed, a standard filter is set (see sx_SetFilter). sx_Query filter respects current filter settings and the setting of sx_SetDeleted.
cpExpression: A standard xBase expression that defines the query filter conditions. If sx_Query determines that the expression contains field references that match fields used to construct current open indexes, query optimization is performed.
For query optimization to work, a field referenced in the conditional expression must be the first field referenced in an index expression (i.e., the leftmost field if there is more than one).
For example, the benchmark test for sx_Query used the expression below to extract a 3 record subset from a 10,000 record table in less than one second.
lRec = sx_Query("STATE='CA' .and. (CITY='San' .or. CITY='Los')")
The table had indexes open on the fields CITY and STATE.
If xBase functions qualify a field in the index constructing expression, they should also be used in the query expression. For example, if the index expression is 'upper(country)', then the query expression should be in the form 'upper(country) = "GERMANY"'. If the query expression was passed as 'country = "GERMANY"' without the upper conversion function, the query would be set up properly and return the correct results, but it would NOT evaluate a record addition or change correctly if the user entered 'Germany' as the country. New records and edited records use the table data rather than the index key to determine whether or not the record is added to the query subset.
An index used in query optimization does not have to be the controlling index and the query conditional expression may use fields that reference more than one index (as in the STATE, CITY example above).
sx_Query sets a standard filter if it cannot use open indexes to evaluate the passed condition(s). sx_Query may use a mix of query optimization and standard filters (e.g., 'STATE = "CA" .and. AGE > 25' where AGE is not indexed).
Conditional expressions that contain references to aliased fields in tables linked to the current work area via sx_SetRelation are not optimized.
The fastest queries are built using
an equal condition (= or ==)
a less than (<) or less than or equal condition (<=)
a greater than (>) or greater than or equal to condition (>=)
(.NOT. field = condition) is much faster than (field <> condition)
To clear a query, pass the query expression as a NULL string (0&).
Clearing a query also clears filters set with sx_SetFilter.
If using values contained in variables to construct a query expression, the values must become part of the xBase expression string. For example, if a state code is solicited from the user and stored in a variable named 'statecode", the query expression would be constructed as follows:
lRec = sx_Query( "state = '" + statecode + "'" )
Logical operator precedence is .NOT., .AND., and finally .OR. sx_Query expressions with more than two elements using different logical operators to join the elements must be carefully constructed by observing the precedence rules.
For example, suppose we want a strange query subset that includes all persons whose last name begins with "B" or everyone who is married. These people must live in a city that begins with the letter 'C'.
"upper(last) = 'B' .or. married .and. city = 'C'"
This will give us the wrong answer. Because .AND. has a higher precedence than .OR., we will first get all married people who live in a city that begins with 'C'. Once this bitmap has been built, all the persons whose last names begin with 'B' are added to the subset - whether or not they live in a city that begins with 'C'. The correct form of the expression is
"(upper(last) = 'B' .or. married) .and. city = 'C'"
This changes the evaluation order and forces the first two bitmaps to be evaluated first with the .OR. operator.
In Boolean terms, .NOT. has the highest precedence and this rule is stricly adhered to An expression that is given as
"dtos(eventdate) = dtos(date) .and. .not. status = 'H'"
(which is perfectly valid in most xBase dialects) will be evaluated as
".not. (dtos(eventdate) = dtos(date) .and. status = 'H')"
which is not what is intended. If using .NOT., always parenthesize each expression in the query to ensure that evaluation proceeds according to intention. The expression above should be passed as:
"(dtos(eventdate) = dtos(date)) .and. (.not. status = 'H')"
In the first case, the pcode generated is of the form
field1value1=: field2value2=: &!
which creates a query bitmap for expression 1, another query bitmap for expression 2, then ands the two bitmaps and finally negates them. This is not what we want. By placing the .not. within the parenthetical expression surrounding the second condition, the following pcode is generated:
field1value1=: field2value2=: !&
This creates a query bitmap for expression 1, another query bitmap for expression 2, negates the second bitmap, and then ands the two bitmaps. This is what is intended.
sx_Query expression equations must be posed in terms of the data type of the index.
1. Character types require a string enclosed in double quotation marks.
upper(lastname) = 'SMITH'
2. Date types require a CTOD function. The date string must be formatted according to the settings of sx_SetDateFormat and sx_SetCentury.
hiredate > ctod('05/15/94')
3. Logical types simply use the field name.
married
4. Numeric types require a number (with no quotes).
age > 25
QKeyVal is a special xBase function that is designed for use with query optimization.
When building query expressions it is sometimes necessary to
(1) refer to the value of the key directly;
(2) target a specific index for optimization.
QKeyVal('tagname') allows us to do these things.
For example, suppose we had an index that was constructed with SUBSTR(DTOS(startdate),5), which indexes on the month and day of an xBase date. Now, if we attempt to create a query bitmap with this index using
sx_Query( "SUBSTR( DTOS( startdate, 5 )) > '0621'" )
we will get erroneous results (usually zero records) even though sx_QueryTest returns FULL optimization possible..
"Why?" you ask. When the query engine attempts to optimize an expression, it extracts the first field name it encounters in the expression and checks each index expression to see if it is also the first field referenced in the key construct. If so, when it comes time to build the bitmap, it replaces the field reference in the expression with the key value. Since the query optimizer allows the construction of complex xBase expressions on either side of an equation, it leaves the expression surrounding the field reference intact and executes it.
The result? If field "startdate" in the example above contained "19940630" then the key for that record would be "0630". The query engine would execute the expression as
SUBSTR( DTOS( '0630', 5 ))
which would return a NULL value and no records would satisfy the condition. We can't simply say "startdate > '0621'" because a date type cannot be compared to a string when the expression is tested for correct syntax.
We can properly execute the query by using a reference to the actual key value here instead of the expression used to build the index.
sx_Query( "QKeyVal( 'startdate' ) > '0621'" )
When using QKeyVal it is necessary to specify the tag name of the index because there is no "QKeyVal" expression allowed in an index expression so there is no way to check if the query can be optimized. This also provides a sideline benefit in that we can now target a specific index to be used for optimization. This is valuable if a conditional index exists in the current index list that has the same key expression as a master index. We don't know which one the query engine will use (and the results will be different depending on the index used to build the query bitmap).
We can use this to advantage if we wish to construct a bitmap from a non-optimizable condition and then switch index orders. Why would we want to do this? For speed. A non-optimized query sets a standard filter. A sx_Query bitmap can be hundreds of times faster than a standard filter in defining and traversing the required subset.
VB Syntax
' create a temporary conditional index
sx_Index( "sxtemp.idx", "last", IDX_NONE, False, "state = 'CA'")
' create a bitmap using this index with a condition
' that satisfies all keys in the conditional index
' (in this case no last name starts with "1")
lRec = sx_Query("QKeyVal('sxtemp.idx') <> '1'")
' remove the conditional index
sx_IndexClose
Kill Dirname + "sxtemp.idx"
' now we can view the subset in the order of any existing index
iPrev = sx_SetOrder( sx_TagArea("company"))
C Syntax
// create a temporary conditional index
sx_Index( "sxtemp.idx", "last", IDX_NONE, False, "state = 'CA'");
// create a bitmap using this index with a condition
// that satisfies all keys in the conditional index
// (in this case no last name starts with "1")
lRec = sx_Query("QKeyVal('sxtemp.idx') <> '1'");
// remove the conditional index
sx_IndexClose();
Unlink( "sxtemp.idx" );
// now we can view the subset in the order of any existing index
iPrev = sx_SetOrder( sx_TagArea("company"));
Note: When constructing the query bitmap with an eye towards including all of the records in the conditional index, use a NOT EQUAL (<>) condition which will be True for every key in the index. Do not use a .NOT. operator because this inverts the entire bitmap and will include records not found in the conditional index subset.
QKeyVal Rules
1. The tag name passed as a parameter to QKeyVal is the index name with extension (but not with path) in the case of NTX or IDX files. With CDX and NSX indexes, it is the tag name passed when the tag was created.
2. QKeyVal only extracts character type keys. Numeric, logical, and date keys cannot be extracted with QKeyVal.
If a query is successful, the record pointer is positioned to the first record in the subset and the record number is returned. If the query is not successful, zero is returned.
' Use Query optimization to set a filter
Sub bFilter_Click ()
Dim RecStr As String
Dim lRec As Long
If Not FilterOn Then
RecStr = InputBox$("Filter condition?", "Filter",
Ä "country = 'GERMANY' .and. city = 'BERLIN'")
If RecStr = "" Then Exit Sub
If sx_EvalTest(RecStr) Then
FilterOn = True
bFilter.Caption = "&Reset"
lRec = sx_Query(RecStr)
Else
MsgBox "Error in expression"
Exit Sub
End If
Else
FilterOn = False
bFilter.Caption = "&Filter"
lRec = sx_Query(0&)
sx_GoTop
End If
End Sub
case IDC_EXECBUTTON:
{
LONG lRecNum = 0L;
LONG lNumRecs = 0L;
BYTE caNumRecs[10];
HCURSOR hCursor;
GetDlgItemText(hdlg, IDC_EDIT1, (LPSTR) caString,
sizeof(caString));
if (lstrlen((LPSTR) caString))
{
// change cursor to hourglass
hCursor = SetCursor(LoadCursor(NULL, IDC_WAIT));
// call query
lRecNum = sx_Query(caString);
// in case we're on a record that doesn't meet the condition
sx_GoTop();
if (lRecNum)
{
lNumRecs = sx_QueryRecCount();
_fmemset(caNumRecs, 0, (size_t) sizeof(caNumRecs));
ConvertLtoA(lNumRecs, caNumRecs, 7);
lstrcpy((LPSTR) caExpression, (LPSTR) caNumRecs);
lstrcat((LPSTR) caExpression, " records found");
}
else
lstrcpy((LPSTR) caExpression, "0 records found");
SendMessage(hStatBar, SBM_DISPLAYTEXT, (WPARAM) 0,
(LPARAM) ((LPCSTR) caExpression));
// restore cursor before exit
SetCursor(hCursor);
return(FALSE);
}
}
return(TRUE);
sx_DBFilter, sx_FilterDlg, sx_QueryRecCount, sx_QuerySetExact, sx_QueryTest, sx_SetDeleted, sx_SetFilter, xBase Expression Engine, xBase Functions Supported