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