Firebird ODBC, Interbase ODBC or OLE DB? Choosing a driver for InterBase and Firebird.

Introduction

I often hear that some people don't understand the differences between ODBC and OLE DB technologies. Sometimes they consider these technologies to be the same. In this very article we'll compare them.

Advantages of standard interfaces use

Both technologies are designed by Microsoft. Their goal is to isolate the client from data source realisation features providing him with standard Access Interface.

There are some pros of such approach:
• independence of client application from data source realisation features.
• easy update of database server versions.
• application connectivity with some database servers.
• development tools' support.

The idea is that it's easier for developers to realise one standard interface than to support multiple databases with different API.

What is an ODBC driver?

ODBC - Open Database Connectivity

ODBC drivers were the Microsoft first attempt to provide a standard of data accessing.

They were the ODBC API realisation with C. ODBC API calls are sent to the definite database API.

ODBC data sources

Windows keeps all the information about data sources and ODBC drivers in its registry. Database connection comes from the registered DSN (Data Source Name), Thus, before using the data source should be registered on user's computer.

It is possible to do it via Data Source Administration (Control Panel-Administration-ODBC Data Sources).

OLE DB is the alternative to ODBC.

OLE DB (или OLEDB) - Object Linking and Embedding, Database. OLE DB technology was created after ODBC and became the Microsoft Corporation idea about standard Access Interfaces.

OLE DB differs from ODBC because the first is object-oriented API based on COM- Interfaces.

OLE DB providers may work not only with relational database but with any other information that can be a table. For example: OLE DB provider for Catalogue Services or Jet OLE DB provider which lets to extract data from Excel and Outlook.

OLE DB data sources

For connecting via OLE DB, the registration of data source in the system is not required (with ODBC it is obligatory). All the information is saved either in udl-files or is written in connection string.

For editing the OLE DB udl-connection Data Link is used. It supports the advanced data provider's settings, including additional pages, about which it is informed by chosen OLE DB provider, into its interface.

To run Data Link, you should create the empty udl-file and run it.

ODBC Escape Sequences

SQL evolution made its realisations in different databases incompatible. ODBC Escape Sequences were offered to solve this problem. They let writing SQL queries, which were compatible with the majority of databases.

ADO and DAO

DAO - Data Access Objects. The technology let working with databases via ODBC drivers, its development ended in 2001.

ADO - ActiveX Data Objects ADO - ActiveX Data Objects It's very popular among Windows applications developers as it has easier high-level interface in comparison with OLE DB.

ADO Components are well projected and seem to be more perfect than the newer technology ADO.NET in many ways. For example, there is no Fetch Backwards property for cursors, no standard way to read and write OLE DB features, no parallel transactions' support in one connection realised.

ADO is supported by:

• Visual C++, Visual Basic in Visual Studio.
• Microsoft Office, Visual Basic for Applications.
• VBcript, JavaScript, WSH
• the rest supporting COM.

ADO and DAO are included in MDAC - Microsoft Data Access Components.

Microsoft Office Integration

Microsoft Office resources support the data loading via either OLE DB and ODBC. Valuable use of all the Office Data Control resources depends on definite OLE DB providers and ODBC drivers' possibilities.

.Net Framework

More and more developers prefer .Net Framework for their new projects. There are certain .Net Providers for OLE DB and ODBC in ADO.Net:

• ODBC .Net Data Provider
• OLE DB .Net Data Provider

Microsoft MS SQL Server

SQL Server lets connecting to other Database servers including Firebird and InterBase from Transact SQL via Linked Server.

Linked Server works only with OLE DB Providers and doesn't support ODBC drivers.

To use ODBC driver you need to connect the additional layer-OLE DB Provider for ODBC drivers and to make the connection like: Firebird/Interbase -> ODBC -> OLEDB ->MS SQL Linked Server. This example is not very effective and now it is the temporary solution.

With the time Microsoft neglects ODBC Support offering OLE DB and ADO.NET Providers in exchange.

Business Intelligence

There are three Business Intelligence Services in SQL Server:

• Integration Services (SSIS) visual component for data integration.
• Analysis Services (SSAS) - OLAP system.
• Reporting Services (SSRS) enables developing Reporting Systems.

They let working with OLE DB and .Net Providers but don't have the valuable ODBC support.

ODBC Firebird Driver, ODBC Interbase Driver or OLE DB Provider?

Firebird/Interbase ODBC drivers

Conclusion

To my mind, the choice of access technology for Firebird and InterBase is evident, but every month the visitors of search systems are looking for magical ODBC Firebird driver and ODBC Interbase driver for making new applications.

Meanwhile the choice of Database Access Provider as the choice of Database itself is the application basis, which influences on efficiency, development rate, and even on the project success.

Additional Information from IBProvider

Useful References