Customizing the OLE DB provider

When installing the OLE DB provider, the Windows Registry must modified. Typically, this is done using the self-registration capability built into the OLE DB provider. For example, you would use the Windows regsvr32 tool to do this. A standard set of registry entries are created by the provider.

In a typical connection string, one of the components is the Provider attribute. To indicate that the SQL Anywhere OLE DB provider is to be used, you specify the name of the provider. Here is a Visual Basic example: 

connectString = "Provider=SAOLEDB;DSN=SQL Anywhere 11 Demo"

With ADO and/or OLE DB, there are many other ways to reference the provider by name. Here is a C++ example in which you specify not only the provider name but also the version to use. 

hr = db.Open(_T("SAOLEDB.11"), &dbinit);

The provider name is looked up in the registry. If you were to examine the registry on your computer system, you would find an entry in HKEY_CLASSES_ROOT for SAOLEDB. 

[HKEY_CLASSES_ROOT\SAOLEDB]
@="SQL Anywhere OLE DB Provider"

It has two subkeys that contain a class identifier (ClsId) and current version (CurVer) for the provider. Here is an example.

[HKEY_CLASSES_ROOT\SAOLEDB\Clsid]
@="{41dfe9f3-db91-11d2-8c43-006008d26a6f}"

[HKEY_CLASSES_ROOT\SAOLEDB\CurVer]
@="SAOLEDB.11"

There are several more similar entries. They are used to identify a specific instance of an OLE DB provider. If you look up the ClsId in the registry under HKEY_CLASSES_ROOT\CLSID and examine the subkeys, you see that one of the entries identifies the location of the provider DLL. 

[HKEY_CLASSES_ROOT\CLSID\
{41dfe9f3-db91-11d2-8c43-006008d26a6f}\
InprocServer32]

@="c:\\sa11\\bin64\\dboledb11.dll"
"ThreadingModel"="Both"

The problem here is that the structure is very monolithic. If you were to uninstall the SQL Anywhere software from your system, the OLE DB provider registry entries would be removed from your registry and then the provider DLL would be removed from your hard drive. Any applications that depend on the provider would no longer work.

Similarly, if applications from different vendors all use the same OLE DB provider, then each installation of the same provider would overwrite the common registry settings. The version of the provider that you intended your application to work with would be supplanted by another newer (or older!) version of the provider.

Clearly, the instability that could arise from this situation is undesirable. To address this problem, the SQL Anywhere OLE DB provider can be customized.

In the following exercise, you generate a unique set of GUIDs, choose a unique provider name and unique DLL names. These three things will help you create a unique OLE DB provider which you can deploy with your application.

Here are the steps involved in creating a custom version of the OLE DB provider.

To customize the OLE DB provider

  1. Make a copy of the sample registration file shown below. It is listed after these steps because it is quite lengthy. The file name should have a .reg suffix. The names of the registry values are case sensitive.

  2. Create 4 sequential UUIDs (GUIDs).

    Use the uuidgen tool from Microsoft's Visual Studio.

    uuidgen -n4 -s -x >oledbguids.txt

  3. The 4 UUIDs or GUIDs are assigned in sequence as follows.

    1. The Provider class ID (GUID1 below).
    2. The Enum class ID (GUID2 below).
    3. The ErrorLookup class ID (GUID3 below).
    4. The Provider Assist class ID (GUID4 below). This last GUID is not used in Windows Mobile deployments.

    It is important that they be sequential (that is what -x in the uuidgen command line does for you). Each GUID should look something like the following.

    Name GUID
    GUID1 41dfe9f3-db92-11d2-8c43-006008d26a6f
    GUID2 41dfe9f4-db92-11d2-8c43-006008d26a6f
    GUID3 41dfe9f5-db92-11d2-8c43-006008d26a6f
    GUID4 41dfe9f6-db92-11d2-8c43-006008d26a6f

    Note that it is the first part of the GUID (for example, 41dfe9f3) that is incrementing.

  4. Use the search/replace capability of an editor to change all the GUID1, GUID2, GUID3, and GUID4 in the text to the corresponding GUID (for example, GUID1 would be replaced by 41dfe9f3-db92-11d2-8c43-006008d26a6f if that was the GUID generated for you by uuidgen).

  5. Decide on your Provider name. This is the name that you will use in your application in connection strings, and so on (for example, Provider=SQLAny). Do not use any of the following names. These names are used by SQL Anywhere.

    Version 10 or later Version 9 or earlier
    SAOLEDB ASAProv
    SAErrorLookup ASAErrorLookup
    SAEnum ASAEnum
    SAOLEDBA ASAProvA

  6. Use the search/replace capability of an editor to change all the occurrences of the string SQLAny to the provider name that you have chosen. This includes all those places where SQLAny may be a substring of a longer string (for example, SQLAnyEnum).

    Suppose you chose Acme for your provider name. The names that will appear in the HKEY_CLASSES_ROOT registry hive are shown in the following table along with the SQL Anywhere names (for comparison).

    SQL Anywhere provider Your custom provider
    SAOLEDB Acme
    SAErrorLookup AcmeErrorLookup
    SAEnum AcmeEnum
    SAOLEDBA AcmeA

  7. Make copies of the SQL Anywhere provider DLLs (dboledb11.dll and dboledba11.dll) under different names. Note that there is no dboledba11.dll for Windows Mobile. 

    copy dboledb11.dll myoledb11.dll
