Understanding UDA and OLE DB

1
Understanding UDA and OLE DB

• OLE DB is a sort of ODBC superset that stems out
  directly from the Universal Data Access vision.
• Dino Esposito
• desposito_at_artis.net

2
Agenda

• UDA The rationale behind
• Getting (a bit more) technical From ODBC to OLE
  DB
• The role of ADO
• Legacy data
• Accessing OLE DB data sources through ADO and ASP

3
Brief History of Data Access

• ODBC provides a common programming interface to
  access a number of different database platforms.
  It uses SQL as the language to define and drive
  the access.
• Relatively easy for C/C developers, not so easy
  for Visual Basic programmers.
• DAO (based on JET engine) and RDO (a COM-based
  version of ODBC) optimize access to local and
  remote databases.
• ODBCDirect is a DAO mode that switches to RDO,
  rather than JET, as the low-level engine.

4
An Enterprise Scenario
Which data companies manage?

• Its common for companies today to have
  heterogeneous data forming the actual enterprise
  datastore.
• You may have host servers as well as DBMS. Not to
  mention documents, fax, and email.
• Legacy data in proprietary format.
• Data that is specific to the activity but not
  coded in any database. (Real-time systems,
  embedded systems)

No matter the format, data is just data
5
A typical Enterprise Scenario
Network
Universal Data Access Layer
6
Towards an Enterprise Solution

• Wouldnt it be great if all the databases across
  the globe spoke the same language and exposed
  their data in the same manner?
• Wouldnt it be even better if all the documents
  could be stored, or simply expressed, through a
  universal language?

7
Universal Data Storage

• Historically speaking, the first approach to this
  was what today wed call Universal Data Storage.
• Moving data into a new, powerful,
  all-encompassing system
• Full integration, support, unique vendor,
  architectural consistency
• Migration of data, often also migration of code

8
Universal Data Access

• The theory of UDA says that you dont have to
  move the data, but the code to access it.
• A common layer of code will let you access
  literally every data source no matter what the
  storage medium or organization.
• No data migration, uniform programming interface,
  a universal tabular format for any data
• Re-think your code to access data, an
  enterprise-wide strategy to plan the porting of
  the code

9
Don't we already have ODBC?

• Both OLE DB and ODBC are aimed at simplifying
  data access across multiple platforms.
• ODBC mostly manages relational data through SQL
• OLE DB accesses relational and nonrelational data
  not necessarily through SQL
• ODBC copies the resultset into the applications
  memory while OLE DB utilizes an independent
  object.
• You can use disconnected recordsets and work
  without maintaining a connection to the data
  source
• OLE DB follows a model that is less dependent on
  the physical structure of the data.

10
ODBC vs. OLE DB
11
ODBC/OLE DB Architecture

• Its more likely that you have to use or write a
  custom OLE DB provider than a custom ODBC driver.
• ODBC is based on WOSA. OLE DB is based on COM and
  theres no separation between the client and the
  provider
• Writing a OLE DB provider is probably far easier.
• Support for any data that can be expressed in
  terms of rows and columns makes it also quite
  compelling.

12
Key Facts of OLE DB

• OLE DB is a COM-based technology that acts as the
  underpinning of the UDA strategy.
• At the higher level, OLE DB is a proxy between
  applications and data sources.
• It provides a standard interface for the
  applications to issue commands and for the data
  sources to reply.
• Both interfaces are based on COM.

13
Consumer and Provider
Content of a directory Email messages Active
processes Resources on a network
DBMS, Memory, Files, File System, Host Services,
Programs
14
The Role of ADO

• ActiveX Data Objects (ADO) is the infrastructure
  for data access pushed by VS98.
• It is functionally equivalent to DAO and RDO
• Porting your code from DAO or RDO to ADO is
  neither trivial nor painful.
• ADO is an object model built on top of OLE DB
• ADO is the cornerstone of many Microsoft
  products.
• ADO is highly recommendable for new projects.

15
ADO and OLE DB
OLE DB is a relatively new technology that
probably needs some time to consolidate and
become fully supported by all RDBMS vendors.

• You always use ADO in the same manner no matter
  what the dimension of your business.
• What actually does the job (good or bad) is the
  underlying OLE DB provider.
• If you use ADO you can access all sorts of
  heterogeneous data in the same manner from a
  (Active) directory listing to email messages, and
  from Excel tables to regular recordsets.

If you dont specify an explicit OLE DB
provider, then ADO defaults to the less
efficient of the Microsoft supplied providers
MSDASQL
16
ASP, ADO and OLE DB
Example
IIS
17
OLE DB Providers
Microsoft made available providers for
MSDASQL Persistence Data Shaping Index
Server SQL Server 6.5 7.0 Active
Directory Oracle OLAP Jet 3.51 4.0 SNA
Server
18
Accessing Legacy Data
Lots of scanned images forming an image data
bank accessible through a specific API

• There are three types of legacy data
• Data stored on disk using proprietary formats.
• Data for which an OLE DB provider doesnt exist.
• Data resident on host machines, mostly IBM
  mainframes accessed through a Telnet server such
  as Microsoft SNA Server or IBM Communication
  Server.

A Web-based host access manager that manipulates
data using existing procedures instead of direct
calls.
19
OLE DB and Legacy Data

• OLE DB lets you expose custom data in standard
  way.
• You cant integrate this data with other data
  sources without OLE DB
• Proprietary and legacy data don't have a standard
  interface

20
References

• OLE-DB
• MSJ, http//www.microsoft.com/msj/0699
• http//msdn.microsoft.com/data/oledb
• ADO
• Professional ADO RDS with ASP, Wrox Press
• ADO 2.1 Programmers Reference, Wrox Press
• UDA
• MIND, http//www.microsoft.com/mind/0498

21
Q A