Passing second column of table as index

We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.

We are trying with IRowsetIndex:: Seek method to access the data from the database.

We are facing problem when we try to access data from the table having composite index other than the first column.

For example:
The table ITEM contains the following columns:
1. ItemCode
2. PcNo
3. SubPcNo
4. BrandNo
5. DescText

6. ProductionKind

7. ProductionState

8. ClearingState

9. ST

10. TS

I’ve two indexes for this table where the two indexes are

1. ItemKey1 on ITEM (ItemCode).

2. ItemKey2 on ITEM (PcNo, SubPcNo, BrandNo).

If I mention ItemKey1 as the index then the Seek method works perfectly.

But if I mention ItemKey2 as the index, then I’m getting the error as “DB_E_BADBINDINFO”.

When we visted the MSDN for this problem we noted that:

1. When you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.

2. When passing key values to an index rowset, the consumer performs these actions only for the number of key columns specified in the cKeyValues argument in IRowsetIndex:: Seek.

So we had the composite index such that columns of a composite index are physically aligned next to each other as in the table.

When the first column of the index is the first column of the table as in index ItemKey1 we found that the seek method works perfectly.

If we pass the index ItemKey2, we are again getting the error “DB_E_BADBINDINFO”.

What else have we missed out while passing the keys in IRowsetIndex:: Seek?

Thanks in advance.

Regards,

Sasi.

I saw this error when tried to use the table accessor for seeking. When you use IRowsetIndex you must create a separate accessor for the index columns and use that accessor for the Seek method, not the table's own accessor.

|||

Thanks for your reply Joao Paulo Figueira.

I was not able to understand the solution you provided, could you kinldy explain in detail.

Thanks & Regards,

Sasi.

|||

You must create a second memory buffer where to store the values to seek. This is built the same way as the memory buffer for accessing table data, but it must have the index columns and in the same order. You should use code similar to the one for binding the table columns in order to create the index accessor.

The only situation when you can share the same accessor between the table and the index is when the first N columns of the index are the same as the first N columns of the table. In all other situations you must create a different accessor for entering the data to seek.

|||

Thanks a lot Sir.

Its working now.

Thanks & Regards,

Sasi.