Geeks With Blogs
Random Musing Putting it together one piece at a time...

A recent SSIS project which involved data retrieval from Firebird SQL to SQLServer 2005 prompted me this post on the differences between ODBC and OLEDB. At this time of writing, there's very little information on how SSIS can import information from a Firebird SQL database in the internet, at least too little for me.

Before finally deciding on using OLEDB adaptors to connect to Firebird for direct data import, there were some other considerations, like consuming Webservices with a Firebird .Net Provider or using one of the ODBC drivers. I realises questions on the differences between OLEDB, ODBC and the other adaptors makes very good interview materials. As far as I know the typical everyday developer/DBA doesn't really ask themselves these questions. Maybe I'll use it next time as well.

Database connectivity has come a long way, we have ODBC, JDBS, OLEDB, DAO, RDO, ADO, ADO.Net and what have you. Having evolved due to the different technological requirements, the more popular ones (as far as I'm concerned) are ODBC, OLEDB, ADO and ADO.Net. We all know both ODBC and OLEDB are Microsoft's stuff. Here's a definition of each

  • ODBC is an industry standard and a component of Microsoft® Windows® Open Services
    Architecture (WOSA). The ODBC interface makes it possible for applications to access
    data from a variety of database management systems (DBMSs). ODBC permits
    maximum interoperability—an application can access data in diverse DBMSs through a
    single interface. Furthermore, that application will be independent of any DBMS from
    which it accesses data. Users of the application can add software components called
    drivers, which create an interface between an application and a specific DBMS.
  • OLEDB a strategic system-level programming interface to data across the organization. OLE DB
    is an open specification designed to build on the success of ODBC by providing an open
    standard for accessing all kinds of data.

To make things simpler to understand, when a .Net application retreive data from the database via ADO(.Net), what really happens is that the OLEDB layer gets called, then the ODBC layer via a wrapper, before hitting the database. Does this mean that ODBC connection is faster than OLEDB connection? Or that OLEDB is faster than SqlCient? My answer is no (opinions differ for different people though). The answer is in fact the opposite, SqlClient is faster than OLEDB due to its native library calls, and OLEDB is faster and more scalable than ODBC.

So when should you use which? My suggestion is, if it works, performs well enough, and it fits into your solution architecture, use it. Don't sweat over the small things, your business problem and overall solution is much more important than that.

Posted on Thursday, December 14, 2006 4:04 AM .Net , Business Intelligence , Architecture | Back to top

Comments on this post: OLEDB and ODBC

# re: OLEDB and ODBC
Requesting Gravatar...
Integration Services. Part 1. Import and data processing in Firebird and Interbase. (MS SQL Server 2005 Business Intelligence).
Left by Andreas Markes on Feb 28, 2008 2:36 AM

# re: OLEDB and ODBC
Requesting Gravatar...
Hi Andreas,

Thanks for that!

Left by Nestor on Feb 28, 2008 5:24 AM

Your comment:
 (will show your gravatar)

Copyright © Nestor | Powered by: