Keywords: OLE DB provider, Firebird ODBC driver, Interbase ODBC driver, connect Firebird, Interbase, ADO .Net, ADO, DAO, ibprovider, gemini, easysoft
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.
Having Firebird and Interbase databases as the examples, there are some tips that can help choosing the access provider.
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.
Technology's advantage - is the Cross Platform features.
unixODBC development lets ODBC drivers' use on Unix platform.
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.
It is possible to work with ODBC driver with the help of a special provider (OLE DB provider for ODBC drivers)
which can be connected with ODBC data sources. This helped in case of OLE DB provider's absence for the definite database.
Today the support of this driver is cancelled.
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.
There is ODBC Escape Sequences support in many databases.
For Firebird and Interbase the ODBC Driver Escape Sequences
is available via IBProvider - OLE DB Provider for Firebird and Interbase.
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.
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.
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.
There are three Business Intelligence Services in SQL Server:
Integration Services (SSIS) visual component for data integration.
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
Gemini InterBase/Firebird ODBC Driver - project is not supported since March, 2005.
ODBC Easysoft drivers spread separately for Firebird and InterBase:
Easysoft ODBC-InterBase Driver - declared about Interbase 6. IB 6.5 IB 7 and IB 7.5 support.
Easysoft ODBC-Firebird Driver - declared about Firebird 1 and FB 1.5 support.
There is a large list of ODBC drivers but no information about drivers' last update for Firebird and InterBase.
If to look at the numbers of supported versions, the changes were long ago.
To download these drivers on Easysoft site, you should sign up.
IBProvider Professional Edition - supports 19 types of Interbase and Firebird servers,
including InterBase XE7 and Firebird 2.1,
and automatically is set to work with each of them.
It independently defines the keywords, connects optimizing algorithms, set of metadata, SQL and ODBC parser, etc.
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.