copy dboledba11.dll myoledba11.dll

    A special registry key will be created by the script that is based on the DLL name that you choose. It is important that the name be different from the standard DLL names (such as dboledb11.dll or dboledba11.dll). If you name the provider DLL myoledb11 then the provider will look up a registry entry in HKEY_CLASSES_ROOT with that same name. The same is true of the provider schema assist DLL. If you name the DLL myoledba11 then the provider will look up a registry entry in HKEY_CLASSES_ROOT with that same name. It is important that the name you choose is unique and is unlikely to be chosen by anyone else. Here are some examples.

    DLL name(s) chosen Corresponding HKEY_CLASSES_ROOT\name
    myoledb11.dll HKEY_CLASSES_ROOT\myoledb11
    myoledba11.dll HKEY_CLASSES_ROOT\myoledba11
    acmeOledb.dll HKEY_CLASSES_ROOT\acmeOledb
    acmeOledba.dll HKEY_CLASSES_ROOT\acmeOledba
    SAcustom.dll HKEY_CLASSES_ROOT\SAcustom
    SAcustomA.dll HKEY_CLASSES_ROOT\SAcustomA

  8. Use the search/replace capability of an editor to change all the occurrences of myoledb11 and myoledba11 in the registry script to the two DLL names you have chosen.

  9. Use the search/replace capability of an editor to change all the occurrences of d:\\mypath\\bin32\\ in the registry script to the installed location for the DLLs. Be sure to use a pair of slashes to represent a single slash. This step will have to be customized at the time of your application install.

  10. Save the registry script to disk and run it.

  11. Give your new provider a try. Do not forget to change your ADO / OLE DB application to use the new provider name.

Here is the listing of the registry script that is to be modified.

REGEDIT4
; Special registry entries for a private OLE DB provider.

[HKEY_CLASSES_ROOT\myoledb11]
@="Custom SQL Anywhere OLE DB Provider 11.0"

[HKEY_CLASSES_ROOT\myoledb11\Clsid]
@="{GUID1}"

; Data1 of the following GUID must be 3 greater than the
; previous, for example, 41dfe9f3 + 3 => 41dfe9ee.

[HKEY_CLASSES_ROOT\myoledba11]
@="Custom SQL Anywhere OLE DB Provider 11.0"

[HKEY_CLASSES_ROOT\myoledba11\Clsid]
@="{GUID4}" 

; Current version (or version independent prog ID)
; entries (what you get when you have "SQLAny"
; instead of "SQLAny.11")

[HKEY_CLASSES_ROOT\SQLAny]
@="SQL Anywhere OLE DB Provider"

[HKEY_CLASSES_ROOT\SQLAny\Clsid]
@="{GUID1}"

[HKEY_CLASSES_ROOT\SQLAny\CurVer]
@="SQLAny.11"

[HKEY_CLASSES_ROOT\SQLAnyEnum]
@="SQL Anywhere OLE DB Provider Enumerator"

