Database Connection Methods: difference between ODBC and OLE DB

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, the current ODBC(Open Database Connectivity ), ODBCIs an early product of General APIs. Many databases follow this standard and are called ODBCCompatible database.

Oledb (Object link and Embedded Database) is located in ODBCBetween Layers 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 handed over ODBCProcessing. 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.


Use ODBCConnect to the database:
ODBCThere are 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 ODBCThe file DSN is stored in a text file.
Their creation methods will not be mentioned.
When using them in ASP, the statement is as follows:
A. 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 = {SQL Server}; server = servername; uid = xx; Pwd = xxx"
B. 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 = {Microsoft Access driver}; DBQ = D:/ABC. mdb"

Use oledb to connect to the database:
A. SQL Server:
Connstr = "provider = sqloledb;
Data Source = servername; uid = xx; Pwd = xxx; database = dbname"
B. Access:
Connstr = "provicer = Microsoft. Jet. oledb.4.0;
Data Source = C:/ABC. mdb"
It is worth noting that the ole db pair ODBCAllows ole db to access existing ODBCData source. Its advantages are obvious because ODBCIt is more common than ole db, so we can obtain ODBCThe driver is correspondingly more than the 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 located at the ole db layer, while the driver is located ODBCLayer. If you want to use ODBCData source. ODBCOle db provider, which then uses the corresponding ODBCDriver. If you do not need to use ODBCData source, you can use the corresponding ole db provider, which is usually called the local provider (native provider ).
We can clearly see that ODBCThe provider requires an additional layer. Therefore, when accessing the same data ODBCThe ole db provider may be slower than the local ole db Provider.