OGIS OLE DB Provider Example

Click here to get the sample associated with this walkthrough.

OGIS OLE DB Provider example

Object Model Diagram: Click here.

Description: The example implements a spatially enabled OLE DB provider for personal geodatabases.

Design: Microsoft OLE DB provider architecture

Categories: OGISDataProvider (not necessary for use by ArcGIS).

Interfaces: OLE DB standard interfaces.

How to use

Open and build the project SampleProvider.dsp, to compile and register the DLL (you will have to modify the path to the ESRI type libraries in the StdAfx.h file to correspond to your ArcGIS install directory).
Make an OLE DB connection from ArcGIS using the SampleProvider. Double click on the Add OLE DB connection object in either ArcCatalog (TOC panel) or ArcMap (Add Data dialog box). This will call the Data Link Properties dialog box—on the Provider tab choose the 'SampleProv OLE DB Provider'.
Click Next to bring up the Connection tab and enter the path to and name of a personal geodatabase or Microsoft Access .mdb file in the Data Source field. Click Next to move to the Advanced tab and click OK. A new OLE DB connection should appear in the TOC panel or Add Data dialog box.
Test the provider by browsing the data.

About the OGIS OLE DB provider example

This example demonstrates how to create a read-only OLE DB provider, which meets all the requirements for ArcGIS; that is, it can identify, query, and retrieve spatial data.

