Menu
What is the difference between OLEDB and ODBC? When do I use which and how do I know what I am looking at is a OLEDB driver v/s an ODBC driver?
589
ODBC is constrained to relational data stores; OLE DB supports all forms of data stores (relational, hierarchical, etc) In general, OLE DB provides a richer and more flexible interface for data access because it is not tightly bound to a command syntax (like SQL in the case of ODBC).
You connect to various data sources through OLEDB providers. So, there's an OLEDB provider for SQL Server, Oracle, MS Access, and others. OLEDB started when ODBC was around version 3.0. So, this makes OLEDB the younger contender.
First of all, it is good to know that ADO.NET used OLE DB providers to access data while OLE DB uses ODBC to access relational databases.
Firstly, with OLE DB, your connection string along with credentials stays with your QlikView document, whereas with ODBC you will have to set up a User/System DSN(in ODBC Administrator Control Panel Applet) and then reference this DSN in the QlikView load script.
OLEDB and ODBC are two different database API's. ODBC is an older standard and is actually not specific to windows - you can get Unix-based ODBC libraries. OLEDB is a COM-based API for database connections.
There is a driver for wrapping ODBC with an OLEDB front-end if the database you are using does not come with a native OLEDB driver. IIRC DB2/400 and Sybase OLEDB drivers (for example) use this method.
The ODBC administrator in Windows is only concerned with ODBC drivers. Both ODBC and OLEDB can use connection strings, which have a slightly different format. You can tell from the connection string - www.connectionstrings.com has samples of connection strings for many different database drivers.
Edit: Oracle has a driver technology for every day of the week.
Oracle have a native OLEDB driver called 'Oracle provider for OLEDB' or some such. If you're using ADO (non-.Net, which sits over OLEDB) this would be the preferred driver.
They also have an ODBC driver that would be (for example) useful for extracting from an Oracle database into MS-Access or with an application or system that does not support OLEDB. For example, older Delphi/Oracle or Powerbuilder/Oracle apps will probably use this driver.
Oracle also has an Oracle-specific interface called OCI. This is the fastest interface if you're writing in C and will also work on non-windows plaforms but ties your application to Oracle. Dynamic language bindings such as cx_Oracle for Python tend to be wrappers around OCI.
If you're using .Net you probably want ODP.Net rather than using the .Net provide for OLEDB. This is the standard .Net interface library provided by Oracle.
There are several JDBC drivers for Oracle. There is a type-2 driver which is a wrapper for OCI and a type-4 driver which is written natively in Java and communicates directly over the network to the server. If you're using Java the type-4 driver is probably the best for most applications (you don't have to install a full Oracle client) unless you have a particular reason to need the type-2 driver.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
