Publish date: 2008-01-16
Updated: 2017-07-13

Firebird ODBC, InterBase ODBC or OLE DB? Choosing a database driver for InterBase and Firebird SQL Server

Introduction
Advantages of standard interfaces use
What is an ODBC driver?
ODBC data sources
OLE DB is the alternative to ODBC
ODBC data sources
ODBC Escape Sequences
ADO and DAO
Microsoft Office Integration
.Net Framework
Microsoft MS SQL Server
Business Intelligence
ODBC Firebird, ODBC InterBase or OLE DB
Conclusion
Additional Information from IBProvider
Useful References

Keywords: OLE DB provider, Firebird ODBC driver, InterBase ODBC driver, connect Firebird, InterBase, ADO .Net, ADO, DAO, ibprovider, gemini, easysoft

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.

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.

Information:
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.
  • 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.

There is such a support for Firebird and InterBase users.

.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

The article given here tells how to work with Firebird in .Net via 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

Gemini InterBase/Firebird ODBC Driver — project is not supported since March, 2005. Driver homepage

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.

OLE DB

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.

There are two OLE DB providers in decision complex. Here you may read detailed information about each of them.

The direct link to download IBProvider Professional Edition.

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

You may also subscribe on news and documents’ upgrade on our site. The subscribe form is in the beginning of the article!

Useful References

Author: Andrew A. Merkulov  
Publish date: 2008-01-16. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.
All documentation