Extending collation support in SQL Server and Jet, Part 4 (What about Jet?)

by Michael S. Kaplan, published on 2005/10/21 03:31 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/10/21/480898.aspx

Prior posts in this series:

Extending collation support in SQL Server and Jet, Part 0 (HISTORY)
Extending collation support in SQL Server and Jet, Part 1 (the broad strokes)
Extending collation support in SQL Server and Jet, Part 2 (generating sort keys)
Extending collation support in SQL Server and Jet, Part 2.1 (is this on?)
Extending collation support in SQL Server and Jet, Part 3 (THAT CLASS)

Okay, we have covered a lot of ground here, without ever actually talking about Microsoft Jet (even though it is in the title).

Jet has been around for a long time. I believe it stands for Joint Engine Technology, and in this story there are two pills you can take the red one and the blue one.

Swallow the blue pill and you will find you have moved off to handle Active Directory, etc.. Any day now one of my personal development heroes Brett Shirley is going to be blogging about it. :-)

If you swallow the red pill, then you go down the rabbit hole into the world of Access and dozens of other technologies that have used it over the years (including Windows!) and now according to Erik Rucker was an inspiration for the new Access Data Engine in Access 12.

I am not running Access 12 yet to test with, so none of this is about Access 12; I'll talk about the new version when (a) I am allowed to and (b) I have actually tried it. For now, we will concentrate on the Jet 4.0 that is in Access 2000/2002/2003.

You may recall that Jet 4.0 has those collation tables from Windows 2000 post beta 1, pre beta 2 that I mentioned in Part 0. So the support of custom collations is important if you wanted to support some of the new languages from Windows 2000, XP/Server 2003, XP SP2, or Vista, especially since there is no built-in support for binary collations like there is in SQL Server.

So we start by knowing that we have VBA to work with, as a coding language. Building those sort keys is straightforward, but requires some thought. We start with the Declare statement and a wrapper function to hide the weirdnesses of VB/VBA 6.x's non-Unicodality:

Private Declare Function LCMapStringW Lib "kernel32" ( _
    ByVal Locale As Long, ByVal dwMapFlags As Long, _
    ByVal lpSrcStr As Long, ByVal cchSrc As Long, _
    ByVal lpDestStr As Long, ByVal cchDest As Long) As Long

 

Private Const SORT_WORDSORT = &H0&
Private Const SORT_STRINGSORT = &H1000&
Private Const LCMAP_SORTKEY = &H400
Private Const LCMAP_LOWERCASE = &H100&          ' lower case letters
Private Const LCMAP_UPPERCASE = &H200&          ' upper case letters
Private Const NORM_IGNORECASE = &H1&
Private Const NORM_IGNORENONSPACE = &H2&
Private Const NORM_IGNORESYMBOLS = &H4&
Private Const NORM_IGNOREKANATYPE = &H10000
Private Const NORM_IGNOREWIDTH = &H20000

Public Enum SortKeyFlags
    STRINGSORT = SORT_STRINGSORT
    IGNORECASE = NORM_IGNORECASE
    IGNORENONSPACE = NORM_IGNORENONSPACE
    IGNORESYMBOLS = NORM_IGNORESYMBOLS
    IGNOREKANATYPE = NORM_IGNOREKANATYPE
    IGNOREWIDTH = NORM_IGNOREWIDTH
End Enum

Public Function GetSortKey(ByVal lcid As Long, ByVal lpSrcStr As String, ByVal flags As SortKeyFlags) As Variant
    Dim rgbyt() As Byte
    Dim stBuff As String
    Dim cbReturn As String
    Dim cbBuff As Long
   
    stBuff = String$(255, vbNullChar)
    cbBuff = LenB(stBuff)
    cbReturn = LCMapStringW(Locale, _
    LCMAP_SORTKEY, StrPtr(stIn), _
    LenB(stIn), StrPtr(stBuff), cbBuff)
   
    If cbReturn > 0 Then
        rgbyt = LeftB(stBuff, cbReturn)
    Else
        ' not a supported language, so at least return the
        ' same string that was sent in so some sort can happen
        rgbyt = stIn
    End If
   
    GetSortKey = rgbyt
End Function

To create a binary field to store that sort key value is a bit of extra work, and I will cover that in a post tomorrow.

Tonight (Friday night) I am heading to a ship party, so we'll see how much work it will take to put myself back together after that, Saturday morning....

This post brought to you by "ཛྷ" (U+0f5c, a.k.a. TIBETAN LETTER DZHA)

One of the many letters that are unsortable in Jet 4.0 but using this new method will find themselves sortable!

# James on 21 Oct 2005 6:12 AM:

# Michael S. Kaplan on 21 Oct 2005 6:26 AM:

go to newer or older post, or back to index or month or day