Difference between OLE DB Connection and ODBC Connection
-
hi
i ve always been confused abt the basic difference between ODBC and OLE DB Connection.
Can an application have OLE DB Connection?
is ODBC used only for RDBMS.?
Please Clarify...
-
the difference as I know is:
1-OLE Db is a standard .. ODBC is vendor specific (Microsoft access must have an odbc made by microsoft)
2- OLE DB is more faster than ODBC ...
of course you can make any connection by using any of them
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Microsoft has a set of Technologies to access Relational (i.e. SQL Server data, Oracle data, DB2 data), non-relational data (i.e. Text data, images, etc), and multi-dimensional data (OLAP cubes). Some of these technologies are being enhanced and others are being phased out and eventually they will be excluded from future releases in its products. Following is a list of data access technologies used by Microsoft:
- Microsoft Data Access Components (MDAC) which includes Open Database Conectivity (ODBC), Active x Data Objects (ADO), and OLE DB, ADO Multi-Dimensional (ADOMD), ADO Extensions for DDL and Security (ADOX),
- DB Library
- ESQL
- DAO
- ADO.NET
Microsoft ADO, OL DB, and ODBC MDAC Components.
Developers can use any of MDAC’s components (ODBC, OLE DB, and ADO) to connect to several relational and non-relational data stores. Providers and drivers (built and shipped by Microsoft or developed by third parties) are available to access data using MDAC components.
Depending on requirements developers may select ODBC, OLE DB, or ADO to connect and access data stores.
· ADO: ActiveX Data Objects (ADO) provides a high-level programming model. ADO can be used from script languages such as Microsoft Visual Basic Scripting Edition (VBScript) or Microsoft JScript.
· OLE DB: OLE DB is a set of COM interfaces for accessing data in databases, file systems, message stores, directory services, workflow, and document stores. Microsoft OLE DB Provider for SQL Server (SQLOLEDB) supports access to Microsoft SQL.
· ODBC: The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that allows applications to access data from a variety of Database Management Systems (DBMS). Applications using this API are limited to accessing relational data sources only. Microsoft SQL Server ODBC Driver (SQLODBC) enables access to Microsoft SQL Server.
For detail information, review the MDAC Reference Manual.
-
I hope this will be a simpler explaination...
OLE-DB connection libraries are the compiled "native" dlls that allow any application to connect to the database they are desiged to communicate with. They are, by design, supposed to be the fastest communcations interface for other programs to use. To keep them fast, programmers of OLE-DB drivers have to keep any error handling overhead down to a minimum. Quite often, that means that any errors generated during the processing may not give you exactly accurate descriptions of the error that occured. Also, OLE-DB must be implemented specifically according to specs. The specs can vary dramatically depending on the vendor and the product.
ODBC, on the other hand, is a wrapper class for OLE-DB libraries. What Microsoft tried to do with ODBC is come up with a standard platform for all applications to communicate to databases. This standardization approach would use each OLE-DB library but allow for a common connection interface and protocol as well as a "one stop shopping" approach to database connectivity. ODBC also has a higher level of error handling that prevents the user from using a OLE-DB library incorrectly. This way the implementation would reduce troubleshooting issues for the developers and users. Because ODBC has this error handling and user interface overhead and because it is a "middle man" to the OLE-DB libraries, speed of communications is dramatically hindered.
I have seen an application take 20 seconds to generate a large and rather complex report from SQL Server using ODBC. After correctly implementing an OLE-DB connection for SQL Server (SQLOLEDB) within the application, the same report took less than 2 seconds.
That should give you an idea of the difference between using OLE-DB libraries instead of ODBC for db connectivity.
"I will not be taken alive!" - S. Hussein -
if I am making an application what is the best to use OLE DB or ODBC (to connect to access or SQL Server)?
which one is used with DSN ? .. because I heared that the best thing is to make DSN Connected to your DB .. and make any connection in your code connected via this DSN
Alamir Mohamed
Alamir_mohamed@yahoo.com -
thanx for all the info.....
another doubt that i have is using an ADOQuery component say in Delphi, when we provide the connection string for Datbase chosing the provider as OLE DB provider for SQL Server are we actually making an OLE DB connection.
What i ve heard is ADO always makes an ODBC connection by default....im not sure if this is correct......
Please clarify this.
-
In all the applications I design, I plan to always use OLE-DB. In applications that may be implemented in other network environments, I have to design the application to have user interfaces at some level in application that will allow the customer to define the database connection information (database server name, custom ports if any, custom login info, etc). This means a bit more programming and of course that means more time which equates to money. If the application is supposed to work with other database systems other than SQL Server, even more work will be involved to account for the db systems that are to be supported.
If you have a limited timeline or budget, then an ODBC connection might be a quick solution. Because it has it's own interface for the users to set the connection information, all you have to do is program your application to utilize the DSN you specify. This is also handy if you are designing an application that is supposed to work with other database systems other than SQL Server.
ADO does not, in my opinion, utilize ODBC by default. But it does seem to interface more simply to it. This is more of the nature of ODBC as opposed to OLE-DB. As I said in the previous post, ODBC is designed to simplify the connection process, thus it requires less parameters to utilize it. OLE-DB requires more specific information to utilize it and as such, the ADO coding seems a bit more complex.
As for the Delphi component, I am not fully aware of it's implementation, but if it is a wrapper component for the ADO libraries, then it is a matter of making sure you specify the use of an OLE-DB connection in it's instantiation.
If you are programming in VB, ASP, or VBS you may like to check out this little website that has great examples of using ADO for OLE-DB and ODBC connections: http://www.able-consulting.com/ADO_Conn.htm
Again, I hope this helps. 🙂
"I will not be taken alive!" - S. Hussein -
Thanx a lot for all the info... ill chk the site n get back .....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply