This project has moved and is read-only. For the latest updates, please go here.

How do I seek records matching criteria from multiple non-unique columns?

Aug 31, 2013 at 1:54 PM
Hi!

Basically, I want to know how to do the following SQL query:
select UserId from tUser where Name = 'Alice' and Age = 20;
from a table (UserId, Name, Age) that contains the following records:
1, Alice, 20
2, Bob, 25
3, Charlie, 20
4, Alice, 27
I would expect the result to be only the record with UserId 1.

I have looked at the StockSample and the IntersectIndexes-method and tried the following code:
[Test]
public void Retrieve_a_single_record_from_multiple_column_criteria()
{
    const string criterionName = "Alice";
    const int criterionAge = 20;

    var nameAsByteArray = Encoding.Unicode.GetBytes(criterionName);
    var ageAsByteArray = BitConverter.GetBytes(criterionAge);

    JET_TABLEID tableIdForColumnName;
    Api.JetDupCursor(_sessionId, _tableId, out tableIdForColumnName, DupCursorGrbit.None);
    Api.JetSetCurrentIndex(_sessionId, tableIdForColumnName, IndexNameName);

    JET_TABLEID tableIdForColumnAge;
    Api.JetDupCursor(_sessionId, _tableId, out tableIdForColumnAge, DupCursorGrbit.None);
    Api.JetSetCurrentIndex(_sessionId, tableIdForColumnAge, IndexNameAge);

    Api.JetMakeKey(_sessionId, tableIdForColumnName, nameAsByteArray, nameAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnName, SeekGrbit.SeekEQ);
    Api.JetMakeKey(_sessionId, tableIdForColumnAge, ageAsByteArray, ageAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnAge, SeekGrbit.SeekEQ);

    var records = new List<int>();
    var bookmarks = Api.IntersectIndexes(_sessionId, tableIdForColumnName, tableIdForColumnAge);
    foreach (var bookmark in bookmarks)
    {
        Api.JetGotoBookmark(_sessionId, _tableId, bookmark, bookmark.Length);
        PrintOneRecord();

        // This is only for assertion.
        var valuePrimaryKey = Api.RetrieveColumnAsInt32(_sessionId, _tableId, _columnIds[ColumnNamePrimaryKey]);
        records.Add(valuePrimaryKey.Value);
    }

    Api.JetCloseTable(_sessionId, tableIdForColumnName);
    Api.JetCloseTable(_sessionId, tableIdForColumnAge);

    CollectionAssert.IsNotEmpty(records);
    CollectionAssert.IsSubsetOf(new[] { 1 }, records);
    Assert.AreEqual(1, records.Count);
}
(I did not include the code for creating the DB, tables, indexes and records.)

However, this code yields all records in the table, including UserId 2 (Bob) which does not match any of the criteria.

I have tried looking through the source code to find similar examples, but all I can find is OR-queries with either criterion A or criterion B, not AND-queries where both criteria must match.
Sep 5, 2013 at 1:13 AM
Hello,

You need to set the index range for each of the tableid's, in order to set the upper bound. You've only set the lower bound for each of the two indices.

Take a look at 'IntersectIndexesTests.cs' in the test code. It contains a few examples that ought to help out.

-martin
Sep 7, 2013 at 7:40 AM
Oh. I see.

When I replace
    Api.JetMakeKey(_sessionId, tableIdForColumnName, nameAsByteArray, nameAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnName, SeekGrbit.SeekEQ);
    Api.JetMakeKey(_sessionId, tableIdForColumnAge, ageAsByteArray, ageAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnAge, SeekGrbit.SeekEQ);
with
    Api.JetMakeKey(_sessionId, tableIdForColumnName, nameAsByteArray, nameAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnName, SeekGrbit.SeekEQ);
    Api.JetMakeKey(_sessionId, tableIdForColumnName, nameAsByteArray, nameAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSetIndexRange(_sessionId, tableIdForColumnName, SetIndexRangeGrbit.RangeInclusive | SetIndexRangeGrbit.RangeUpperLimit);

    Api.JetMakeKey(_sessionId, tableIdForColumnAge, ageAsByteArray, ageAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSeek(_sessionId, tableIdForColumnAge, SeekGrbit.SeekEQ);
    Api.JetMakeKey(_sessionId, tableIdForColumnAge, ageAsByteArray, ageAsByteArray.Length, MakeKeyGrbit.NewKey);
    Api.JetSetIndexRange(_sessionId, tableIdForColumnAge, SetIndexRangeGrbit.RangeInclusive | SetIndexRangeGrbit.RangeUpperLimit);
everything works exactly as I expected!
It didn't occur to me to set both an upper and a lower range when searching for one specific value, i.e. not a range.
It seems... odd... but I can accept it. (:

Thanks so very much!