Differences between OLEDB and ODBC (advantages and disadvantages)

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 WOSA (Windows OpenServices Architecture). It establishes a set of specifications, it also provides a set of standard APIs for database access (application programming interfaces ). These APIs use SQL to complete most of their tasks. ODBC also provides support for the SQL language. You can directly send SQL statements to ODBC.
It is designed for the purpose that database users do not have to consider which database to use but only need the same operation.

ODBC is the abbreviation of Open Database Connect, which is a unified interface standard proposed by Microsoft in 1991 for accessing databases. It is the middleware between applications and Database systems. It performs database operations by interacting with the driver and application of the required database on the corresponding application platform, avoiding the direct call of database-related operations in the application, this provides database independence.
ODBC mainly consists of the driver and driver manager. A driver is a module used to support ODBC function calls. Each driver corresponds to the corresponding database. When an application is transplanted from one database system to another, you only need to change the alias of the corresponding database system set by the ODBC Administrator in the application. The driver manager can be linked to all ODBC applications and is responsible for managing the binding of ODBC functions to functions in the DLL in the application.
ODBC uses hierarchical methods to manage databases. At each layer of the database communication structure, it may depend on the characteristics of database products, ODBC introduces a public interface to solve potential inconsistency, thus solving the relative independence of database system applications, this is also one of the important reasons for the great success of ODBC since its launch.

In terms of structure, ODBC is divided into single-bundle type and multi-bundle type.

1. Single-bundle driver

A single-bundle driver is between an application and a database, providing a unified data access method like an intermediary driver. When a user performs database operations, the application passes an ODBC function call to the ODBC driver manager, the odbc api determines whether the call is directly processed and whether the result is returned or sent to the driver for execution and the result is returned. As can be seen from the above, a single-bundle driver itself is a database engine that can directly perform database operations, although the database may be located anywhere on the network.

2. Multi-bundle driver

A multi-bundle driver transfers commands and data between the database engine and the customer application. It does not execute data processing but is used for remote operations on a network communication protocol interface. The front-end application sends a request to the database, which is forwarded to the ODBC driver Manager. The driver manager completes or passes the request to multiple bundles of drivers, the multi-bundle driver translates requests into forms that can be understood by the database communication interfaces of specific manufacturers (such as the SQLNet of Oracle) and submits them to interfaces for processing, the interface sends the request to the data engine on the server over the network. After the server completes processing, it sends the result back to the database communication interface. The database interface sends the result to multiple ODBC drivers, then, the driver sends 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 connections were very difficult. the format of each database is different. Developers must have a deep understanding of the underlying APIs of each database they develop. therefore, General APIs that can process a variety of databases have emerged. that is, ODBC (Open Database Connectivity) is an early product of common APIs. many types of databases comply with this standard, known as ODBC-compatible databases.

OLEDB (Object link and Embedded Database) is located between the ODBC layer and applications. on your ASP page, ADO is an "application" located on top of OLEDB ". your ADO call is first sent to OLEDB and then processed by ODBC. you can directly connect to the OLEDB layer. If you do this, you will see the performance improvement of the server-side cursor (the default recordset cursor is also the most commonly used cursor.

Reference http://www.connectionstrings.com/

Use odbc to connect to the database:

Odbc provides three kinds of dsn, the difference is very simple: User dsn can only be used for this user. The difference between the system dsn and the file dsn is that the storage location of the connection information is different: the system dsn is stored in the odbc storage area, and the file dsn is placed in a text file.
Their creation methods will not be mentioned.

When using them in asp, the statement is as follows:

1. SQL server:

System dsn: connstr = "DSN = dsnname; UID = xx; PWD = xxx; DATABASE = dbname"
File dsn: connstr = "FILEDSN = xx; UID = xx; PWD = xxx; DATABASE = dbname"
You can also use a connection string (so that you do not need to create a dsn ):
Connstr = "DRIVER = {SQLSERVER}; SERVER = servername; UID = xx; PWD = xxx"

2. access:

System dsn: connstr = "DSN = dsnname"
(Or: connstr = "DSN = dsnname; UID = xx; PWD = xxx ")
File dsn: connstr = "FILEDSN = xx"
You can also use a connection string (so that you do not need to create a dsn ):
Connstr = "DRIVER = {MicrosoftAccess Driver}; DBQ = d: \ abc. mdb"

Use oledb to connect to the database:

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. mdb"

It is worth noting that ole db is compatible with ODBC and allows ole db to access existing ODBC data sources. The advantage is obvious, because ODBC is more common than ole db, the ODBC driver can be obtained correspondingly more than ole db. In this way, you do not have to get the ole db driver to immediately access the original data system.
The provider is at the ole db layer, while the driver is at the ODBC layer. If you want to use an ODBC data source, you need to use the ole db provider for ODBC, which then uses the corresponding ODBC driver. If you do not need an ODBC data source, you can use the corresponding ole db provider, which is usually called the local provider ).
It can be clearly seen that the use of ODBC providers requires an additional layer. Therefore, when accessing the same data, the ole db provider for ODBC may be slower than the local OLEDB provider.

ODBC and OLEDB connection strings

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 SQLServer 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 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 SQLServer 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 Data Source is the port to use (1433is the default ))