Index range exact

Sep 10, 2010 at 9:14 AM

Is there are way of setting an index range so that I find all records with an exact value in a non-unique index? Say I have these values:

Blue Day
Blue Day
Blue Sky

I just want to get the two "Blue" records. I know I can set a range that gets everything starting with "Blue", but then I have to check for the end condition myself. I was hoping there's a way of just getting exact values. The unit test code doesn't seem to contain anything that does what I want, but I thought I'd ask anyway.

Learning how to use MakeKey and SetIndexRange and what all of the grBits mean is vitally important for someone who wants to use Managed Esent at full power. I'm a bit confused by the large number of options and I'm reading the unit test code and running experiments to see how the combinations work, but it's slow work. It would be fabulous to have a short reference list of the combinations that perform commonly needed tasks.

Cheeers, Greg

Sep 10, 2010 at 9:27 AM

You can set the index scan to be EQ, and that should work.

However, note that if you have long keys, they might be normalized.

I run into this when I had a key > 120 characters long, when the difference was in the last few characters. Esent normalized the keys to be the same.  

Sep 13, 2010 at 1:19 PM

I do a MakeKey for "Blue" and then Seek EQ and loop MoveNext through the records, I get the "Blue" records of course, but it runs off the end of "Blue" and the loop reads to the end of the index. From reading the C API documentation it seems that is the expected behaviour, as my Seek does not create any kind of index range to limit what I will read. I think was expected it to be too smart, and it can't read my mind and know when to stop.

I just noticed that Seek has a JET_bitSetIndexRange flag which may give the effect I want, automatically creating an exact match range. I'll report any interesting news I might find on this issue later.

I should not run into the normalized key problem you describe, as I always try to put keys and indexes on fields of small or moderate size. But I'll keep the warning in the back of my mind. I did see an article on normalised keys, but I dont' think I've read it yet.

Cheers, Greg

Ps. I've created a set of T4 templates that generate a complete set of CRUD and entity classes for all tables in a database. I'll give you a link to it in a couple of weeks when I feel it's polished to an acceptable standard.

Sep 13, 2010 at 2:12 PM

As far as I understand your problem you can use the following sequence:

Api.MakeKey(session, table, VALUE, MakeKeyGrbit.NewKey)
if(Api.TrySeek(session, table, SeekGrbit.SeekEQ))
   Api.MakeKey(session, table, VALUE, MakeKeyGrbit.NewKey);
   if (Api.TrySetIndexRange(session, table, SetIndexRangeGrbit.RangeUpperLimit))
    // iterate through the records

Sep 13, 2010 at 11:59 PM

Yes, this code works. I have also verified that the Seek flag JET_bitSetIndexRange is a convenient way of combining the Seek with the set index range.

I was just running some experiments with the code similar you what you've show. I tried it with single column indexes and multi-columnn indexes. I'm getting inconsistent results on the multi-column indexes and I think it's due to some of the columns being nullable. My test code below tries to seek and list all records with (2,"green") in the index pair. It dies on the last line with errNoCurrentRecord, even though I know that there are 2 matching records.

 I need to run more experiments with single and multi-column indexes, some with nullable and non-nullable columns to find a pattern in why I sometimes get crashes or zero records returned -- Greg

Api.JetSetCurrentIndex(SessId, table, ContactIndex.IxPair.ToString());
Api.MakeKey(SessId, table, (short)2MakeKeyGrbit.NewKey);
Api.MakeKey(SessId, table, "green"Encoding.ASCII, MakeKeyGrbit.None);
if (Api.TrySeek(wrap.SessId, table, SeekGrbit.SeekEQ))
  Api.MakeKey(SessId, table, (short)2MakeKeyGrbit.NewKey);
  Api.MakeKey(SessId, table, "green"Encoding.ASCII, MakeKeyGrbit.None);
  Api.JetSetIndexRange(wrap.SessId, table, SetIndexRangeGrbit.RangeUpperLimit);
Sep 14, 2010 at 8:44 AM

I think that you need to use MakeKeyGrbit.PartialColumnEndLimit to make the second key. See the "How Do I Create an Index Range?" entry in the documentation.

Sep 21, 2010 at 8:28 AM

Yes, wrong grBits. I found the correct combination that will allow me to seek for exact matches on a two-column key, a nullable short and a nullable string. I'm not interested in partial matches in my case, I want to get the exact matches.  I also want the code to work if either or both of the keys are null. This sample pasted out of my code works:

JET_COLUMNID colidId = Api.GetTableColumnid(wrap.SessId, table, "Id");
JET_COLUMNID colidName = Api.GetTableColumnid(wrap.SessId, table, "Name");
JET_COLUMNID colidPopularity = Api.GetTableColumnid(wrap.SessId, table, "Popularity");
JET_COLUMNID colidColour = Api.GetTableColumnid(wrap.SessId, table, "Colour");
Api.JetSetCurrentIndex(wrap.SessId, table, "IxPair");
Api.MakeKey(wrap.SessId, table, seekpop.Value, MakeKeyGrbit.NewKey);    // SEE NOTE BELOW
Api.MakeKey(wrap.SessId, table, seekcolour, Encoding.ASCII, MakeKeyGrbit.None);
if (Api.TrySeek(wrap.SessId, table, SeekGrbit.SeekEQ))
    Api.MakeKey(wrap.SessId, table, seekpop.Value, MakeKeyGrbit.NewKey);
    Api.MakeKey(wrap.SessId, table, seekcolour, Encoding.ASCII, MakeKeyGrbit.None);    // SEE NOTE BELOW
    Api.JetSetIndexRange(wrap.SessId, table, SetIndexRangeGrbit.RangeInclusive | SetIndexRangeGrbit.RangeUpperLimit);
        int id = Api.RetrieveColumnAsInt32(wrap.SessId, table, colidId).Value;
        string name = Api.RetrieveColumnAsString(wrap.SessId, table, colidName, Encoding.Unicode);
        short? pop = Api.RetrieveColumnAsInt16(wrap.SessId, table, colidPopularity);
        string colour = Api.RetrieveColumnAsString(wrap.SessId, table, colidColour, Encoding.ASCII);
        Info("{0,4} | {1,-20} | {2,4} | {3} |", id, name, pop, colour);
    while (Api.TryMoveNext(wrap.SessId, table));

There is one catch I haven't shown in the above code. If the short? is null, then I have to use this to set the key for the column because there is no override for a short? seek argument:

Api.JetMakeKey(wrap.SessId, table, null0MakeKeyGrbit.NewKey);


Sep 21, 2010 at 11:33 AM

I just remembered that the code can be simplified by adding SeekGrbit.SetIndexRange in the TrySeek, as this automatically sets the exact index range and makes the following MakeKey calls and JetSetindexRange call superfluous. I discovered this last week, and I completely forgot when posting my previous sample. This helps simplify the code considerably -- Greg