The difference between OLE DB and ODBC (pros and cons)

ODBC is an open standard for connecting databases, and OLE DB (Object linking and Embedding databases) is located between the ODBC layer and the application. In your ASP page, ADO is an application that is located above OLE DB. Your ADO call is sent first to OLE DB, and then to ODBC processing ODBC is an open standard for connecting to a database.
ODBC (OpenDatabase Connectivity, open database interconnect) is an integral part of the database in Microsoft's Open Services Architecture (Wosa,windows openservices Architecture), It establishes a set of specifications and provides a set of standard APIs (application programming interfaces) for database access. These APIs use SQL to accomplish most of their tasks. ODBC itself also provides support for the SQL language, which allows users to send SQL statements directly to ODBC.

It itself is designed to be a database user who does not have to consider which database to use but only the same operation.

ODBC is the abbreviation for open database Connect, a common interface between the application and database systems, presented by Microsoft Corporation in 1991 as a standard for accessing databases. It provides database independence by using the corresponding driver-to-application interaction on the appropriate application platform and the required database to operate on the database, avoiding the need to invoke database-related operations directly in the application.
ODBC is primarily composed of drivers and driver managers. A driver is a module that supports ODBC function calls, and each driver corresponds to the corresponding database, and when an application is migrated from one database system to another, simply change the alias of the application that is set by the ODBC Administrator to the corresponding database system. The driver manager is linked to all ODBC applications and is responsible for managing the binding of ODBC functions in the application to functions in the DLL.
ODBC uses a hierarchical approach to manage the database, at each level of the database communication structure, and in places where it is possible to rely on the characteristics of the database product, ODBC introduces a common interface to address potential inconsistencies, which is a good solution to the relative independence of database-based system applications, which is also an ODBC One of the important reasons for great success once it is launched.

from the structure, the ODBC is divided into two types: single bundle and multi-bundle.

1. Single-beam driver

The beam driver is between the application and the database and provides a uniform way of accessing data, like a mediation driver. When a user makes a database operation, the application passes an ODBC function call to the ODBC Driver Manager, which is determined by the ODBC API that it processes directly and returns the result to the driver execution and returns the result. As can be seen from the above, the single-bundle driver itself is a database engine that directly completes the operation of the database, although the database may be anywhere on the network.

2. Multi-beam driver

The multi-beam driver is responsible for the transfer of commands and data between the database engine and the client application, which itself does not perform data processing operations and is used as an interface for remote operation of the network communication protocol. The front-end application presents a request for database processing, which is forwarded to the ODBC Driver Manager, which is completed in-place or passed to the multi-bundle driver according to the request, and the multi-beam driver translates the request into a specific manufacturer's database communication interface (such as Oracle Sqlnet) can be understood in the form of the interface to deal with, the interface to send the request over the network to the server data engine, the server after processing the results sent back to the database communication interface, the database interface to the results of the multi-beam ODBC driver, and then by the driver to pass the results to the application.

ODBC (Open Database Interconnect): An early Database interface technology introduced by Microsoft. It is actually the predecessor of ADO. Early database connections are very difficult. Each database has a different format, and developers have a deep understanding of the underlying API for each database they develop. As a result, generic APIs that can handle a wide variety of databases have emerged. Now ODBC (Open Database Connectivity), ODBC is an early product of people creating generic APIs. There are many types of databases that comply with this standard, known as ODBC-compliant databases.

OLE DB (Object linking and Embedding database) is located between the ODBC layer and the application. In your ASP page, ADO is an "application" on top of OLE DB. Your ADO call is sent to OLE DB before being processed by ODBC. You can connect directly to the OLE DB layer, and if you do, you'll see a performance boost for server-side cursors (the default cursor for the recordset and the most common cursor).

Reference http://www.connectionstrings.com/

To connect to a database with ODBC:

There are three types of DSNs available in ODBC, the difference is simple: the user DSN can only be used for this user. The difference between a System DSN and a file DSN is that the connection information is stored in a different location: The System DSN is stored in an ODBC storage area, and the file DSN is placed in a text file.
The way they are created is not said.

When using them in ASP, the following is the notation:

