Restricts the set of row entries visible through calls to IRowset::GetNextRows and IRowsetIndex::Seek.
HRESULT SetRange (
HACCESSOR hAccessor,
ULONG cStartKeyColumns,
void * pStartData,
ULONG cEndKeyColumns,
void * pEndData,
DBRANGE dwRangeOptions);
Parameters
hAccessor
[in]
The handle of the accessor to use for both *pStartData and *pEndData. This accessor must meet the following criteria, which are illustrated with a key that consists of columns A, B, and C, where A is the most significant column and C is the least significant column:
If the accessor does not meet these criteria, the method returns DB_E_BADBINDINFO or a status of DBSTATUS_E_BADACCESSOR for the offending column.
If hAccessor is the handle of a null accessor (cBindings in IAccessor::CreateAccessor was zero), then SetRange does not set a range.
cStartKeyColumns
[in]
The number of bindings in hAccessor for which *pStartData contains valid data. SetRange retrieves data from the first cStartKeyValues key columns from *pStartData. For example, suppose the accessor binds columns A, B, and C of the key in the previous example and cStartKeyValues is 2. SetRange retrieves data for columns A and B.
pStartData
[in]
A pointer to a buffer containing the starting key values of the range, at offsets that correspond to the bindings in the accessor.
cEndKeyColumns
[in]
The number of bindings in hAccessor for which *pEndData contains valid data. SetRange retrieves data from the first cEndKeyValues key columns from *pEndData. For example, suppose the accessor binds columns A, B, and C of the key in the previous example and cEndKeyValues is 2. SetRange retrieves data for columns A and B.
pEndData
[in]
A pointer to a buffer containing the ending key values of the range, at offsets that correspond to the bindings in the accessor.
dwRangeOptions
[in]
A bitmask describing the options of the range. The values in DBRANGEENUM have the following meanings:
| Value | Description |
| DBRANGE_ INCLUSIVESTART |
The start boundary is inclusive (the default). |
| DBRANGE_ EXCLUSIVESTART |
The start boundary is exclusive. |
| DBRANGE_ INCLUSIVEEND |
The end boundary is inclusive (the default). |
| DBRANGE_ EXCLUSIVEEND |
The end boundary is exclusive. |
| DBRANGE_ EXCLUDENULLS |
Exclude NULLs from the range. |
| DBRANGE_PREFIX | Use *pStartData as a prefix. *pEndData must be a null pointer. Prefix matching can be specified entirely using the inclusive and exclusive flags. However, because prefix matching is an important common case, this flag enables the consumer to specify only the *pStartData values, and enables the provider to interpret this request quickly. |
| DBRANGE_MATCH | Set the range to all keys that match *pStartData. *pStartData must specify a full key. *pEndData must be a null pointer. Used for fast equality match. |
| DBRANGE_MATCH_N_MASK | Equal to 0xFF000000. |
| DBRANGE_MATCH_N_SHIFT | Equal to 24 to indicate the number of bits to shift to get the number N. |
Return Code
S_OK
The method succeeded.
E_FAIL
A provider-specific error occurred.
E_INVALIDARG
dwRangeOptions was invalid.
cStartKeyValues was not zero and pStartData was a null pointer.
cEndKeyValues was not zero and pEndData was a null pointer.
hAccessor was the handle of a null accessor.
DB_E_BADACCESSORHANDLE
hAccessor was invalid.
DB_E_BADACCESSORTYPE
The specified accessor was not a row accessor.
DB_E_ERRORSOCCURRED
An error occurred while transferring data for one or more key columns. To determine the columns for which values were invalid, the consumer checks the status values. For a list of status values that can be returned by this method, see "Status Values Used When Setting Data" in "Status" in Chapter 6.
DB_E_NOINDEX
The rowset uses integrated indexes and there is no current index.
If this method performs deferred accessor validation and that validation takes place before any data is transferred, it can also return any of the following return codes for the reasons listed in the corresponding DBBINDSTATUS values in IAccessor::CreateAccessor:
E_NOINTERFACE
DB_E_BADBINDINFO
DB_E_BADORDINAL
DB_E_BADSTORAGEFLAGS
DB_E_UNSUPPORTEDCONVERSION
Comments
A range defines a view in the index containing a contiguous set of key values. The *pStartData and *pEndData values always specify the starting and ending positions in the range, respectively. Thus, for an ascending index, *pStartData contains the smaller value and *pEndData contains the larger value; for a descending index, *pStartData contains the larger value and *pEndData contains the smaller value.
A range on the entire index is defined by calling SetRange (hAcc, 0, NULL, 0, NULL, 0). When a range is set, Seek can only position to rows in the current range.
For information about how SetRange transfers data from *pDataStart and *pDataEnd, see "Setting Data" in Chapter 6.
The DBRANGE_INCLUSIVE* and DBRANGE_EXCLUSIVE* flags apply only to the last value in the *pStartData and *pEndData buffers. The other values are always inclusive.
Examples with full keys
a) open ranges (single column key)
| Desired range | SetRange call |
| X > 5 | |
| X ≥ 5 | |
| X < 5 | |
| X ≤ 5 | |
b) closed ranges (single column key)
| Desired range | SetRange call |
| X ≥ 5 and X ≤ 10 |
|
| X ≥ 5 and X < 10 |
|
| X > 5 and X ≤ 10 |
|
| X > 5 and X < 10 |
|
c) open ranges (multicolumn key [A, B])
The only way to specify open ranges with multicolumn indexes is by using partial keys. See the examples with partial keys below.
Notice that the range A > 5 and B = 1 cannot be specified because it does not correspond to a contiguous range.
d) closed ranges (multicolumn key [A, B])
| Desired range | SetRange call |
| A = 1 and B ≥ 5 and B ≤ 10 |
|
| A = 1 and B > 5 | |
| A = 1 and B < 5 | |
Examples with partial keys
e) open ranges (multicolumn key [A, B])
| Desired range | SetRange call |
| A > 5 | |
| A ≥ 5 | |
| A = 5 | |
| A < 5 | |
| A ≤ 5 | |
f) closed ranges (multicolumn key [A,B])
| Desired range | SetRange call |
| A > 5 and A ≤ 10 | |
SetRange provides the DBRANGE_PREFIX flag to make it easier for a consumer to formulate ranges involving keys whose value represents a prefix. Consider the text command SELECT * FROM T WHERE T.A LIKE "abc*". This range can be formulated using only the inclusive and exclusive flags as follows for an ascending index:
SetRange (hAcc, 1, {"abc"}, 1, {"abd"}, DBRANGE_INCLUSIVESTART | DBRANGE_EXCLUSIVEEND);
This requires the consumer to know the collation order. The DBRANGE_PREFIX flag is provided to make it easier for the consumer to formulate this very common case as follows:
SetRange(hAcc, 1, {"abc"}, 0, NULL, DBRANGE_PREFIX);
Examples
| Desired range | SetRange call |
| A = 1 and B like "abc*" |
|
This call is the same regardless of whether DBRANGE_PREFIX is used with an ascending or descending index.
To facilitate the formulation of ranges involving equality, the SetRange method offers the DBRANGE_MATCH option flag. Consider the example SELECT * FROM T WHERE T.X = 5. The corresponding range can be formulated in terms of the inclusive and exclusive flags as follows:
SetRange(hAcc, 1, {5}, 1, {5}, DBRANGE_INCLUSIVESTART | DBRANGE_INCLUSIVEEND);
The same range can be formulated using the match flags as follows:
SetRange(hAcc, 1, {5}, 0, NULL, DBRANGE_MATCH);
Example
| Desired range | SetRange call |
| A = 5 and B= 6 | |
| A = 5 and B= 6 | |
These calls are the same regardless of whether DBRANGE_MATCH is used with an ascending or descending index.
DBRANGE_MATCH_N is an extension of DBRANGE_MATCH, which indicates to the provider that the first N key values should be assumed to define comparison on equality. This is achieved by reserving the 8 high-order bits of dwRangeOptions to encode the value N. The defined values are DBRANGE_MATCH_N_SHIFT and DBRANGE_MATCH_N_MASK.
Example
a = 5, b = 6, c = 7, d = 8, e = 9, f >= 10 and f <= 20 becomes:
SetRange (hAcc,
6, {5,6,7,8,9,10},
6, {5,6,7,8,9,20},
(5 << DBRANGE_MATCH_N_SHIFT) | DB_RANGE_INCLUSIVESTART | DB_RANGE_INCLUSIVEEND);
Providers will check whether (dwRangeOptions and DBRANGE_MATCH_N_MASK) is non-zero. If so, then the key values will be interpreted by taking the first N keys encoded in the high-order bits as the keys on which to perform an equality match.
To save consumers from having to determine whether NULL values sort at the start or the end of an index, DBRANGE_EXCLUDENULLS excludes NULL values from the final column specified in *pStartData or *pEndData. This value of dwRangeOptions can only be used with partial keys. It is equivalent to one of the following:
Thus, the consumer does not need to know if NULL values sort at the start or end of the index.
The DBRANGE_EXCLUDENULLS flag is defined to allow consumers to easily obtain contiguous key values excluding NULLs at the start or end of the range. For multicolumn indexes (for example, [X, Y]), this means that key values for the prefix of the key must be an equality match. For example, it makes sense to set a range on X = 5 and DBRANGE_EXCLUDENULLS, but not on X BETWEEN 4 AND 6 and DBRANGE_EXCLUDENULLS because the range X BETWEEN 4 AND 6 and Y IS NOT NULL is not contiguous. Therefore, to use DBRANGE_EXCLUDENULLS a consumer must use an equality comparison or MATCH on the prefix of a multicolumn key.
To determine how an index treats NULLs, a consumer checks the information returned by GetIndexInfo method or the INDEXES schema rowset of IDBSchemaRowset.
Examples
| Desired range | SetRange call |
| X is NULL | |
| X is not NULL | |
| A = 4 and B is not NULL |
|
| A = 4 and B is not NULL |
|
A SetRange request (with the exclusive flag on an ascending index allowing duplicate keys) sets the index position at the first index entry greater than the *pStartData value. If the index is descending, then SetRange sets the position at the first entry less than the *pStartData value.
An index is traversed using IRowset::GetNextRows. This method returns DB_S_ENDOFROWSET when it reaches the end of the currently set range, or when it tries to read before the beginning or after the end of the index. This method also returns DB_S_ENDOFROWSET when the rowset is being populated asynchronously and no additional rows are available at this time. To determine whether additional rows may be available, the consumer should call IDBAsynchStatus::GetStatus or listen for the IDBAsynchNotify::OnStop notification.
As mentioned earlier, with descending indexes the user specifies the values that determine the start of the range using *pStartData and the values that determine the end of the range using *pEndData. The following are some examples.
Examples with full keys
a) open ranges (single column key)
| Desired range | SetRange call |
| X > 5 | |
| X ≥ 5 | |
| X < 5 | |
| X ≤ 5 | |
b) closed ranges (single column key)
| Desired range | SetRange call |
| X ≥ 5 and X ≤ 10 |
|
| X ≥ 5 and X < 10 |
|
| X > 5 and X ≤ 10 |
|
| X > 5 and X < 10 |
|
c) open ranges (multicolumn key [A, B])
The only way to specify open ranges with multicolumn indexes is by using partial keys. See examples with partial keys below.
The range A > 5 and B = 1 cannot be specified because it does not correspond to a contiguous range.
d) closed ranges (multicolumn key [A, B])
| Desired range | SetRange call |
| A = 1 and B ≥ 5 and B ≤ 10 |
|
| A = 1 and B > 5 | |
| A = 1 and B < 5 | |
Examples with partial keys
e) open ranges (multicolumn key [A, B])
| Desired range | SetRange call |
| A > 5 | |
| A ≥ 5 | |
| A = 5 | |
| A < 5 | |
| A ≤ 5 | |
f) closed ranges (multicolumn key [A,B])
| Desired range | SetRange call |
| A > 5 and A ≤ 10 |
|
See Also