Chapter 11 (ODBC, OLE DB, ADO)

Database Standards

Consider all of the database products on the market:

  • Access, dBase, FileMaker on the desktop
  • Oracle, SQL Server, MySQL, DB2, Sybase, and others on the server

The problem that the developer has is trying to learn enough about all of these products to be able to use them.

Wouldn't it be nice for developers if there was only a single database management system? Then all you would have to learn is the details of how one database worked and you would be able to use it for all of your applications.

Well, we don't have a single database program, but we do have the next-best thing(s): ODBC, OLE DB, and ADO.NET.

ODBC

ODBC:Open DataBase Connectivity: a standard interface by which application programs can access and process SQL databases in a DBMS-Independent manner.

If a developer needs to access and integrate data from many different sources, he must learn the API (application programming interface) of each data source.

The life of the developer would be made significantly easier if all he had to do was learn one interface. Like this:

Application <---> ODBC <---> Database Server <---> Relational database

The client computer, in addition to the application program, has a driver program that accepts database requests from the application program and converts them into a format that is acceptable to the database (called the data source). If the client is to connect to several data sources, there must be one driver for each data source, and a driver manager that will pass the request to the appropriate driver.

Driver: a program that acts as an interface between a program and either (1) another program, or (2) data.

ODBC Drivers are supplied by the database vendors.

An ODBC driver is responsible for taking ODBC-compliant commands and seeing to it that they execute correctly.

Conformance Levels

To make it easy for companies to comply with the ODBC standard, and make the standard powerful at the same time, the creators of ODBC created three levels of conformance. There are 3 levels for ODBC and 3 levels for SQL.

Figure 11-5, Summary of ODBC Conformance Levels

Core API

  • Connect to data sources
  • Prepare and Execute SQL statements
  • Retrieve data from a result set
  • Commit or roll back transactions
  • Retrieve error information

Level 1 API

  • All of Core API, plus:
  • Connect to data sources with driver-specific information
  • Send and receive partial results
  • Retrieve information about driver options, capabilities, and functions

Level 2 API

  • All of Core and Level 1 API, plus:
  • Browse possible connections and data sources
  • Retrieve native form of SQL
  • Call a translation library
  • Process a scrollable cursor

Figure 11-6, Summary of ODBC SQL Conformance Levels

Minimum SQL Grammar

  • CREATE TABLE, DROP TABLE
  • Simple SELECT (does not include sub-queries)
  • INSERT, UPDATE, DELETE
  • Simple expressions (A>B + C)
  • CHAR, VARCHAR, LONGVARCHAR data types

Core SQL Grammar

  • Minimum SQL grammar
  • ALTER TABLE, CREATE INDEX, DROP INDEX
  • CREATE VIEW, DROP VIEW
  • GRANT, REVOKE
  • Full SELECT (includes sub-queries)
  • Aggregate functions such as SUM, COUNT, MAX, MIN, AVG
  • DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL, FLOAT, DOUBLE PRECISION data types

Extended SQL Grammar

  • Core SQL Grammar
  • Outer joins
  • UPDATE and DELETE using cursor positions
  • Scalar functions such as SUBSTRING, ABS
  • Literals for date, time, and timestamp
  • Batch SQL statements
  • Stored procedures

Establishing an ODBC Data Source Name

A data source is an ODBC data structure that identifies a database and the DBMS that processes it.

There are 3 types of ODBC data sources:

File data source.

A file that can be shared among database users.

System data source.

A data source that is local to a single computer. Anybody with appropriate privileges on the given computer can access the data source.

User data source.

A data source that is only available to the user who created it.

OLE DB

OLE DB: Object Linking and Embedding (Database) is a Microsoft standard that (1) provides an object interface to database data and (2) allows vendors to implement parts of the standard (rather than the whole standard, which was the case with ODBC).

OLE DB was developed (after ODBC) to allow the same consistent interface to non-relational data sources.

Application <-> OLE DB <-> ODBC <-> DB Server <-> Relational database

<-> OLE DB <-> Non-relational databases, ISAM, Email, etc.

Goals of OLE DB

  • Create object interfaces for DBMS functionality
  • Increase flexibility
  • Create an object interface to any type of data:
  • Relational databases
  • ODBC
  • VSAM
  • Email
  • Other
  • Do not force data to be converted or moved from where they are

ADO

ADO (ActiveX Data Objects): A simplified Microsoft standard that provides an object interface to database data.

ADO was developed as a more recent improvement to simplify access to data sources. Microsoft says that ADO will replace all other methods of data access.

ADO uses the following object model:

Connection

|------RecordSet

||------Fields Collection

||------Field

|------Command

||------Parameters Collection

||------Parameter

|------Errors Collection

||------Error

10/27/2018Database StandardsPage 1 of 9