Difference between ODBC and oledb

1. Relationship between ODBC, oledb, ADO, and ADOX:
ODBC: a standard for Database Communication
Oledb: Ms intends to use it to replace ODBC in an object-oriented manner.
Oledb is divided into two types: Direct oledb and ODBC-oriented oledb. The latter architecture is on ODBC.
The characteristics of oledb can also be used for databases without their own oledb providers.
ADO: it is actually an application-level interface. It uses oledb to communicate with databases.
ADOX: extends the Security and maintainability of ADO (for example, creating a database.

2. 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:
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"

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

4. Use the udl file:
A udl file is a text file used to store database connection information, which is a bit like a file DSN. However, udl is for oledb (direct and ODBC-oriented.
Udl creation method:
Right-click desktop or resource manager-> New-> Microsoft Data Connection
The setting should be clear.
Udl usage:
Connstr = "file name = E:/ABC. udl"

 

 

 

 

 

 

Performance Comparison of oledb

There are many ways to connect to a database. You can use system DSN, DSN-less connection or local oledb provider. oledb? What is this? Many of you have never heard of this before. To answer this question, let's first review the database connection history.

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.
ODBC compatible databases include access, MS-SQL server, Oracle, INFORMIX and so on.

However, ODBC is not perfect. It still contains a large number of low-level calls and it is still difficult to develop ODBC applications. developers have to spend a lot of energy on the underlying database communication, rather than focusing on the data they want to process. later, Microsoft proposed a solution: Dao (data access objects ). the Dao code looks like this:

Objitem. addnew
Objitem. Name = "Chair"
Objitem. Price = 10
Objitem. Update

You may have read the DaO code. later Dao evolved into rdo (Remote Data Objects, designed for the distributed database system), and then ADO. although they all have their own shortcomings. according to Microsoft, "ODBC provides access to local SQL data, and Dao provides advanced data objects ". dao and rdo both require data to be stored in SQL (Structured Query Language) format. to address these defects, Microsoft proposed oledb, a com-based data storage object that can provide operations on all types of data and even access data offline (for example, you are using your host, and you can easily see the data image for the last data synchronization ).

Oledb 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. how can we directly connect to oledb?

To directly connect to the oledb layer, you must change the connection string of your connection object. Use the old method to create a connectiong object:

Dim objconn
Set objconn = server. Createobject ("ADODB. Connection ")

Next, we don't need to routinely look like DSN = pubs or driver = {ms SQL-
Server}; uid = sa; Pwd =; database = pubs; server = mymachine connection string, and use the following connection string:

Objconn. connectionstring = "provider = providername; Data
Source = databasesource; initial catalog = databasename; user id = userid;
Password = password"

For SQL:
Providername = sqloledb
Data Source = server name
Initial catalog = Database Name

For access:
Providername = Microsoft. Jet. oledb.3.51
Data Source = full path to. MDB File

Let's take a look at two examples. One is for access and the other is for SQL. If your DSN-less connection string for connecting to SQL is as follows:

Driver = {ms SQL-server}; uid = sa; Pwd =; database = pubs; server = mymachine

The connection string directly connected to oledb should be as follows:

Provider = sqloledb; Data Source = mymachine; initial catalog = pubs; User
Id = sa; Password =

Let's take a look at access. If your access connection string is:

Driver = {Microsoft Access Driver (*. mdb )};
DBQ = C:/inetpub/wwwroot/users. MDB

The connection string directly connected to oledb should be as follows:

Provider = Microsoft. Jet. oledb.3.51; Data
Source = C:/inetpub/wwwroot/users. MDB

That's easy, right?

Is this important?
Now, you may be confused about how to learn this new database connection method. Why not go through the standard DSN-less/system DSN path? Let me tell you why. According to wrox's ADO 2.0
In the programmer's reference book, the performance of connections connected with oledb instead of DSN or DSN-less is improved as follows:

Performance Comparison
SQL access
Oledb DSN
Connection times: 18 82 connection times: 62 99
Iterating through 1,000 records times: 2900 5400 iterating through
1,000 records times: 100 950

 

 

 

I. Comparison of ADO, rdo, and Dao

ADO is not automatically compatible with your existing data access application code. When ADO encapsulates the functionality of DAO and rdo, many language elements must be converted into ADO syntax. In some cases, you can make a simple conversion of some functions of the existing code. In other cases, it is best to rewrite the application with the new features of ADO.

Dao (data access objects) data access object is the first object-oriented interface, which exposes the Microsoft Jet Database Engine (used by Microsoft Access ), and allows Visual Basic developers to directly connect to access tables through ODBC, just like directly connecting to other databases. Dao is most suitable for single-system applications or small-scale local distribution.

Rdo (Remote Data Objects) Remote Data Objects are an ODBC-oriented data access interface. They are combined with easy-to-use DAO styles and provide an interface, shows the underlying functions and flexibility of all ODBC databases. Although rdo is restricted in its access to jet or isam databases, it can only access relational databases through the existing ODBC driver. However, rdo has proved to be the best interface that many SQL Server, Oracle, and other large relational database developers often choose. Rdo provides more complex objects, attributes, and methods used to access stored procedures and complex result sets.

ADO is a successor of Dao/rdo. Ado 2.0 is more functionally similar to rdo, and generally there is a similar ing between the two models. Ado "extends" the object model used by Dao and rdo, which means it contains fewer objects, more attributes, methods (and parameters), and events. For example, if ADO does not have the same object as rdoengine and rdoenvironment, the ODBC driver manager and henv interface can be exposed. Although your interface may be implemented through the odbc ole db service provider, you cannot create an ODBC Data Source from ADO currently.

The multiple functions included in the DAO and rdo models are merged into a single object, which generates a much simpler object model. However, for this reason, you may initially find it very difficult to find the appropriate ADO object, set, attribute, method, or event. Unlike Dao and rdo, ADO objects can be created out of the hierarchy although they are hierarchical.

However, it should be noted that ADO currently does not support all DaO functions. Ado mainly includes rdo-style functionality to facilitate interaction with ole db data sources, as well as remote and DHTML technology.

Generally speaking, it may be too early to transplant most Dao applications to ADO during the evolution of ADO, because the current ADO does not support data definition (DDL), users, and groups, and so on. However, if you only use DAO for client-server applications and do not rely on the Jet Database Engine or do not use DDL, it is now possible to port Dao to ADO. Microsoft will provide an ado ddl component to help transplant Dao to ADO and provide general DDL support for ole db vendors.

 

Ii. Ado 2.0 Object Model

The ADO 2.0 object model consists of eight objects, most of which are similar in functionality to rdo objects, but only have stronger functionality. It takes some time to browse the object model in Object Browser (F2) to familiarize yourself with the location of various attributes, methods, events, collections, and so on.
Note that all objects followed by an asterisk (*) are objects that are also applied to the ADO recordset Type Library (Ador.
· The command object contains information about a command, such as query strings and parameter definitions. The command object is similar to the rdo rdoquery object in function.
· The connection object contains information about a data provider. The connection object is similar to the rdo rdoconnection object in function and contains information about the structure description. It also contains functions of some rdoenvironment objects, such as the transaction control.
· The error object contains the extended information when the data provider encounters an error. The error object is similar to rdo's rdoerror object in function.
· Field object * contains the information of a single column in the record set data. The function of the Field object is similar to that of the rdo rdocolumn object.
· The parameter object contains the information of a single parameter of the parameterized command object. This command object has a set of parameters containing all its parameter objects. The parameter object is functionally similar to the rdoparameter object of rdo.
· Property object * contains the features defined by the provider of An ADO object. There is no rdo equivalent to this object, but Dao has a similar object. An ADO object can have two attributes:
· Built-in attribute: "local" attribute of ADO. That is to say, any new object using the familiar myobject. Property syntax can immediately use the properties in ADO. The built-in attribute does not appear in the form of property objects in the properties set of an object. Therefore, although you can modify their values, you cannot modify or delete them.
· Dynamic attributes: non-local attributes of ADO, which are defined by the next-level data provider. They appear in the properties set of the appropriate ADO object.
For example, the attribute specific to a data provider may indicate whether A recordset object supports transactions or updates. These additional properties appear as property objects in the properties set of the recordset. Dynamic attributes can only be referenced by using the myobject. properties (0) or myobject. properties ("name") syntax. Different data providers may provide one or more special properties to process operations specific to the provider.
· Recordset object * the recordset object contains the records returned by a query and the cursors in those records. The recordset object is similar to the rdoresultset object in rdo. You can open a recordset without explicitly opening the connection object (for example, executing a query ). However, if you choose to create a connection object, you can open multiple recordset objects on the same connection.

 

 

Iii. Migration of General Data Access

Two basic data access solutions are listed below. Each solution is discussed in the topic below. In each solution, an rdo solution and an ADO solution are introduced.
· Establish a connection to a database
· Run a basic query

Note that some examples reference additional controls on a form, such as an mshflexgrid control or Textbox Control. To make your project work properly, add these controls to the project.

1. Establish a connection to a database
1.> rdo
To open a connection, you must provide a connection string with parameters. Note: When rdo creates an rdoquery object, it does not need to be connected. However, when an rdoresultset object is initially created, it is required:
Dim cn as new rdoconnection
Dim CNB as new rdoconnection
Const connectionstring = "uid = myname; Pwd = mypw; driver = {sqlserver };_
Server = myserver; database = pubs; DSN = ''"
The connection string accesses a specific SQL server and allows ODBC to open a connection without a DSN. This is a typical ODBC connection string with all standard parameters.
In the next code section, a cursor driver type and logon timeout are created in the form load event. By default, rdo uses the rduseifneeded cursor type, which calls the server-side cursor on SQL Server. The following example specifies rdusenone so that this default rule is not observed. The rddrivernoprompt flag means that if the user ID and password do not match, the application will generate an error.
Private sub form_load ()
With Cn
CN. Connect = connectstring
CN. logintimeout = 10
CN. cursordriver = rdusenone
CN. establishconnection rddrivernoprompt
End
The second connection executes any customer batch update:
With CNB
CNB. Connect = connectstring
CNB. cursordriver = rduseclientbatch
CNB. establishconnection
End
End sub
When the connection operation is complete, the last event occurs and it can handle any errors that occur when the connection is opened. You can use this event to test whether the connection works normally. If the connection works properly, any button dependent on the connection is valid.
Private sub cn_connect (byval erroroccurred as Boolean)
If erroroccurred then
Msgbox "cocould not open connection", vbcritical
Else
Runokframe. Enabled = true
End if
End sub
2.> ado
To establish a database connection in ADO, first create a set of ADO objects that can be referenced by the ADODB object. These objects will be used later to set specific attributes for opening the connection and generate the result set:
Dim cn as new ADODB. Connection
Dim RS as new ADODB. recordset
Dim CNB as new ADODB. Connection
Dim QY as new ADODB. Command
The next line creates a connection string, As you created in the previous rdo example. In both cases, you use ODBC's "non-DSN" connection policy to save time and improve performance:
Const connectionstring = "uid = myname; Pwd = mypw; driver = {SQL Server };_
Server = myserver; database = pubs; DSN = ", connection = adconnectasync"
The following declaration initializes the variables used in this example. (Note the creation of a variable array that saves the result set ):
Dim SQL as string
Dim RC as integer
Dim I as integer
Dim changes as integer
Dim BMS () as Variant
Next, open an ADO connection to a database in the form_load event. Note that this code is very similar to rdo code, except that the constant starts with "ad" rather than "RD ". If you want to see all the constants that can be used, check the ADODB database.
Note that you do not need to specify the prompt action because ADO is set to "no prompt" by default ". However, if you choose to change it, you can use the ADO properties set to process the desired prompt behavior. In rdo, you can use the openconnection parameter to set behavior. In ADO, you must set properties ("prompt.
At the same time, if you do not want to use the cursor driver, you do not need to specify it (such as cursordriver = rdusenone in rdo), because the default rule of ADO is that there is no cursor driver.
Private sub form_load ()
With Cn
'Establish a non-DSN connection
. Connectionstring = connectstring
. Connectiontimeout = 10
'. Properties ("prompt") = adpromptnever
'This is the default prompting mode in ADO.
. Open
End
With CNB
. Connectionstring = connectstring
. Cursorlocation = aduseclient
. Open
End
End sub
2. Run a basic query
1.> rdo
This event process returns an SQL statement-based result set. It executes a restricted query and passes the result set to a control, which inserts the result data into an mshflexgrid control. Note: An open connection is required to establish a result set.
Private sub runbutton_click ()
Dim RS as rdoresultset
Set rs = cn. openresultset ("select * from titles where title _
Like '% H '")
Rdogrid1.showdata rs
Rs. Close
End sub
2.> ado
Once the database connection is enabled, you can run a query on it. The following event process is very similar to the previous rdo code. However, in this case, you use the new ADO open method and ADO connection object for SQL query as parameters, rather than using the openresultset METHOD OF THE rdoconnection object. You can also choose to use the execute method of the ADO connection object, as you can do in rdo (as long as it does not return a row set ).
A major difference between ado2 and rdo2 is that ado2 allows you to create a record set and set its attributes before opening the record set.
Private sub runbutton_click ()
Dim RS as new ADODB. recordset
Rs. Open "select * from titles where title like '% H'", CN
Adogrid1.showdata rs
Rs. Close
End sub
You can run the query and process the result set asynchronously in ADO. That is to say, you specify the adfetchasynch option on Rs. Open, ADO causes the cursor driver to automatically fill the result set in the background.