| DCOM Explained by Rosemary Rock-Evans Digital Press ISBN: 1555582168 Pub Date: 09/01/98

Previous | Table of Contents | Next

### Microsoft and Database Connectivity

So, all of this lengthy introduction to the world of database connectivity eventually brings us to Microsoft and how it provides access to stored “persistent” data. There are in fact two methods which Microsoft now supports to access data—ODBC and OLE DB.

Microsoft’s standard answer to the approach of database connectivity is ODBC (Open Database Connectivity)—a de facto standard that provides a standard SQL dialect with which to access files and databases. OLE DB is for use with DCOM and provides an interface and method-based approach to accessing data. OLE DB provides a means for the developer to access data in all sorts of different file types and DBMSs from a component possibly written in an object-oriented language.

Whereas ODBC is “procedurally” constructed with commands that are based on functions—READ, SELECT, etc.—OLE DB is interface based and includes a set of interfaces and methods which can be invoked from components. Let us first examine ODBC; then we can look at OLE DB and how it works.

ODBC (Open Database Connectivity)

Microsoft’s standard answer to the approach of database connectivity is ODBC (Open Database Connectivity)—a de facto standard that provides a standard SQL dialect that can be used to access many Relational DBMSs. Microsoft, together with many third-party vendors, have produced “drivers” (translation products), which converted the ODBC dialect to other dialects of SQL.

Some specialized vendors such as Information Builders (with EDA/SQL) and Platinum Software (with InfoHub) can convert from ODBC to the DML of other types of DBMSs. ODBC is based on the Call Level Interface (CLI) specification of the SQL Access Group (SAG). The importance of the Call Level Interface as opposed to the use of an Embedded API is that in general terms, the SQL is not static SQL (compiled and bound to the program prior to execution), but dynamic SQL—processed during program execution.

ODBC comes in two parts—the core functionality and the extended SQL functions. The core grammar and functions are based on the SAG CLI, and these functions enable a developer to:

  • Establish a connection with a data source, execute SQL statements, and retrieve results
  • Receive standard error messages
  • Provide a standard logon interface to the end user
  • Use a standard set of data types
  • Use a standard SQL grammar defined by ODBC

The extended SQL grammar provides developers with a way of exploiting the more advanced capabilities of DBMSs, those that are not covered in the SAG CLI. The extended set of functions is split into Level 1 and Level 2.

The tables overleaf list some of the commands which are core, level 1, and level 2.

The extensions include support for:

  • Data types such as date, time timestamp, and binary
  • Scrollable cursors
  • A standard SQL grammar for scalar functions, outer joins, and procedures
  • Asynchronous execution
  • A standard way for programs to find out what capabilities a driver and data source provide

ODBC can also support the use of DBMS specific SQL grammar (native code), allowing applications to add “pass thru” code when necessary.

Table 15.1 Core and Level 1 functions
Level of function | Type of function | Function/command
--- | --- | ---
Core | Connecting to a data source | SQLAlocEnv
|  | SQLAllocConnect
|  | SQLConnect
| Preparing SQL requests | SQLAllocStmt
|  | SQLPrepare
|  | SQLSetParam
|  | SQLGetCursorName
|  | SQLSetCursorName
| Submitting requests | SQLExecute
|  | SQLExecDirect
| Retrieving results and info about results | SQLRowCount
|  | SQLNumResultCols
|  | SQLDescribeCol
|  | SQLColAttributes
|  | SQLBindCol
|  | SQLFetch
|  | SQLError
| Terminating a statement | SQLFreeStmt
|  | SQLCancel
|  | SQLTransact
| Terminating a connection | SQLDisconnect
|  | SQLFreeConnect
|  | SQLFreeEnv
Level 1 | Connecting to a data source | SQLDriverConnect
| Obtain info about a driver and data source | SQLGetInfo
|  | SQLGetFunctions
|  | SQLGetTypeInfo
| Setting and retrieving driver options | SQLSetConnectOption
|  | SQLGetConnectOption
|  | SQLSetStmtOption
|  | SQLGetStmtOption
| Submitting requests | SQLParamData
|  | SQLPutData
| Retrieving results and info about results | SQLGetData
| Obtaining information from System Catalog tables | SQLColumns
|  | SQLSpecialColumns
|  | SQLStatistics
|  | SQLTables

The architecture of the ODBC software is shown in Figure 15.2. The client application communicates with the ODBC Driver Manager, a Microsoft supplied product, which establishes which driver to use and routes the instructions to the correct driver. Client applications use the Application Programming Interface.

The driver translates the SQL calls into the syntax expected by the target DBMS and routes the call to the DBMS. The DBMS sends the results of the call to the driver, which are in turn sent to the application. The driver handles connection to and disconnection from the DBMS.

Microsoft supplied their own ODBC Desktop Driver pack for communicating with Access, FoxPro, Excel, Btrieve, Dbase, Paradox, and formatted text. A very large number (hundreds) of third parties also supply drivers, and these suppliers can be specialized middleware suppliers or the DBMS vendors may also provide drivers for their own products. Drivers are generally supplied by third parties such as DLLs. The Driver Manager communicates with the Drivers using a different interface—the Service Providers Interface (SPI).

Previous | Table of Contents | Next