Testing for NLS sorting/collation behavior changes when calling JetAttachDatabase

  • Article

The JetAttachDatabase function’s JET_bitDbDeleteCorruptIndexes and JET_bitDbDeleteUnicodeIndexes options help assist applications in upgrading indexes due to underlying OS collation/sorting behavior changes.

When the operating system’s Unicode sorting version changes, that changes the behavior of collation of the system.  In those cases, database indexes need to be reindexed in order to ensure that they are accurate.  Fortunately this is pretty rare, but, unfortunately, that makes it a bit hard to test.

Callers of JetAttachDatabase can use the JET_bitDbDeleteCorruptIndexes or JET_bitDbDeleteUnicodeIndexes options to choose how to check for indexes that need to be updated.  If the NLS sorting version has changed, the JetAttachDatabase call can return JET_wrnCorruptIndexDeleted, JET_errPrimaryIndexCorrupted or JET_errSecondaryIndexCorrupted return codes to notify the caller that the database needs to be reindexed.

For more complete information, make sure to read the complete JetAttachDatabase documentation, I've glossed over the behavior to get to my point, which is:

How Do I Test It?

So when does it change, and how do you test it?  Usually the behavior only changes when upgrading operating systems, though it is possible that an unexpected problem requires updating the NLS sorting version, thus requiring databases to be reindexed.  It’s pretty hard to test a code path that your application only uses after OS upgrade!

I have a workaround.  It might not be the “best” solution, however it’s way easier that reinstalling a bunch of old OS’s and upgrading, or, worse, upgrading to an OS that doesn’t exist yet!

Use at your own risk

Unfortunately my technique isn’t terribly supported and could have bizzare ramifications, however on Windows 8 it is possible to switch the default sorting (collation) behavior to an older version, such as Windows 7.  This requires mucking with a registry key, so all of the typically warnings apply when tampering with the registry.  It’s also possible that some applications get confused with the change in sort behavior, so please only do this on a test box.  Additionally, downgrading your sorting is presumably giving you bad behavior that we fixed.  (At least I’d like to think that Windows 8 sorting is better than Windows 7!)

In the registry, there’s a key HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlNlsSortingVersions that has information about sorting.  Mucking with this stuff isn’t supported, and I might totally change how it works in future versions, however for Windows 8, we can leverage the “default” key to trick Jet into seeing a different sort version so that we can test our application’s nls-version-was-upgraded reindexing code path.

The “default” version that windows looks at is pointed to by the “(Default)” reg value.  Changing that from Windows 8’s 0006020D to 00060101 (the low byte doesn’t matter) will get Windows 7 sorting behavior.  It will also cause JET to give us that “JET_errPrimaryIndexCorrupted” or “JET_errSecondaryIndexCorrupted” return code.  And then we can make sure our application appropriately re-indexes its database.

Like I said, this is totally a hack, and probably does nasty things to other applications that depend on CompareStringEx or other NLS collation behavior, but it might be useful to test that hard-to-exercise  code path for database reindexing.

Hope you find it useful,

-Shawn

References: