IRowsetIndex::SetRange

  • Article

Restricts the set of row entries visible through calls to IRowset::GetNextRows and IRowsetIndex::Seek.

Syntax

HRESULT SetRange (
   HACCESSOR   hAccessor,
   DBORDINAL   cStartKeyColumns,
   void       *pStartData,
   DBORDINAL   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:

    • For each key column this accessor binds, it must also bind all more significant key columns. For example, the accessor can bind column A, columns A and B, or columns A, B, and C.

    • Key columns must be bound in order from most significant key column to least significant key column. For example, if the accessor binds columns A and B, the first binding must bind column A and the second binding must bind column B.

    • If the accessor binds any non-key columns, key columns must be bound first. For example, if the accessor binds columns A, B, and the bookmark column, the first binding must bind column A, the second binding must bind column B, and the third binding must bind the bookmark 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), IRowsetIndex::SetRange does not set a range.

  • cStartKeyColumns
    [in]

    The number of bindings in hAccessor for which *pStartData contains valid data. IRowsetIndex::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 that cStartKeyValues is 2. IRowsetIndex::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. IRowsetIndex::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 that cEndKeyValues is 2. IRowsetIndex::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 meanings described in the following table.

    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 0xff.

    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.

    dwRangeOptions included DBRANGE_PREFIX or DBRANGE_MATCH, and pEndData was not a NULL pointer.

    dwRangeOptions was DBRANGE_MATCH_N, and the provider did not support that option. For more information about DBRANGE_MATCH_N, see "Equality Matching."

    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. Some providers may return DB_E_BADACCESSORHANDLE instead of this error code when command accessors are passed to the rowset.

  • 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.

  • DB_E_NOINDEX
    The rowset uses integrated indexes, and there is no current index.

Comments

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

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. Therefore, 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.

As long as the *pStartData and *pEndData values specified are valid for the column, IRowsetIndex::SetRange should succeed, even if the start and end values are outside of the range of values contained in the index. If the index does not contain any rows within the range, calling IRowset::GetNextRows after the call to IRowsetIndex::SetRange should return DB_S_ENDOFROWSET.

A range on the entire index is defined by calling IRowsetIndex::SetRange (hAcc, 0, NULL, 0, NULL, 0). When a range is set, IRowsetIndex::Seek can position only to rows in the current range.

For information about how IRowsetIndex::SetRange transfers data from *pDataStart and *pDataEnd, see Setting Data.

Inclusive and Exclusive Ranges with Ascending Indexes

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

IRowsetIndex::SetRange call

X > 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_EXCLUSIVESTART);

X = 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_INCLUSIVESTART);

X < 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_EXCLUSIVEEND);

X = 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_INCLUSIVEEND);

B. closed ranges (single column key)

Desired range

IRowsetIndex::SetRange call

