The difference between OLE DB and ODBC (pros and cons) _ Database Other

ODBC is an open standard for connecting to databases.

ODBC (OpenDatabase connectivity, open Database interconnection) is an integral part of the database in Microsoft's Open service Architecture (wosa,windows openservices Architecture), which 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 SQL languages, and users can send SQL statements directly to ODBC.
It is in itself designed to be the user of a database that does not have to consider which database to use, but only the same operations.

ODBC is the abbreviation for open database Connect, which is the Open-source interconnect, a unified interface standard for accessing databases, presented by Microsoft in 1991, and middleware between applications and database systems. It provides database independence by using the interaction of the driver and the application on the corresponding application platform and the corresponding database to avoid the direct call to database operation in the application.
ODBC consists primarily of drivers and driver managers. The driver is a module that supports ODBC function calls, each of which corresponds to the corresponding database, and when the application is ported from one database system to another, you simply change the alias that is set by the ODBC Administrator program in the application that corresponds to the corresponding database system. The driver Manager can link to all ODBC applications and is responsible for managing the binding of the ODBC functions in the application to functions in the DLL.
ODBC uses a hierarchical approach to managing databases, and at every level of the database communication structure, ODBC introduces a common interface to address potential inconsistencies in the areas where the database product itself may be dependent, which is a good solution to the relative independence of the database system application, which is also an ODBC One of the important reasons for great success once it is launched.

from the structural point of view, ODBC is divided into two types: single beam and multiple beam type.

1. Single-beam driver

The Tan driver is between the application and the database and provides a uniform way of accessing data, like a mediation driver. When a user performs a database operation, the application passes an ODBC function call to the ODBC Driver Manager, which is judged by the ODBC API to handle directly by it and return the result to the driver and return the result. Visible from the top, the single beam driver itself is a database engine that directly completes operations on the database, although the database may be located anywhere on the network.

2. Multi-beam driver

A multiple-beam driver is responsible for transferring 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 to the network communication protocol for remote operations. The front-end application presents a request to the database processing, which is forwarded to the ODBC Driver manager, where the driver manager completes or passes to the multiple drivers in place, depending on the request, and the multi-beam driver translates the request to a specific manufacturer's database communication interface, such as Oracle Sqlnet) can be understood in a form that is delivered to the interface to deal with, the interface sends the request over the network to the data engine on the server, the server processes the result back to the database communication interface, the database interface passes the result to the multi-beam ODBC driver, and the driver passes the result to the application.

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

OLE DB (object link and embedded database) is located between the ODBC layer and the application. In your ASP page, ADO is an "application" located above OLE DB. Your ADO call is sent to OLE DB before it is 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 cursors of the recordset and the most commonly used cursors).

Reference http://www.connectionstrings.com/

To connect to a database with ODBC:

There are three types of DSNs available in ODBC, and the difference is simple: a user DSN can only be used for this user. The difference between a System DSN and a file DSN is only 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 are written:

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 create 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 ")
Dsn:connstr= "Filedsn=xx" with file
You can also use a connection string (thereby eliminating the need to create a DSN):
Connstr= "Driver={microsoftaccess DRIVER};D Bq=d:\abc\abc.mdb"

To connect to a 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 noteworthy that OLE DB compatibility with ODBC allows OLE DB to access existing ODBC data sources. The advantages are obvious because ODBC is more commonly used relative to OLE DB, so you can get more ODBC drivers than OLE DB. This does not necessarily mean that the driver for the OLE DB can be accessed without immediate access to the original data system.
The provider is located at the OLE DB layer, and the driver is at the ODBC layer. If you want to use an ODBC data source, you need to use an OLE DB Provider for ODBC, and it 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 providers, which are often referred to as local providers (native provider).
It is clear that using an ODBC provider means that an additional layer is required. Therefore, when accessing the same data, the OLE DB Provider for ODBC may be slower than the local DB provider.

Odbc,oledb Connection String detailed

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 a 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 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 "the" 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 same result)
2. Trusted Connection:
"Datasource=aron1;initial catalog=pubs;integrated Security=sspi;"
-OR-
"Server=aron1;database=pubs; Trusted_connection=true; "
(Bothconnection strings produces same result)
(Useservername\instancename as Data Source to use a Specifik SQL Server instance,only SQLServer2000)
3. Connect via 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 "the" the ' Data Source is the ' port to use (1433is the default))