[HKEY_CLASSES_ROOT\SQLAnyEnum\Clsid]
@="{GUID2}"

[HKEY_CLASSES_ROOT\SQLAnyEnum\CurVer]
@="SQLAnyEnum.11"

[HKEY_CLASSES_ROOT\SQLAnyErrorLookup]
@="SQL Anywhere OLE DB Provider Extended Error Support"

[HKEY_CLASSES_ROOT\SQLAnyErrorLookup\Clsid]
@="{GUID3}"

[HKEY_CLASSES_ROOT\SQLAnyErrorLookup\CurVer]
@="SQLAnyErrorLookup.11"

[HKEY_CLASSES_ROOT\SQLAnyA]
@="SQL Anywhere OLE DB Provider Assist"

[HKEY_CLASSES_ROOT\SQLAnyA\Clsid]
@="{GUID4}"

[HKEY_CLASSES_ROOT\SQLAnyA\CurVer]
@="SQLAnyA.11" 

; Standard entries (Provider=SQLAny.11)

[HKEY_CLASSES_ROOT\SQLAny.11]
@="Sybase SQL Anywhere OLE DB Provider 11.0"

[HKEY_CLASSES_ROOT\SQLAny.11\Clsid]
@="{GUID1}"

[HKEY_CLASSES_ROOT\SQLAnyEnum.11]
@="Sybase SQL Anywhere OLE DB Provider Enumerator 11.0"

[HKEY_CLASSES_ROOT\SQLAnyEnum.11\Clsid]
@="{GUID2}"

[HKEY_CLASSES_ROOT\SQLAnyErrorLookup.11]
@="Sybase SQL Anywhere OLE DB Provider Extended Error Support 11.0"

[HKEY_CLASSES_ROOT\SQLAnyErrorLookup.11\Clsid]
@="{GUID3}"

[HKEY_CLASSES_ROOT\SQLAnyA.11]
@="Sybase SQL Anywhere OLE DB Provider Assist 11.0"

[HKEY_CLASSES_ROOT\SQLAnyA.11\Clsid]
@="{GUID4}" 

; SQLAny (Provider=SQLAny.11)

[HKEY_CLASSES_ROOT\CLSID\{GUID1}]
@="SQLAny.11"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\ExtendedErrors]
@="Extended Error Service"

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\ExtendedErrors\{GUID3}]
@="Sybase SQL Anywhere OLE DB Provider Error Lookup"

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\InprocServer32]
@="d:\\mypath\\bin32\\myoledb11.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\OLE DB Provider]
@="Sybase SQL Anywhere OLE DB Provider 11.0"

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\ProgID]
@="SQLAny.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID1}\VersionIndependentProgID]
@="SQLAny" 

; SQLAnyErrorLookup

[HKEY_CLASSES_ROOT\CLSID\{GUID3}]
@="Sybase SQL Anywhere OLE DB Provider Error Lookup 11.0"
@="SQLAnyErrorLookup.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID3}\InprocServer32]
@="d:\\mypath\\bin32\\myoledb11.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{GUID3}\ProgID]
@="SQLAnyErrorLookup.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID3}\VersionIndependentProgID]
@="SQLAnyErrorLookup" 

; SQLAnyEnum

[HKEY_CLASSES_ROOT\CLSID\{GUID2}]
@="SQLAnyEnum.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID2}\InprocServer32]
@="d:\\mypath\\bin32\\myoledb11.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{GUID2}\OLE DB Enumerator]
@="Sybase SQL Anywhere OLE DB Provider Enumerator"

[HKEY_CLASSES_ROOT\CLSID\{GUID2}\ProgId]
@="SQLAnyEnum.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID2}\VersionIndependentProgID]
@="SQLAnyEnum" 

; SQLAnyA

[HKEY_CLASSES_ROOT\CLSID\{GUID4}]
@="SQLAnyA.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID4}\InprocServer32]
@="d:\\mypath\\bin32\\myoledba11.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{GUID4}\ProgID]
@="SQLAnyA.11"

[HKEY_CLASSES_ROOT\CLSID\{GUID4}\VersionIndependentProgID]
@="SQLAnyA"
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0