X = 5 and X = 10

 
SetRange(hAcc, 1, {5}, 1, {10}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

X = 5 and X < 10

 
SetRange(hAcc, 1, {5}, 1, {10}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

X > 5 and X = 10

 
SetRange(hAcc, 1, {5}, 1, {10}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

X > 5 and X < 10

 
SetRange(hAcc, 1, {5}, 1, {10}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

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

IRowsetIndex::SetRange call

A = 1 and B = 5 and B = 10

 
SetRange(hAcc, 2, {1, 5}, 2, {1, 10}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A = 1 and B > 5

 
SetRange(hAcc, 2, {1, 5}, 1, {1}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A = 1 and B < 5

 
SetRange(hAcc, 1, {1}, 2, {1, 5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

Examples with partial keys

E. open ranges (multicolumn key [A, B])

Desired range

IRowsetIndex::SetRange call

A > 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_EXCLUSIVESTART);

A = 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_INCLUSIVESTART);

A = 5

 
SetRange(hAcc, 1, {5}, 1,{5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A < 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_EXCLUSIVEEND);

A = 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_INCLUSIVEEND);

F. closed ranges (multicolumn key [A,B])

Desired range

IRowsetIndex::SetRange call

A > 5 and A = 10

 
SetRange(hAcc, 1, {5}, 1, {10}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

Prefix Matching

IRowsetIndex::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 for an ascending index, as follows:

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

IRowsetIndex::SetRange call

A = 1 and B like "abc*"

 
SetRange(hAcc, 2, {1, "abc"}, 0, NULL, 
 DBRANGE_PREFIX);

This call is the same whether or not DBRANGE_PREFIX is used with an ascending or descending index.

Equality Matching

To facilitate the formulation of ranges involving equality, the IRowsetIndex::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

IRowsetIndex::SetRange call

A = 5 and B= 6

 
SetRange(hAcc, 2, {5, 6}, 2, {5, 6}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A = 5 and B= 6

 
SetRange(hAcc, 2, {5, 6}, 0, NULL, 
 DBRANGE_MATCH);

These calls are the same whether or not 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 nonzero. If so, 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.

Ranges That Include NULLs

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 be used only with partial keys. It is equivalent to one of the following:

  • If NULL values sort at the start of the index, it is equivalent to specifying an additional column in *pStartData, setting the value of the status of that column to DBSTATUS_S_ISNULL, and setting the DBRANGE_EXCLUSIVESTART value in dwRangeOptions. Therefore, the range will start after the last NULL value in the column.

  • If NULL values sort at the end of the index, it is equivalent to specifying an additional column in *pEndData, setting the value of the status of that column to DBSTATUS_S_ISNULL, and setting the DBRANGE_EXCLUSIVEEND value in dwRangeOptions. Therefore, the range will end before the first NULL value in the column.

Therefore, 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 IRowsetIndex::GetIndexInfo method or the INDEXES schema rowset of IDBSchemaRowset.

Examples

Desired range

IRowsetIndex::SetRange call

X is NULL

 
SetRange(hAcc, 1, {NULL}, 1, {NULL}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

X is not NULL

 
SetRange(hAcc, 0, NULL, 0, NULL, 
 DBRANGE_EXCLUDENULLS);

A = 4 and B is not NULL

 
SetRange(hAcc, 1,{4}, 1, {4}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND | 
 DBRANGE_EXCLUDENULLS);

A = 4 and B is not NULL

 
SetRange(hAcc, 1, {4}, 0, NULL, 
 DBRANGE_MATCH | DBRANGE_EXCLUDENULLS );

Duplicate Keys

An IRowsetIndex::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, IRowsetIndex::SetRange sets the position at the first entry less than the *pStartData value.

End of Range

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.

Inclusive and Exclusive Ranges with Descending Indexes

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. Following are some examples.

Examples with full keys

A. open ranges (single column key)

Desired range

IRowsetIndex::SetRange call

X > 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_EXCLUSIVEEND);

X = 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_INCLUSIVEEND);

X < 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_EXCLUSIVESTART);

X = 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_INCLUSIVESTART);

B. closed ranges (single column key)

Desired range

IRowsetIndex::SetRange call

X = 5 and X = 10

 
SetRange(hAcc, 1, {10}, 1, {5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

X = 5 and X < 10

 
SetRange(hAcc, 1, {10}, 1, {5}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

X > 5 and X = 10

 
SetRange(hAcc, 1, {10}, 1, {5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

X > 5 and X < 10

 
SetRange(hAcc, 1, {10}, 1, {5}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

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

IRowsetIndex::SetRange call

A = 1 and B = 5 and B = 10

 
SetRange(hAcc, 2, {1, 10}, 2, {1, 5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A = 1 and B > 5

 
SetRange(hAcc, 1, {1}, 2, {1, 5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

A = 1 and B < 5

 
SetRange(hAcc, 2, {1, 5}, 1, {1}, 
 DBRANGE_EXCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

Examples with partial keys

E. open ranges (multicolumn key [A, B])

Desired range

IRowsetIndex::SetRange call

A > 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_EXCLUSIVEEND);

A = 5

 
SetRange(hAcc, 0, NULL, 1, {5}, 
 DBRANGE_INCLUSIVEEND);

A = 5

 
SetRange(hAcc, 1, {5}, 1, {5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_INCLUSIVEEND);

A < 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_EXCLUSIVESTART);

A = 5

 
SetRange(hAcc, 1, {5}, 0, NULL, 
 DBRANGE_INCLUSIVESTART);

F. closed ranges (multicolumn key [A,B])

Desired range

IRowsetIndex::SetRange call

A > 5 and A = 10

 
SetRange(hAcc, 1, {10}, 1, {5}, 
 DBRANGE_INCLUSIVESTART | 
 DBRANGE_EXCLUSIVEEND);

See Also

Reference

IRowset::GetNextRows

IRowsetIndex::Seek