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 12 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.12"), &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] 
@="{41dfe9f7-db91-11d2-8c43-006008d26a6f}" 

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

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:\\sa12\\bin64\\dboledb12.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. Here are the steps involved in creating a custom version of the OLE DB provider.

Customize the OLE DB provider

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

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.
Use the Microsoft Visual Studio uuidgen utility to create 4 sequential UUIDs (GUIDs).
uuidgen -n4 -s -x >oledbguids.txt

The 4 UUIDs or GUIDs are assigned in the following sequence:

The Provider class ID (GUID1 below).
The Enum class ID (GUID2 below).
The ErrorLookup class ID (GUID3 below).
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 appear similar to 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.

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).

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

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

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

copy dboledb12.dll myoledb12.dll
copy dboledba12.dll myoledba12.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 dboledb12.dll or dboledba12.dll). If you name the provider DLL myoledb12 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 myoledba12 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
myoledb12.dll	HKEY_CLASSES_ROOT\myoledb12
myoledba12.dll	HKEY_CLASSES_ROOT\myoledba12
acmeOledb.dll	HKEY_CLASSES_ROOT\acmeOledb
acmeOledba.dll	HKEY_CLASSES_ROOT\acmeOledba
SAcustom.dll	HKEY_CLASSES_ROOT\SAcustom
SAcustomA.dll	HKEY_CLASSES_ROOT\SAcustomA

Use the search/replace capability of an editor to change all the occurrences of myoledb12 and myoledba12 in the registry script to the two DLL names you have chosen.
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 must be customized at the time of your application install.
Save the registry script to disk and run it.
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\myoledb12] 
@="Custom SQL Anywhere OLE DB Provider 12.0" 
[HKEY_CLASSES_ROOT\myoledb12\Clsid] @="{GUID1}" 
; Data1 of the following GUID must be 3 greater than the 
; previous, for example, 41dfe9f3 + 3 => 41dfe9ee. 
[HKEY_CLASSES_ROOT\myoledba12] 
@="Custom SQL Anywhere OLE DB Provider 12.0" 
[HKEY_CLASSES_ROOT\myoledba12\Clsid] @="{GUID4}" 
; Current version (or version independent prog ID) 
; entries (what you get when you have "SQLAny" 
; instead of "SQLAny.12") 
[HKEY_CLASSES_ROOT\SQLAny] 
@="SQL Anywhere OLE DB Provider" 
[HKEY_CLASSES_ROOT\SQLAny\Clsid] 
@="{GUID1}" 
[HKEY_CLASSES_ROOT\SQLAny\CurVer] 
@="SQLAny.12" 
[HKEY_CLASSES_ROOT\SQLAnyEnum] 
@="SQL Anywhere OLE DB Provider Enumerator" 
[HKEY_CLASSES_ROOT\SQLAnyEnum\Clsid] 
@="{GUID2}" [HKEY_CLASSES_ROOT\SQLAnyEnum\CurVer] 
@="SQLAnyEnum.12" [HKEY_CLASSES_ROOT\SQLAnyErrorLookup] 
@="SQL Anywhere OLE DB Provider Extended Error Support" 
[HKEY_CLASSES_ROOT\SQLAnyErrorLookup\Clsid] 
@="{GUID3}" 
[HKEY_CLASSES_ROOT\SQLAnyErrorLookup\CurVer] 
@="SQLAnyErrorLookup.12" 
[HKEY_CLASSES_ROOT\SQLAnyA] 
@="SQL Anywhere OLE DB Provider Assist" 
[HKEY_CLASSES_ROOT\SQLAnyA\Clsid] 
@="{GUID4}" 
[HKEY_CLASSES_ROOT\SQLAnyA\CurVer] 
@="SQLAnyA.12" 
; Standard entries (Provider=SQLAny.12) 
[HKEY_CLASSES_ROOT\SQLAny.12] 
@="Sybase SQL Anywhere OLE DB Provider 12.0" 
[HKEY_CLASSES_ROOT\SQLAny.12\Clsid] 
@="{GUID1}" 
[HKEY_CLASSES_ROOT\SQLAnyEnum.12] 
@="Sybase SQL Anywhere OLE DB Provider Enumerator 12.0" 
[HKEY_CLASSES_ROOT\SQLAnyEnum.12\Clsid] 
@="{GUID2}" 
[HKEY_CLASSES_ROOT\SQLAnyErrorLookup.12] 
@="Sybase SQL Anywhere OLE DB Provider Extended Error Support 12.0" 
[HKEY_CLASSES_ROOT\SQLAnyErrorLookup.12\Clsid] 
@="{GUID3}" 
[HKEY_CLASSES_ROOT\SQLAnyA.12] 
@="Sybase SQL Anywhere OLE DB Provider Assist 12.0" 
[HKEY_CLASSES_ROOT\SQLAnyA.12\Clsid] 
@="{GUID4}" 
; SQLAny (Provider=SQLAny.12) 
[HKEY_CLASSES_ROOT\CLSID\{GUID1}] 
@="SQLAny.12"
"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\\myoledb12.dll" 
"ThreadingModel"="Both" 
[HKEY_CLASSES_ROOT\CLSID\{GUID1}\OLE DB Provider] 
@="Sybase SQL Anywhere OLE DB Provider 12.0" 
[HKEY_CLASSES_ROOT\CLSID\{GUID1}\ProgID] 
@="SQLAny.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID1}\VersionIndependentProgID] 
@="SQLAny" 
; SQLAnyErrorLookup 
[HKEY_CLASSES_ROOT\CLSID\{GUID3}] 
@="Sybase SQL Anywhere OLE DB Provider Error Lookup 12.0" 
@="SQLAnyErrorLookup.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID3}\InprocServer32] 
@="d:\\mypath\\bin32\\myoledb12.dll" 
"ThreadingModel"="Both" 
[HKEY_CLASSES_ROOT\CLSID\{GUID3}\ProgID] 
@="SQLAnyErrorLookup.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID3}\VersionIndependentProgID] 
@="SQLAnyErrorLookup" 
; SQLAnyEnum [HKEY_CLASSES_ROOT\CLSID\{GUID2}]
@="SQLAnyEnum.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID2}\InprocServer32] 
@="d:\\mypath\\bin32\\myoledb12.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.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID2}\VersionIndependentProgID] 
@="SQLAnyEnum" 
; SQLAnyA [HKEY_CLASSES_ROOT\CLSID\{GUID4}] 
@="SQLAnyA.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID4}\InprocServer32] 
@="d:\\mypath\\bin32\\myoledba12.dll" 
"ThreadingModel"="Both" 
[HKEY_CLASSES_ROOT\CLSID\{GUID4}\ProgID] 
@="SQLAnyA.12" 
[HKEY_CLASSES_ROOT\CLSID\{GUID4}\VersionIndependentProgID] 
@="SQLAnyA"

Discuss this page in DocCommentXchange.