none
ODBC or OLEDB

    Question

  • i heard from sqlapi that "preferably to use ODBC because Microsoft says about the migration to ODBC API as default for any new SQLServer versions." and is that true?  
    Friday, July 12, 2013 2:15 AM

Answers

  • It is true that ODBC is preferred over OLE DB for new native (unmanaged) application development.  See the announcement in the Data Access forum (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e696d0ac-f8e2-4b19-8a08-7a357d3d780f/microsoft-is-aligning-with-odbc-for-native-relational-data-access-faq).

    The Data Access Technology Roadmap (http://msdn.microsoft.com/en-us/library/ms810810.aspx) states the SQL Server 2012 Native client is that last to include OLE DB.  The old SQLOLEDB driver has been deprecated for several years.  So OLE DB database will still work but won't be enhanced for new SQL Server features.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Latheesh NKMVP Friday, July 12, 2013 4:23 AM
    • Marked as answer by Lennane Saturday, July 13, 2013 12:22 AM
    Friday, July 12, 2013 2:26 AM
  • But i still wonder if Microsoft-supplied providers and other 3rd parties' OLE DB providers use ODBC driver to do the underlying operations, for ODBC is in a lower level in MDAC architecture?

    Take mysql for instance, we have two ways of accessing data.

    1 installing  mysql ODBC

    2 using  native C api ship with mysql

    I also read that ODBC offering servics by invoking  RDBMS's CLI, it that correct? and if so, does it mean that using ODBC is less faster than using it's native api? ODBC is in purpose to provide a uniform way to access variant databases

    It is a common myth that data access via ODBC is inherently slower than call-level access via a native driver.  An ODBC driver may be a truly native driver that implements an ODBC-compliant call-level interface API so that no middle layer is needed.  This is the case with SQL Server Native Client, which inherently communicates with SQL Server natively using the TDS protocol over a network library. 

    ODBC drivers for other DBMS products may or may not be native drivers.  Some might simply wrap a native driver with ODBC interfaces.  That approach does introduce another layer and the performance implications depend much on the underlying implementation.  These same considerations apply to OLE DB, managed ADO.NET providers, etc.  It is up to the underlying driver/provider implementation as to whether or not an additional layer is used and the resulting performance.

    You are correct that ODBC (or any other abstraction layer) provides uniformity in database access among different DBMS products.  ODBC also provides a level of version independence so that you can use different ODBC driver versions without changing or rebuilding applications. 

    You might also find this article an interesting read from a historical perspective http://msdn.microsoft.com/library/ee730343.aspx.  It pretty much details what I've mentioned in this thread.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Lennane Tuesday, July 16, 2013 7:09 AM
    Saturday, July 13, 2013 3:23 PM

All replies

  • It is true that ODBC is preferred over OLE DB for new native (unmanaged) application development.  See the announcement in the Data Access forum (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e696d0ac-f8e2-4b19-8a08-7a357d3d780f/microsoft-is-aligning-with-odbc-for-native-relational-data-access-faq).

    The Data Access Technology Roadmap (http://msdn.microsoft.com/en-us/library/ms810810.aspx) states the SQL Server 2012 Native client is that last to include OLE DB.  The old SQLOLEDB driver has been deprecated for several years.  So OLE DB database will still work but won't be enhanced for new SQL Server features.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Latheesh NKMVP Friday, July 12, 2013 4:23 AM
    • Marked as answer by Lennane Saturday, July 13, 2013 12:22 AM
    Friday, July 12, 2013 2:26 AM
  • thanks a lot
    Saturday, July 13, 2013 12:22 AM
  • more questions:

    oledb is said to be replace odbc when it first showup, for oledb is a com-based technology. and now is revert,why ,where i can  find  the reasons?

    Microsoft offer provider to SQLServer and related ms's db products. If i want to access other DB like mysql, sqlite,  can use oledb  (i don't write myself a mqsql provider or use 3rd party lib), or oledb use ODBC for the underlaying operations  for me ?

    Saturday, July 13, 2013 1:01 AM
  • I can only speculate as to the rationale other than in the links I mentioned.  ODBC seems to be more widely use and accepted than OLE DB and, unlike OLE DB, runs on non-Windows operating systems too.  ODBC drivers are also more common than OLE DB providers for DBMS products other than SQL Server.  So it makes sense that Microsoft would prefer to align with the majority of the industry rather than maintain redundant relational database access APIs going forward.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 13, 2013 3:07 AM
  • I benefited a  great deal from ur answers and ur blogs on "RIP OLE DB".

    But i still wonder if Microsoft-supplied providers and other 3rd parties' OLE DB providers use ODBC driver to do the underlying operations, for ODBC is in a lower level in MDAC architecture?

    Take mysql for instance, we have two ways of accessing data.

    1 installing  mysql ODBC

    2 using  native C api ship with mysql

    I also read that ODBC offering servics by invoking  RDBMS's CLI, it that correct? and if so, does it mean that using ODBC is less faster than using it's native api? ODBC is in purpose to provide a uniform way to access variant databases

    Saturday, July 13, 2013 9:25 AM
  • But i still wonder if Microsoft-supplied providers and other 3rd parties' OLE DB providers use ODBC driver to do the underlying operations, for ODBC is in a lower level in MDAC architecture?

    Take mysql for instance, we have two ways of accessing data.

    1 installing  mysql ODBC

    2 using  native C api ship with mysql

    I also read that ODBC offering servics by invoking  RDBMS's CLI, it that correct? and if so, does it mean that using ODBC is less faster than using it's native api? ODBC is in purpose to provide a uniform way to access variant databases

    It is a common myth that data access via ODBC is inherently slower than call-level access via a native driver.  An ODBC driver may be a truly native driver that implements an ODBC-compliant call-level interface API so that no middle layer is needed.  This is the case with SQL Server Native Client, which inherently communicates with SQL Server natively using the TDS protocol over a network library. 

    ODBC drivers for other DBMS products may or may not be native drivers.  Some might simply wrap a native driver with ODBC interfaces.  That approach does introduce another layer and the performance implications depend much on the underlying implementation.  These same considerations apply to OLE DB, managed ADO.NET providers, etc.  It is up to the underlying driver/provider implementation as to whether or not an additional layer is used and the resulting performance.

    You are correct that ODBC (or any other abstraction layer) provides uniformity in database access among different DBMS products.  ODBC also provides a level of version independence so that you can use different ODBC driver versions without changing or rebuilding applications. 

    You might also find this article an interesting read from a historical perspective http://msdn.microsoft.com/library/ee730343.aspx.  It pretty much details what I've mentioned in this thread.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Lennane Tuesday, July 16, 2013 7:09 AM
    Saturday, July 13, 2013 3:23 PM
  • It will take me times to fullly assimilate what your had mentioned and articles above. Thanks again.
    Monday, July 15, 2013 6:49 AM
  • Hi Dan, have you ever encount a memory leak problem caused by msado15.dll.(C++)

    I have googled some questions about this problem but find no suitable answers. 

    Some people mentioned that this problem  has been slove in a newer version of msado15.dll and it work for their's, however, not for mine.

    Could you give me some advices on memory leak detections of this problem. by the way that code have connection string with "Provider=SQLOLDED.1" 

    Tks!

     

    Tuesday, July 16, 2013 8:39 AM
  • Could you give me some advices on memory leak detections of this problem. by the way that code have connection string with "Provider=SQLOLDED.1"  

    I assume the provider is "SQLOLEDB.1" rather than "SQLOLDED.1".

    A memory leak may be due to a bug in the application code, data access layer (ADO) or the provider (presumably SQLOLEDB).  Review the ADO bug at http://support.microsoft.com/kb/978155 to see if that applies in your environment. 

    Since SQLOLEDB is unsupported, you might try SQL Server Native Client (SQLNCLI, SQLNCLI10 or SQLNCLI11 depending on SQL version).  If SSNC does not resolve the problem, then the most likely cause is an application bug.  See thread http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cc46f23b-e317-4a08-a999-4acbe6c1d0f6/ado-memory-leak for an example of such a memory leak bug.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, July 17, 2013 12:29 PM
  • How are you, Dan!  I have got a questions while using ODBC and still can figure it out, can you help me out? Ths!

    http://social.technet.microsoft.com/Forums/en-US/5a66e3d9-6034-41a0-97ce-36fbcfd31a8d/how-can-i-go-back-to-the-prior-resluts-set-after-sqlmoreresluts-or-sqlfetchscroll-return-sqlnodata

    Tuesday, August 20, 2013 8:09 AM