It is with delight that I can report that I found a OLEDB Simple Provider C++ sample that Microsoft had deposited in Github for posterity. I got it working with a little tweak and was able to write some client VBA ADO code to retrieve an ADO recordset. I also managed to step through the code to document the interaction.
Context - Data Access Solutions
I have been surveying data access solution for working with Excel worksheets. I have found the Microsoft.ACE.OLEDB provider but I have found some weaknesses with it and was wondering what else is possible. As a caveat, it is worth noting Microsoft say they will remove this feature (OLEDB Simple Provider) from a future version of Windows. Nevertheless, for due diligence this solution ought to be explored (later I intend to investigate .NET managed data providers which will be more future proof).
Context - OLEDB Simple Provider VB6 sample
Also note that I have been investigating trying to write a custom implementation for the OLEDB Simple Provider using VBA. VBA is extremely close to VB6. Unfortunately, VB6 has a few extra mechanisms to assist the interaction with the underlying OLEDB Simple Provider interfaces. These extra mechanisms are simply not present in VBA rendering any VB6 code informative but unusable. So I needed to step through the C++ sample because I want to write a C# implementation.
Because a VBA solution is not possible we need to drop down to lower level, C++, so we can figure out how to write a C# implementation.
Client VBA Code
Below is ordinary client VBA ADO code. The key details are in the ADO connection string and the string passed to ADO recordset's Open method.
The connection is critical to correctly using the OLEDB Simple Provider. First, we first specify Provider=MSDAOSP; in all cases. The second term Data Source is where we specify a COM ProgId of a COM class which implements IDataSource. In this case we pass Data Source=ospsampc because ospsampc is the ProgId of the Windows sample. The registry settings can be found in Appendix A .
The other key detail is what is passed to ADO recordset's Open method. In the sample a file path is passed. However, there is no reason why a different implementation may pass a web url or a COM moniker. In the sample code, the file is opened and processed; it is semicolon separated with a row and column count on the top row (see Appendix B).
Option Explicit
'* Tools->References
'ADODB Microsoft ActiveX Data Objects 6.1 Library C:\Program Files (x86)\Common Files\System\ado\msado15.dll
Sub TestCustom()
Const sFileName As String = "C:\Users\Simon\source\repos\Windows-class-samples\Samples\Win7Samples\dataaccess\osp\customer.txt"
Debug.Assert Dir(sFileName) <> ""
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
'*
'* ospsampc is progid of the custom simple provider
'*
oConn.Open "Provider=MSDAOSP;Data Source=ospsampc"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = oConn
rs.Open sFileName
rs.MoveFirst
'* lets get the recordset to a variant array
Dim vVarArray
vVarArray = rs.GetRows
End SubTweak to given sample code
So the sample code is at Windows-classic-samples/Samples/Win7Samples/dataaccess/osp/vc at master · Microsoft/Windows-classic-samples · GitHub . It compiles fine for me no problem. One slight tweak is that the output filename needs to be changed. So here are instructions...
- Right-click on project icon and from menu select Properties
- In Property Pages, select General in the left hand pane
- On the right hand pane, change Target Name from $(ProjectName) to ospsampc
Call Graph Walkthrough
The choreography between the classes can be a little confusing so I carefully stepped through the code and annotated it as this is the best way to learn the interaction. What you see below is VBA lines of code interleaved with the C++ sample code that gets called (typically via the OLEDB Simple Provider Dll, msdaosp.dll). Most of it is COM QueryInterface and COM initialisation. I have omitted calls to AddRef, Release and and calls to listening mechanisms as they cluttered the graph.
I recommend reading through the call graph but if you want a summary here it is VBA passes in the conn
- VBA passes ADO a connection string to the Connection.Open method, Provider=MSDAOSP;Data Source=ospsampc
- ADO sees the Provider=MSDAOSP and knows to load the OLEDB Simple Provider and passes control to its Dll, msdaosp.dll
- msdaosp.dll parses the second term of the connection string to find the custom implementation's progid, Data Source=ospsampc
- The progid ospsampc is found in the registry and the sample dll, ospsampc.dll, is loaded.
- ospsampc.dll is called via entry point DllGetClassObject to get an object that implements IClassFactory.
- IClassFactory.CreateInstance is called and ospsampc.dll returns an instance of MyDataSource (whilst creating an instance of MyOSPObject for later).
- The instance of MyDataSource is queried for interface IDataSource
- Control is passed back to VBA
- VBA code creates recordset and sets active connection. No sample code is called for this (must be purely ADO and/or msdaosp.dll).
- VBA code passes a filepath as the string parameter to the ADO recordset's Open method.
- IDataSource::getDataMember is called with the filepath passed in to bstrDM parameter, this method is implemented by MyDataSource::getDataMember.
- MyDataSource::getDataMember calls MyOSPObject::Init on its private instance of MyOSPObject passing the filepath as parameter.
- MyOSPObject::Init loads the file into memory
- MyDataSource::getDataMember queries MyOSPObject for the OLEDBSimpleProvider interface
- The OLEDB Simple provider dll, msdaosp.dll can now call MyOSPObject directly on its OLEDBSimpleProvider interface implementation
- msdaosp.dll calls MyOSPObject's OLEDBSimpleProvider interface implementation for row and column count etc as part of initialisation
- Control is passed back to VBA
- From now on, when VBA calls ADO to manipulate recordset msdaosp.dll makes calls on MyOSPObject's OLEDBSimpleProvider interface implementation.
VBA: oConn.Open "Provider=MSDAOSP;Data Source=ospsampc"
C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll
//
// get the class factory by calling DllGetClassObject
//
ospsampc.dll!DllGetClassObject(const _GUID & rclsid, const _GUID & riid, void * * ppvObj)
ospsampc.dll!MyClassFactory::MyClassFactory()
ospsampc.dll!MyClassFactory::QueryInterface(const _GUID & riid, void * * ppv)
//
// call CreateInstance on class factory, creates a MyDataSource (and during initialisation internally creates a MyOSPObject)
// returns an IUnknown , no QI for IDataSource yet
//
ospsampc.dll!MyClassFactory::CreateInstance(IUnknown * pUnkOuter, const _GUID & riid, void * * ppv)
ospsampc.dll!MyDataSource::MyDataSource()
ospsampc.dll!MyDataSource::Init()
ospsampc.dll!MyOSPObject::MyOSPObject()
ospsampc.dll!CExList::CExList()
ospsampc.dll!MyDataSource::QueryInterface(const _GUID & riid, void * * ppv)
//
// After some interim calls to QI for IUnknown we soon get a QI for IDataSource
//
ospsampc.dll!MyDataSource::QueryInterface(const _GUID & riid, void * * ppv)
VBA: Set rs = New ADODB.Recordset
VBA: Set rs.ActiveConnection = oConn
VBA: rs.Open sFileName
C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll
//
// the parameter passed by VBA in the rs.Open method is passed as bstrDM below
// in this case a file path which gthis implementation will load and process
// but the string could be an a block of COM moniker pointing to a block of cells on an Excel worksheet
//
// after initialization MyOSPObject is QI'ed for OLEDBSimpleProvider interface
//
ospsampc.dll!MyDataSource::getDataMember(wchar_t * bstrDM, const _GUID & riid, IUnknown * * ppUnk)
ospsampc.dll!MyOSPObject::Init(wchar_t * pwszFilePath)
ospsampc.dll!MyOSPObject::QueryInterface(const _GUID & riid, void * * ppv)
C:\Program Files (x86)\Common Files\system\ole db\msdaosp.dll
//
// now msdaosp.dll calls MyOSPObject directly to QI for OLEDBSimpleProvider (again)
// and then starts calling on OLEDBSimpleProvider::getRowCount , OLEDBSimpleProvider::getColumnCount etc.
//
ospsampc.dll!MyOSPObject::QueryInterface(const _GUID & riid, void * * ppv) Line 87
ospsampc.dll!MyOSPObject::getRowCount(long * pcRows)
ospsampc.dll!MyOSPObject::getColumnCount(long * pcColumns)
ospsampc.dll!MyOSPObject::isAsync(int * pbAsynch)
Links
Appendix A - Registry Entries for sample
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}]
@="ospsampc"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\InprocServer32]
"ThreadingModel"="Both"
@="C:\\Users\\Simon\\source\\repos\\Windows-class-samples\\Samples\\Win7Samples\\dataaccess\\osp\\vc\\Debug\\ospsampc.dll"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\ProgID]
@="ospsampc"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{1E79B2C1-077B-11d1-B3AE-00AA00C1A924}\VersionIndependentProgID]
@="ospsampc"Appendix B - Sample database file
So the given database file has semicolons separating fields with newline as row terminator. There is also a row and column count on the top row. Also we have a row of column headers. No schema information though. Here at the top rows...
90;10
CustomerID;CompanyName;ContactName;ContactTitle;Address;City;Region;PostalCode;Country;Phone;
ALFKI;Alfreds Futterkiste;Maria Anders;Sales Representative;Obere Str. 57;Berlin;;12209;Germany;030-0074321;
ANATR;Ana Trujillo Emparedados y helados;Ana Trujillo;Owner;Avda. de la Constitución 2222;México D.F.;;05021;Mexico;(5) 555-4729;
ANTON;Antonio Moreno Taquería;Antonio Moreno;Owner;Mataderos 2312;México D.F.;;05023;Mexico;(5) 555-3932;
AROUT;Around the Horn;Thomas Hardy;Sales Representative;120 Hanover Sq.;London;;WA1 1DP;UK;(71) 555-7788;
BERGS;Berglunds snabbköp;Christina Berglund;Order Administrator;Berguvsvägen 8;Luleå;;S-958 22;Sweden;0921-12 34 65;
BLAUS;Blauer See Delikatessen;Hanna Moos;Sales Representative;Forsterstr. 57;Mannheim;;68306;Germany;0621-08460;
No comments:
Post a Comment