+ODBC and OLE DB

 

ODBC

ODBC is designed to work with numerous database management systems. ODBC provides a consistent application programming interface (API) that works with different databases through the services of a database-specific driver.

A consistent API means that the functions a program calls to make a connection, execute a command, and retrieve results are identical whether the program is talking to Oracle or SQL Server.

ODBC also defines a standardized call-level interface and uses standard escape sequences to specify SQL functions that perform common tasks but have different syntax in different databases. The ODBC drivers can automatically convert this ODBC syntax to either Oracle-native or SQL Server–native SQL syntax without requiring the revision of any program code. In some situations, the best approach is to write one program and allow ODBC to perform the conversion process at run time.

ODBC by itself does not provide complete database independence, full functionality, and high performance from all databases. Different databases and third-party vendors offer varying levels of ODBC support. Some drivers implement only core API functions mapped on top of other interface libraries. Other drivers, such as the SQL Server ODBC driver, offer full Level 2 support in a native, high-performance driver.

If a program uses only the core ODBC API, it will likely forego features and performance capabilities with some databases. Furthermore, not all native SQL extensions can be represented in ODBC escape sequences (such as Oracle DECODE and SQL Server CASE expressions).

Additionally, it is common practice to write SQL statements to take advantage of the database's optimizer. The techniques and methods that enhance performance within Oracle are not necessarily optimal within SQL Server 2000. The ODBC interface cannot translate techniques from one RDBMS to another.

ODBC does not prevent an application from using database-specific features and tuning for performance, but the application needs some database-specific sections of code. ODBC makes it easy to keep the program structure and the majority of the program code consistent across multiple databases.

 

OLE DB

SQL Server 2000 takes advantage of OLE DB within the components of SQL Server itself. Additionally, application developers should consider OLE DB for new development with SQL Server 2000. Microsoft includes an OLE DB provider for Oracle 8 with SQL Server 2000.

OLE DB is an open specification designed to build on the features of ODBC. ODBC was created to access relational databases, and OLE DB is designed to access relational and nonrelational information sources, such as mainframe ISAM/VSAM and hierarchical databases, e-mail and file system stores, text, graphical and geographical data, and custom business objects.

OLE DB defines a collection of COM interfaces that encapsulate various database management system services and allows the creation of software components that implement such services. OLE DB components consist of data providers (that contain and expose data), data consumers (that use data), and service components (that process and transport data, for example, query processors and cursor engines).

OLE DB interfaces are designed to help components integrate smoothly so that OLE DB component vendors can bring high quality OLE DB components to the market quickly. In addition, OLE DB includes a bridge to ODBC to allow continued support for the broad range of ODBC relational database drivers available today.