To read the article online, visit http://www.4GuysFromRolla.com/webtech/063099-1.shtml

OLEDB For Me


There are a number of ways to connect to a database. You can use a System DSN, a DSN-less connection, or the native OLEDB provider. Now, many of you may be scratching your heads right now. OLEDB? What in tarnation is that? Well, to answer that we need to look at some database history.

Back in the old days, database connectivity was difficult. Everybody had their own database formats, and developers had to know a low level API for each database they wished to develop for. There was a push for a universal API, an API which would work for numerous data stores. It was about this time that ODBC, or Open Database Connectivity, which was an early attempt at creating this universal API. A number of databases conformed to this standard, and became known as ODBC-compliant databases. ODBC-compliant databases consist of Access, MS-SQL Server, Oracle, Informix, etc.

Well, ODBC wasn't perfect. It still contained a lot of low-level calls, and was difficult to develop with. Developers had to focus more on low-level communications with the database, as opposed to being able to concentrate on getting the data they needed and using it how they saw fit. Along came Microsoft's solution: DAO, or Data Access Objects. If you've seen code like:

objItem.AddNew
objItem.Name = "Chair"
objItem.Price = 10
objItem.Update

you were probably looking at DAO code. After DAO came RDO (Remote Data Objects, targetted for distributed database architecture), and then ADO. These have all had their shortcomings, though. According to Microsoft, "ODBC provides native access to SQL data" and "DAO provides high-level objects to data". Even DAO and RDO require the data in a data store to be in SQL format (Structured Query Language). In response to these shortcomings, Microsoft introduced OLEDB, a COM-based data access object which provides access to all types of data, and even provides access to disconnected data stores (for example, if you're on your laptop, you can easily view a snapshot of the database from the last time you synced up).

OLEDB sits between the ODBC layer and the application. With your ASP pages, ADO is the "application" that sits above OLEDB. Your ADO calls are first sent to OLEDB, which are then sent to the ODBC layer. You can connect directly to the OLEDB layer, though, and if you do so, you'll see an increase in performance for server-side cursors (the default cursor type for recordsets, and the most common type of cursor used). So, just how do you connect directly to OLEDB?

The answer, young grasshopper, is in the connection string of your connection object. Create a connection object like usual:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

Then, instead of using a connection string like DSN=pubs or DRIVER={MS SQL-Server};UID=sa;PWD=;DATABASE=pubs;SERVER=myMachine, 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

(Note: This only works for ADO 2.0 and up! If you are using an older version of ADO, you will need to upgrade. You can download the latest version of ADO for free at http://www.microsoft.com/data.)

So, let's look at two examples, one for Access and one for SQL. Say that you had a DSN-less connection string for a SQL database like so:

DRIVER={MS SQL-Server};UID=sa;PWD=;DATABASE=pubs;SERVER=myMachine

To connect directly to OLEDB, your connection string would be:

Provider=SQLOLEDB; Data Source=myMachine; Initial Catalog=pubs; User ID=sa; Password=

Now, let's look at the Access side. If you had an Access connection string like so:

DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\users.mdb

To connect directly to OLEDB, your connection string would be:

Provider=Microsoft.Jet.OLEDB.3.51; Data Source=c:\inetpub\wwwroot\users.mdb

That's it! Pretty simple, eh?

Why it matters...
Now you might be wondering why learn this new connection method? Why not just stick with the standard DSN-less/System DSN approach? Well, I'll tell you why. According to tests done by Wrox in their book ADO 2.0 Programmer's Reference, if you use an OLEDB connection as opposed to a DSN or DSN-less connection, you'll see the following improvements:

Performance Comparison
SQLAccess
 OLEDBDSN  OLEDBDSN
Connection Times: 1882 Connection Times: 6299
Iterating through 1,000 Records Times: 29005400 Iterating through 1,000 Records Times: 100950

Note: these results are published on pgs. 232, 233 in Wrox's ADO 2.0 Programmer's Reference. Times are in milliseconds, and the iterating through 1,000 Records times were calculated using server-side cursors (there was only a minor difference in performance between OLEDB & DSN recordset iterations when using client-side cursors).

Well, I hope this has educated a few of you, and convinced you to connect to your SQL and Access data stores a bit differently! :) Have a great day!

Happy Programming!


Article Information
Article Title: 4GuysFromRolla.com - OLEDB For Me
Article Author: Scott Mitchell
Published Date: Wednesday, June 30, 1999
Article URL: http://www.4GuysFromRolla.com/webtech/063099-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers