OLE DB 2.0 Architecture - Supporting Remote Data Exchange

Microsoft OLE DB stands for Object Linking and Embedding Database and provides a standardised way to represent and work with data from different sources, enabling applications to access and manipulate data in a consistent and efficient manner.

1. OLE DB 2.0 ARCHITECTURE
Arun Seetharaman
Computer Call
January 20, 1999

3. Data
access
WINDOWS DNA DATA
ACCESS

4. AGENDA
➔Review of OLE DB object model
•New in OLE DB 2.0
•Connecting to OLE DB data sources
•Navigating hierarchical data
•OLE DB service components
•Questions and answers

5. DATA AS A
COMMON ABSTRACTION
•Common representation of data
•Code/knowledge reuse
•Tools can access data generically
•Generic components can add functionality
•Components can combine different types of
data
◆ Sharing data
⚫Reduces memory footprint
⚫Reduces data movement
⚫Better component coordination

6. TERMINOLOGIES
•Data Provider
•Software that implements OLEDB Interfaces
•Exposes data in a tabular form
◆Consumer
⚫Consumes the data
◆Service Provider
⚫Consumes data exposed by Data Provider
⚫Transforms and provides data

7. OLE DB DESIGN PHILOSOPHY
•Data stores provide interfaces to
native functionality
•Data store is not required to
implement extended functionality
◆Consumers assume common functionality
⚫Consumers don't need excess conditional code
◆“Service components” augment data store's
native functionality
⚫Generic, reusable components provide a guaranteed
level of interoperability

8. Data source
IDBCreateSession
Rowset
OpenRowset()
Rowset
Execute()
OLE DB OBJECT MODEL
ICommand
Command
CreateCommand ()
IDBCreateCommand
IOpenRowset
Session
CreateSession()

9. AGENDA
Review of OLE DB object model
➔New in OLE DB 2.0
➔Connecting to OLE DB data sources
•Navigating hierarchical data
•OLE DB service components
•Questions and answers

10. CONNECTING TO
A DATA SOURCE
BEFORE 2.0
•Via root enumerator
•Passing provider’s CLSID to CoCreateInstance
•Hard-code provider CLSIDs
•Obtain CLSID via application specific code
◆Disadvantages:
⚫No common UI
⚫No common way to represent or persist
connection information

11. CONNECTING TO
A DATA SOURCE
OLE DB 2.0
•OLE DB 2.0 provides common services to
connect to data sources:
•IDataInitialize
•Persists connection strings
•Leverages OLE DB services
(More on this later…)
•IDBPromptInitialize
•Uses the data links UI
•Manages data source properties and data link
(*.UDL) files

12. WHAT CAN I DO WITH
IDATAINITIALIZE ?
•Modify sample consumer
to use IDataInitialize
•Instantiate the OLE DB initialization service
CLSID_MSDAINITIALIZE
One simple change to current code
•Create a Data Source object via
IDataInitialize::CreateDBInstance

13. // Create the Initialization Component
hr = CoCreateInstance( CLSID_MSDAINITIALIZE, NULL,
CLSCTX_INPROC_SERVER,
IID_IDataInitialize, (void **)&pIDataInit);
if (FAILED(hr))
{… }
// Create an instance of the OLE DB provider for Jet
hr = pIDataInit->CreateDBInstance( CLSID_JETOLEDB_3_51,
NULL, CLSCTX_INPROC_SERVER,
NULL, IID_IDBInitialize, (IUnknown**) &pIDBInit);
if (FAILED(hr))
{… }

14. WHAT ELSE CAN I DO
WITH IDATAINITIALIZE?
•Given a data source, retrieve a connection string
•GetInitializationString
•Persist a connection string
•WriteStringToStorage

15. // Use IDataInitialize::GetInitializationString to return connection
string
hr = pIDataInit->GetInitializationString( (IUnknown*)pIDBInit, TRUE,
&pwszInitString);
if (FAILED(hr))
{… }
// Use IDataInitialize::WriteStringToStorage to persist connection string
hr = pIDataInit->WriteStringToStorage(pwszFileName, pwszInitString,
CREATE_ALWAYS);
if (FAILED(hr))
{… }