1.sql Server:

Using the system dsn:connstr= "DSN=DSNNAME; Uid=xx; Pwd=xxx;database=dbname "
Use the document Dsn:connstr= "FILEDSN=XX; Uid=xx; Pwd=xxx;database=dbname "
You can also use a connection string (thereby eliminating the need to establish a DSN):
Connstr= "Driver={sqlserver}; Server=servername; Uid=xx; Pwd=xxx "

2.access:

Using the system dsn:connstr= "Dsn=dsnname"
(or for: connstr= "dsn=dsnname; Uid=xx; Pwd=xxx ")
Use file dsn:connstr= "Filedsn=xx"
You can also use a connection string (thereby eliminating the need to establish a DSN):
Connstr= "Driver={microsoftaccess DRIVER};D Bq=d:\abc\abc.mdb"

connect to the database with OLE DB:

1.sql Server:
Connstr= "PROVIDER=SQLOLEDB;
Datasource=servername; Uid=xx; Pwd=xxx;database=dbname "

2.access:
Connstr= "Provicer=microsoft. JET. oledb.4.0;
Datasource=c:\abc\abc.mdb "

It is worth noting that OLE DB's compatibility with ODBC allows OLE DB to access existing ODBC data sources. The advantage is obvious, because ODBC is more common than OLE DB, so you can get an ODBC driver that is more appropriate than OLE DB. This does not necessarily mean that the OLE DB driver will be able to access the existing data system immediately.
The provider is located at the OLE DB layer, and the driver is located in the ODBC layer. If you want to use an ODBC data source, you need to use an OLE DB Provider for ODBC, which will then use the appropriate ODBC driver. If you do not need to use an ODBC data source, you can use the appropriate OLE DB provider, which is commonly referred to as the local provider (native provider).
It is clear that using an ODBC provider means an additional layer is required. Therefore, when accessing the same data, an OLE DB Provider for ODBC may be slower than the local DB provider.

Odbc,oledb Connection string

SQL Server

U ODBC
1. Standard Security:
"Driver={sqlserver}; Server=aron1;database=pubs; Uid=sa; PWD=ASDASD; "
2. Trusted Connection:
"Driver={sqlserver}; Server=aron1;database=pubs; Trusted_connection=yes; "
3. Prompt for username and password:
Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "Driver={sql Server}; Server=aron1;database=pubs; "

u OLE DB, OleDbConnection (. NET)

1. Standard Security:
"Provider=sqloledb;datasource=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
2. Trusted Connection:
"Provider=sqloledb;datasource=aron1;initial catalog=pubs;integrated Security=sspi;"
(Useservername\instancename as Data Source to use an Specifik SQL Server instance,only SQLServer2000)
3. Prompt for username and password:
Oconn.provider= "SQLOLEDB"
Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "Data source=aron1;initial catalog=pubs;"
4. Connect via an IP address:
"PROVIDER=SQLOLEDB;DATASOURCE=190.190.200.100,1433; Network library=dbmssocn;initial catalog=pubs; Userid=sa; PASSWORD=ASDASD; "
(Dbmssocn=tcp/ipinstead of Named Pipes, at the end of the that the Data Source is the port to use (1433is the default))

u SqlConnection (. NET)

1. Standard Security:
"Datasource=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
-OR-
"Server=aron1;database=pubs; Userid=sa; PASSWORD=ASDASD; Trusted_connection=false "
(Bothconnection strings produces the same result)
2. Trusted Connection:
"Datasource=aron1;initial catalog=pubs;integrated Security=sspi;"
-OR-
"Server=aron1;database=pubs; Trusted_connection=true; "
(Bothconnection strings produces the same result)
(Useservername\instancename as Data Source to use an Specifik SQL Server instance,only SQLServer2000)
3. Connect via an IP address:
"DATASOURCE=190.190.200.100,1433; Network library=dbmssocn;initial catalog=pubs; Userid=sa; PASSWORD=ASDASD; "
(Dbmssocn=tcp/ipinstead of Named Pipes, at the end of the that the Data Source is the port to use (1433is the default))

The difference between OLE DB and ODBC (pros and cons)