The sample provider can read data from an ArcGIS personal geodatabase or Microsoft Access .mdb file (don't confuse this example with the standard ESRI OLE DB provider, which provides access to data in various ESRI formats). The example follows the standards in the OpenGIS (OGIS) OLE-COM Simple Features Specification.

The example serves personal geodatabase data via OLE DB. There is already a standard ESRI OLE DB provider to do this job, but the example exists to show how to implement OLE DB providers for spatial data.

The following description assumes that you have a working knowledge of Microsoft's Component Object Model (COM) technology and that you are familiar with Microsoft's OLE DB data access technology. To create an OLE DB provider, you should have a working knowledge of C++ templates.

Starting to develop an OLE DB provider

There are a few options for developing your own provider.

Use Microsoft Visual C++ OLE DB templates to create your provider. At Visual C++ 6.0 these templates supported just read-only providers. With Visual C++ .NET they also support updatable providers.
Use Microsoft's simple OLE DB provider toolkit in the Data Access 2.x SDK for creating read-only providers.
Use a third party OLE DB provider toolkit, for example, http://www.simba.com/index.htm.
Write a provider in C++ using standard ATL classes.

The ATL Object wizard creates a provider using the OLE DB template classes.

This example was created by using the Visual C++ 6.0 OLE DB templates. A complete discussion of these templates is beyond the scope of this book. See the Microsoft article 'Using the Visual C++ 6.0 OLE DB Provider Templates' for an explanation of how to create a Visual C++ project from these templates using the ATL Object Wizard.

The resulting project will contain seven standard OLE DB COM objects (in three .h files). These objects are implemented using the ATL data access templates (found in atldb.h from your Visual Studio installation folder).

Data Source—a connection to your physical file or database.
Session—the current operating environment of your data source.
Command—an object used to issue commands (SQL statements) and create rowsets.
Rowset—an object that contains rows of data organized in columns.
Three Schema Rowsets—rows containing information about the schema of your data source.

Implementing the Data Source object

The Data Source object represents a connection to your physical file or database. Look in the example's SampleProvDS.h file (when running the templates in a fresh project it will be in the <YourProvider>DS.h file).

[VCPP]


        class ATL_NO_VTABLE CSampleProvSource: public CComObjectRootEx <
  CComSingleThreadModel > , public CComCoClass < CSampleProvSource,
  &CLSID_SampleProv > , public IDBCreateSessionImpl < CSampleProvSource,
  CSampleProvSession > , public IDBInitializeImpl < CSampleProvSource > ,
  public IDBPropertiesImpl < CSampleProvSource > , public IPersistImpl <
  CSampleProvSource > , public IInternalConnectionImpl < CSampleProvSource > 
{
    ...
}

A Data Source object must support the important IDBCreateSession, IDBInitialize, and IDBProperties interfaces. Note how the class implements these by inheriting from the template classes IDBCreateSessionImpl, IDBInitializeImpl, and IDBPropertiesImpl.

One of the class's main responsibilities is to open (initialize) and close (uninitialize) your data source. To do this the example code overrides template class implementation of the IDBInitialize::Initialize method.

The code creates an Access workspace factory and opens the database. The name of the database is obtained from the DBPROPSET_DBINIT property in the DBPROP_INIT_DATASOURCE property set. The data source name is set by the OLE DB consumer, in this case ArcGIS, using the IDBProperties interface. This happens when you make an OLE DB connection to this provider using the Data Link dialog box.

The data source properties are derived from the Data Link dialog.

[VCPP]


        STDMETHOD(Initialize)(void)
{
  HRESULT hr;
  if (FAILED(hr = IDBInitializeImpl < CSampleProvSource > ::Initialize()))
    return hr;

  // Get the database property from the OLE DB properties
  DBPROPIDSET propIDSet;
  DBPROPID propID = DBPROP_INIT_DATASOURCE;

  propIDSet.rgPropertyIDs = &propID;
  propIDSet.cPropertyIDs = 1;
  propIDSet.guidPropertySet = DBPROPSET_DBINIT;

  ULONG nProps;
  DBPROPSET *propSet = 0;

  if (FAILED(hr = GetProperties(1, &propIDSet, &nProps, &propSet)))
    return E_FAIL;

  IPropertySetPtr ipConnProps(CLSID_PropertySet);

  ipConnProps->SetProperty(CComBSTR(OLESTR("DATABASE")), propSet
    ->rgProperties[0].vValue);

  ::VariantClear(&propSet->rgProperties[0].vValue);
  ::CoTaskMemFree(propSet->rgProperties);
  ::CoTaskMemFree(propSet);

  // Create an Access WorkspaceFactory and open the Workspace
  IWorkspaceFactoryPtr ipAccessWSF(CLSID_AccessWorkspaceFactory);
  if (FAILED(hr = ipAccessWSF->Open(ipConnProps, 0, &m_ipWS)))
    return E_FAIL;

  return hr;
}

In IDBInitialize::Uninitialize, the sample frees the Workspace object, which is connected to the Access database.

You may want your Session object to maintain references to objects that the Data Source holds onto (for efficiency reasons). In the sample IDBCreateSession::CreateSession is implemented to pass the workspace object to the Session.

Because the Data Source, Session, Command and Rowset objects all work closely together, often in parent/child relationships, and because they are only exposed through COM, the example declares their member variables as public so that each class can reference them with simplicity.

Note that the sample imports some of the standard ESRI type libraries in its stdafx.h file, so it can use ArcObjects for its implementation. This is only necessary because ArcObjects is convenient when implementing a provider for personal geodatabases. Normally you will not need to import these type libraries since ArcObjects will not be relevant to your data source.

The example uses ArcObjects for convenience. ArcObjects is not necessary for implementing OLE DB providers.

Implementing the standard schema rowsets

Schema rowsets contain information about the structure of your data source. The ATL Object Wizard creates implementations for the three standard OLE DB schema rowsets: Tables, Columns, and Provider Types. They are located in the SampleProvSess.h file.

The Tables schema rowset contains a list of all the tables in your data source. The templates provide the C<YourProvider>SessionTRSchemaRowset class. The example implements this rowset by using IWorkspace::DatasetNames to get a list of the datasets in the database, then populating the template objects with this information. A CTABLESRow object is used for each table found (this class is defined in Atldb.h). Note, from the three standard schema rowsets, ArcGIS uses only the Tables schema rowset.

The Columns schema rowset contains a list of all the columns and their respective tables in your data source. The templates provide the C<YourProvider>SessionColSchemaRowset class. The example does not implement this schema rowset. Although ArcGIS does not require this schema rowset, if you plan to access your data via other clients or from ADO, you should implement this rowset.

The Provider Types schema rowset contains a list of all the data types that your data source supports. The templates provide the C<YourProvider>SessionPTSchemaRowset class. The example does not implement this schema rowset.

Implementing the OGIS schema rowsets

ArcGIS uses the OGIS schema rowsets to discover what tables are spatially enabled and other spatial information.

The Feature Tables schema rowset is mandatory. It contains a list of the feature classes in your data source. You will need to create a row class to hold information about each feature class. In the example, this class is called OGISTables_Row. The Provider Column Map macros make the implementation fairly simple.

[VCPP]


        class OGISTables_Row
{
  public:
    WCHAR m_szAlias[4];
    WCHAR m_szCatalog[4];
    WCHAR m_szSchema[4];
    WCHAR m_szTableName[129];
    WCHAR m_szColumnName[129];
    WCHAR m_szDGName[129];

    OGISTables_Row()
    {
        m_szAlias[0] = L '\0';
        m_szCatalog[0] = L '\0';
        m_szSchema[0] = L '\0';
        m_szTableName[0] = L '\0';
        m_szColumnName[0] = L '\0';
        m_szDGName[0] = L '\0';
    }
};

The OGISTables_Row class is used as the array element to implement the Feature Tables schema rowset.

The class for the schema rowset itself is CSampleProvSessionSchemaOGISTables. The Execute function populates the rowset.

The Geometry Columns schema rowset is also mandatory. It contains a list of the geometry columns and their associated feature classes in your data source. In the example, the OGISGeometry_Row class holds information about a geometry column. The class for the schema rowset is CSampleProvSchemaOGISGeoColumns.

The Spatial Reference schema rowset contains a list of the spatial references in your data source. ArcGIS doesn't use this schema rowset. If you want to implement this class, the example code contains a basic definition, CSampleProvSessionSchemaSpatRef, and a row class, OGISSpat_Row, to hold information about a spatial reference.

Implementing the Session object

The Session object represents the current operating environment of your data source. The example does not need to override any of the template's implementation; however, this object does contain the ATL Schema Map that defines which schema rowsets exist. In the example, see the CSampleProvSession class:

The Session object defines which schema rowsets exist in your provider.

[VCPP]


        BEGIN_SCHEMA_MAP(CSampleProvSession)SCHEMA_ENTRY(DBSCHEMA_TABLES,
  CSampleProvSessionTRSchemaRowset)SCHEMA_ENTRY(DBSCHEMA_COLUMNS,
  CSampleProvSessionColSchemaRowset)SCHEMA_ENTRY(DBSCHEMA_PROVIDER_TYPES,
  CSampleProvSessionPTSchemaRowset)SCHEMA_ENTRY(DBSCHEMA_OGIS_FEATURE_TABLES,
  CSampleProvSessionSchemaOGISTables)SCHEMA_ENTRY
  (DBSCHEMA_OGIS_GEOMETRY_COLUMNS, CSampleProvSchemaOGISGeoColumns)SCHEMA_ENTRY
  (DBSCHEMA_OGIS_SPATIAL_REF_SYSTEMS, CSampleProvSessionSchemaSpatRef);
END_SCHEMA_MAP()

The GUIDs of the OGIS schema rowsets are contained in the OleDBGis.h file (available from www.opengis.org). The guids.cpp file in the example forces these GUID definitions to be compiled into the program thus avoiding link errors. Note that in the project settings for guids.cpp, it is set to 'Not using precompiled headers' (under the C/C++ tab).

Implementing the Command object

The Command object is used to issue commands (SQL statements) and create rowsets. It is implemented in the SampleProvRS.h and .cpp files.

In the OLE DB specification, the purpose of the Command object is mostly for database optimization, so that a SQL statement can be created once, optionally prepared, and reexecuted with optional parameters to create new rowsets.

The ATL Object Wizard provides a standard Command class. In the example, the main modification is in the implementation of ICommmand::Execute. Note that this ICommand is a different interface than esriSystemUI.ICommand. In fact, when esriSystemUI.olb is imported in the example, ICommand is renamed to IESRICommand to avoid any possible clash.

The example implementation of ICommand::Execute sets a query filter, opens the table, and creates the Rowset object that is the result of executing the command. References to the table and query filter objects are cached as class members to provide efficiency. These references are freed when new command text is set by the implementation of ICommandText::SetCommandText.

Queries can have spatial criteria—these are specified as parameters to the Command object. In this case you must implement ICommandWithParameters. The example provides a C++ template class for implementing this interface: ICommandWithParametersImpl. The CSampleProvCommand class inherits from this template. The template does not provide a complete implementation of ICommandWithParameters; it only handles the OGIS spatial parameters. However, this is enough to support the requirements of the example.

The ICommandWithParameters interface is used to access OGIS-compliant spatial query criteria.

The implementation of ICommand::Execute processes spatial parameters using a helper function: SetupSpatialFilter. The example needs to convert the OGIS WKB back to an ESRI geometry object since the data source is an ESRI personal geodatabase. You will probably not need to do this, since it is unlikely your data source will use ESRI geometry objects. Note that the OGIS spatial filter operators (touches, within, and so on) are defined in the OleDBGis.h file.

Implementing the Rowset object

The Rowset object contains rows of data organized in columns. It is defined in the SampleProvRS.h file.

[VCPP]


        class CSampleProvRowset: public CRowsetImpl < CSampleProvRowset,
  CSampleProvFeatureRowData, CSampleProvCommand, CVirtualArray <
  CSampleProvFeatureRowData > , CSimpleRow > , public IColumnsRowsetImpl <
  CSampleProvRowset, CSampleProvCommand > 
{
    ...
};

The ATL template class CRowsetImpl provides most of the implementation. A parameter to the template is the storage class that will represent one row of data. For this purpose, the example implements the CSampleProvFeatureRowData class. Compare the implementation of this class with the OGISTables_Row and CTABLESRow classes previously discussed; once again the Provider Column Map does most of the work.

[VCPP]


        BEGIN_PROVIDER_COLUMN_MAP(CSampleProvFeatureRowData)PROVIDER_COLUMN_ENTRY("OID",
  1, m_oidColumn)PROVIDER_COLUMN_ENTRY("SHAPE", 2, m_shapeColumn)
  END_PROVIDER_COLUMN_MAP()

For simplicity, the example deals with only the OID and SHAPE columns because they are present in every geodatabase feature class. The example does not handle tables not registered with the geodatabase, that is, those that have no OID column.

If your data has a fixed schema, as is often the case, this mechanism will work well for you. If your schema varies from table to table, then you will need a more elaborate row class than the one presented here. For some ideas on doing this, see the Microsoft ATLMTO sample in MSDN.

Another parameter to CRowsetImpl is the array type that will represent the set of rows. By default the ATL CSimpleArray template class is used. For datasets of any significant size, CSimpleArray will not be suitable since the entire dataset will be loaded into memory. As an improvement to this, the example defines a CVirtualArray template class, which wraps an ArcObjects cursor so that only the current record is held in memory at one time. It is this class that retrieves the ESRI Geometry for the row and converts it to an OGIS WKB. Your provider will create the WKB from whatever format your data source's geometry is stored in.

When implementing Rowsets, the array class implementation is important for performance.

The example's main code for Rowset is in the Execute method. The CVirtualArray object is initialized with a cursor representing the results of the Command on the table and the number of rows that the cursor will return. Note that ATL must have this row count—you should take care that it is calculated as efficiently as possible.

CSampleProvRowset also inherits from the template class IColumnsRowsetImpl. The standard OLE DB mechanism for obtaining column definitions is to use IColumnsInfo::GetColumnInfo. However, this method returns fixed definitions for the column descriptions; the IColumnsRowset interface exists to allow for more flexible column metadata reporting. Additionally, OLE DB consumers can get the column information directly from a rowset without having to return to the Session object and IDBSchemaRowset. Implementation of IColumnsRowset is mandatory for OGIS-compliant providers.

The OGIS specification defines additional metadata columns: GEOM_TYPE, SPATIAL_REF_SYSTEM_ID and SPATIAL_REF_SYSTEM_WKT, so that consumers can identify the spatial column containing the WKB geometry, what its geometry type is, and what spatial reference system it belongs to.

The example defines the IColumnsRowsetImpl template class as a way of implementing IColumnsRowset. It uses CColumnsRowsetRow as a helper class. Examine the GetColumnsRowset and PopulateRowset functions; you will need to replace this code in your provider.

The example does not pass back the OGIS Spatial Reference System ID or the WKT (see the example's GetDBStatus function) because these are currently expensive to obtain in ArcObjects. Your provider should attempt to support these.

Development licensing	Deployment licensing
ArcGIS for Desktop Basic	ArcGIS for Desktop Basic
ArcGIS for Desktop Standard	ArcGIS for Desktop Standard
ArcGIS for Desktop Advanced	ArcGIS for Desktop Advanced

OGIS OLE DB Provider Example