16. WHAT ELSE CAN I DO
WITH IDATAINITIALIZE?
•Persist a connection string
•LoadStringFromStorage
•Given a connection string, instantiate a data source object
•GetDataSource

17. //Create the Initialization Component
hr = CoCreateInstance( CLSID_MSDAINITIALIZE, NULL,
CLSCTX_INPROC_SERVER, IID_IDataInitialize,
(void **)&pIDataInit);
if (FAILED(hr))
{… }
// Read the initialization info from previous example
hr = pIDataInit->LoadStringFromStorage(pwszFileName,
&pwszInitString);
if (FAILED(hr))
{… }
// Create a Data Source object using the initialization string
hr = pIDataInit->GetDataSource(NULL, CLSCTX_INPROC_SERVER,
pwszInitString,IID_IDBInitialize, (IUnknown**) &pIDBInit);
if (FAILED(hr))
{… }

18. DATA LINKS
•Common UI for data sources
•Can be used by any application
•Reflects properties of the provider
•Connection information can be persisted as “Data links”
Data links can be used from ADO or OLE DB
Invoked from desktop

19. IDBPROMPTINITIALIZE
•Program access to the Data Links service
•PromptDataSource
•Opens data link properties dialog
•Returns a data link with properties
•PromptFileName
•Opens Select data link dialog
•Returns path to data link (*.UDL) file

20. DATA LINKS EXAMPLE
•Demonstrate data links UI from Windows
®
desktop
•Data links UI via RowsetViewer
•RowsetViewer is available in the
data access SDK

21. AGENDA
Review of OLE DB object model
New in OLE DB 2.0
Connecting to OLE DB data sources
➔Navigating hierarchical data
•OLE DB service components
•Questions and answers

22. NAVIGATING HIERARCHICAL
DATA
•Data is more than just isolated tables
•Hierarchies arrange data “nodes” in a tree
•Modeling hierarchies in OLE DB

23. OLE DB
IRowset->GetNextRows(chap1,…)
chap1
chap1
IRowset->GetNextRows(chap2,…)
chap2
chap2
IRowset->GetNextRows(chap3,…)
chap3
chap3
OLE DB
.....................
...
...
...
...
HIERARCHIES VIA CHAPTERS
•Efficiently expose hierarchical data through a single rowset
per level
•Appears as child recordsets in ADO 2.0

24. SHAPE EXAMPLE
SHAPE {select * from Orders}
APPEND({select * from `Order
Details`}RELATE OrderID TO
OrderID)
Orders rowset
OLE DB
......
OLE DB
............
Order details rowset
chap1
chap2
chap3
...
...
...
...
...
chap1
chap2
chap3

25. SHAPE PROVIDER
•Constructs hierarchical rowsets from data providers
•Driven by Shape data
manipulation syntax
◆Types of Hierarchies
⚫Appends chapter-valued columns to parent
rowset
⚫Generates parent rowset from child rowset
with aggregations
⚫Parameterised Hierarchies

26. SHAPE EXAMPLE
•Load shape provider’s data link
from storage
•Create a command object
•Set command text with a shape command
•Set any rowset properties
on the command
•Execute the command to obtain
parent rowset

27. // Create an instance of the OLE DB Initialization Component
hr = CoCreateInstance( CLSID_MSDAINITIALIZE, NULL,
CLSCTX_INPROC_SERVER, IID_IDataInitialize,
(void **)&pIDataInit);
if (FAILED(hr))
{ ...}
// Read the Data Link file for this example:
hr = pIDataInit->LoadStringFromStorage(pwszFileName,
&pwszInitString);
if (FAILED(hr))
{ ...}
// Create a Data Source object using the initialization string
hr = pIDataInit->GetDataSource(NULL, CLSCTX_INPROC_SERVER,
pwszInitString,IID_IDBInitialize, (IUnknown**) &pIDBInit);
if (FAILED(hr))
{ ...}

28. // Create the command object
hr = pSession->QueryInterface( IID_IDBCreateCommand,(void
**)&pIDBCreateCommand );
if (FAILED(hr))
{ ...}
hr = pIDBCreateCommand->CreateCommand( NULL,
IID_ICommandText, (IUnknown **)&pICommandText);
if (FAILED(hr))
{ ...}
pwsz_CommandText =
L"SHAPE{select * from Orders} COMPUTE (SUM(FREIGHT)) as
CHAPTER by customerID) ";

hr = pICommandText->SetCommandText( guidDialect,
pwsz_CommandText);
if (FAILED(hr))
{ ...}

29. NAVIGATING CHAPTERS
•IParentRowset::GetChildRowset
returns child rowset
•IRowset:GetNextRows(hChapter…) returns child rows for
that chapter

30. AGENDA
Review of OLE DB object model
New in OLE DB 2.0
Connecting to OLE DB data sources
Navigating hierarchical data
➔OLE DB service components
•Questions and answers

31. SERVICE COMPONENTS
•What are service components?
•How do they work?
•2.0 service components
•Client cursor engine
•Resource pooling
•How do I use them?

32. Service Components make up the difference
Minimum provider interfaces
Service
components
OLE DB consumer
WHAT ARE
SERVICE COMPONENTS?
•A bridge between consumer expectation and provider
capability

33. WHAT ARE
SERVICE COMPONENTS?
•OLE DB core components that augment a provider’s
functionality
•Work with existing providers
•Without knowledge of the service
•Without special logic
•Work with consumers
•ADO uses them automatically

34. SERVICE COMPONENTS
•Service component
manager
•Invokes services
as needed
•Handles navigation
between interfaces
implemented by
different objects
•Provides a common
entry point for hooking
other services
•Consumer calls
methods directly
OLE DB rowset
Provider rowset
Service component manager
Service component
Service component

35. 2.0 SERVICE COMPONENTS
•Client cursor engine
•Scrollable cursors
•IRowsetFind implementation
•Updateable rowsets over a SQL provider
◆Resource pooling
⚫Multiple, homogenous pools
⚫Caches provider information
⚫Automatic transaction enlistment in Microsoft
Transaction Server

36. USING SERVICE COMPONENTS
•Invoking service components
•Create a data source using IDataInitialize or
IDBPromptInitialize
•Use ADO
•Use the data links UI
•Service component manager determines which services to
invoke
•Applications should request the functionality they want
•Via command and rowset properties

37. DISABLING SERVICE
COMPONENTS
•By provider
•OLEDB_SERVICES registry entry
◆By Consumer
⚫Property control of core services
⚫“OLE DB Services” String

38. CLIENT CURSOR EXAMPLE
•Connect to a sample provider
•Request backwards scrolling
•Re-connect using service components
•Demonstrate scrolling

39. OLE DB RESOURCE POOLING
•Improves performance and scaling
•Works with Microsoft Transaction Server
•What is pooled?
•Initialized data source object
Based on connection info
•Associated session object
•Provides multiple, homogenous pools
•Reduces contention
•Manages object lifetimes and
state changes

40. OLE DB POOLING
•Application
•Calls middle tier object
◆OLE DB services
⚫Returns proxy DSO
⚫Sets initialization properties
⚫Calls Initialize()
⚫Caches information
⚫Sets cached properties
⚫Calls Initialize()
⚫Creates a session
⚫Enlists in transaction
⚫Does work
Windows NT
MTS
◆Middle tier object
⚫Calls CreateDBInstance()
OLE DB services
OLE DB
⚫Creates provider DSO
SQL Server
DSO
Provider
DCOM
MT
O

41. OLE DB POOLING
•Application
•Calls middle tier object
◆Middle tier object
⚫Calls CreateDBInstance()
◆OLE DB services
⚫Returns proxy DSO
⚫Sets initialization properties
⚫Calls Initialize()
⚫Caches information
⚫Creates Provider DSO
⚫Sets cached properties
⚫Calls Initialize()
⚫Creates a Session
⚫Enlists in transaction
⚫Does work
⚫Goes away
Windows NT
MTS
OLE DB services
OLE DB
SQL Server
DSO
Provider

42. OLE DB POOLING
Windows NT
MTS
OLE DB services
OLE DB
SQL Server
DSO
Provider
HTTP
Internet
Information
Server
MT
O
Web
browser
⚫Calls middle tier object
⚫Calls CreateDBInstance()
⚫Returns proxy DSO
⚫Sets initialization properties
⚫Calls Initialize()
⚫Caches information
⚫Sets cached properties
⚫Calls Initialize()
⚫Creates a Session
⚫Enlists in transaction
⚫Does work
Provider
OLE DB
DSO
Oracle
◆Creates provider DSO
◆Middle tier object
◆OLE DB Services
◆Web browser

43. OLE DB POOLING
Windows NT
MTS
OLE DB services
OLE DB
SQL Server
DSO
Provider
Web
browser
⚫Calls middle tier object
⚫Calls CreateDBInstance()
⚫Returns proxy DSO
⚫Sets initialization properties
⚫Calls Initialize()
⚫Caches information
⚫Sets cached properties
⚫Calls Initialize()
⚫Creates a Session
⚫Enlists in transaction
⚫Does work
⚫Goes away
Provider
OLE DB
DSO
Oracle
◆Creates provider DSO
◆Middle tier object
◆OLE DB Services
◆Web browser

44. OLE DB POOLING
Windows NT
MTS
OLE DB services
OLE DB
SQL Server
Provider
DSO
Web
browser
Provider
DSO
Oracle
DCOM
MT
O
◆Application
⚫Calls middle tier object
◆OLE DB Services
⚫Sets initialization properties
⚫Calls CreateDBInstance()
⚫Caches information
⚫Does work
◆Middle tier object
⚫Returns proxy DSO
⚫Returns DSO from pool DSO
⚫Calls
Initialize()

45. OLE DB POOLING
Windows NT
MTS
OLE DB services
OLE DB
SQL Server
Provider
DSO
Web
browser
Provider
DSO
Oracle
◆Application
⚫Calls middle tier object
◆Middle tier object
⚫Calls CreateDBInstance()
⚫Sets initialization properties
⚫Calls Initialize()
⚫Does work
⚫Goes away
◆OLE DB services
⚫Returns proxy DSO
⚫Caches information
⚫Returns DSO from pool

46. Windows NT
MTS
OLE DB services
OLE DB
SQL Server
Provider
DSO
Web
browser
Provider
DSO
Oracle
◆Application
◆Middle tier object
◆OLE DB services
OLE DB POOLING

47. OLE DB POOLING
OLE DB Services
MTS
Web
browser
Windows NT
OLE DB
DSO
SQL
Server
Provider
◆Application
◆Middle tier object
◆OLE DB services
⚫Oracle connection times out

48. OLE DB POOLING
OLE DB Services
MTS
Web
browser
Windows NT
◆Application
◆Middle tier object
◆OLE DB services
⚫Oracle connection times out
⚫SQL Server connection
times out

49. LEVERAGING OLE DB POOLING
•Connect using CreateDBInstance, GetDataSource, or data
links UI
•Don’t request provider specific interfaces prior to
connecting
•Don’t request prompting
•Don’t uninitialize
•Only use one session per connection
•…or, just use ADO!

50. OLE DB SUMMARY
•OLE DB 2.0 improves the architecture for universal data
access
•Easier access to your data sources
•Ability to model hierarchical data
•Service components
•OLE DB 2.0 service components
•Client cursor engine support for scrolling and searching
•Resource pooling improves performance and scalability
•Leveraging service components is easy