The ADO is a collection of objects that expose a standard set of properties and methods that our applications can use when accessing data. Like many of its predecessors, such as DAO and RDO, ADP relies on an underlying layer of software to actually interact with a given data source. As we saw in the last chapter, OLE DB is this underlying layer.
OLE DB technology is being positioned as the cornerstone to Microsoft's component database architecture. It is a set of OLE interfaces that provide applications with a standard means of accessing data stored in various information sources. These standard interfaces support specific elements of the Database Management System's (DBMS) functionality that are appropriate to the data source, enabling it to share its data.
The benefits of component DBMS's can be seen in the success of the Open Database Connectivity (ODBC) database access interface. ODBC is provided as a means of accessing data from a diverse set of sources, using a standard series of functions and commands, the idea being that the programmer is shielded from having to code to each specific data source’s requirements, thus vastly increasing productivity.
OLE DB takes ODBC a step further, towards a truly standard means of accessing data from diverse sources. Whereas ODBC is designed around accessing relational data sources using Structured Query Language (SQL), OLE DB is focused on providing access to any data, anywhere. For example, an ODBC provider has just been released that provides access to NT 4, Novell version 3, and NDS directory services, all through OLE DB. And there are more to come.
In addition to simplifying the programmer’s job, the OLD DB interface layer provides the developer with a means of accessing data which may not be stored in a traditional DBMS format. As we all know, there is a large amount of mission-critical data stored in systems that are not classified as a DBMS. One of the most significant limitations of the ODBC approach to data access is that it is difficult for non-relational database vendors to support. Building a data provider layer means exposing the data via SQL. For a non-SQL data provider, such as an Excel file or a Mail system, this requires the equivalent of a SQL engine within the ODBC driver.
OLE DB simplifies the development of access methods for simple tabular data providers by only requiring them to implement the functionality native to their data source. At a minimum, an access provider must implement the interfaces necessary to expose data in a tabular form. This requirement allows for the development of query processor components, such as SQL query processors, that can work with tabular information from any provider that exposes its data through OLE DB. In essence then, OLE DB provides an interface layer that is consistent despite its having an underlying data structure that may be very diverse.
As we have seen, OLE DB is a collection of components that work together to provide data access capabilities to an application. These components are loosely grouped into two classes, consumers and providers. Consumers are the components that submit requests for data. Providers service these requests by accessing the data sources, and retrieving the information requested. In these terms, ADO is an OLE DB consumer. In other words, it makes requests to a provider. The name of the provider that will service a request is supplied either as a part of a connection string, or as the Provider property of the Connection object.
In this example, we supply the name of the Microsoft ODBC provider for OLE DB and the ADO, MSDASQL, as a parameter to the Provider method of the Connection object.
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Provider ="MSDASQL"
DBConn.Open "DSN=SQLForum;UID=sa;PWD=;"
Set Session("DBConn") = DBConn
Be careful not to include a Provider Name as both a part of the connection string, and as the Provider property of the Connection object. If you do, the result is unpredictable.
Providers, like any other OLE object, must be defined in the registry before the OLE DB layer can use them. They are identified by the OLE DB Provider sub-key, under the class ID of the provider. Within the HKEY_CLASSES_ROOT key, providers must have the following sub-keys and values for the programmatic identifier (ProgID):
ProviderProgID = FriendlyDisplayName
ProviderProgID\CLSID = ProviderCLSID
The entries for the MSDASQL ODBC provider are show here—the CLSID value is a unique key that identifies the provider installed:
MSDASQL = Microsoft OLE DB Provider for ODBC Drivers
MSDASQL\CLSID = {c8b522cb-5cf3-11ce-ade5-00aa0044773d}Under the HKEY_CLASSES_ROOT\CLSID sub-key, providers must have the following sub-keys and values:
ProviderCLSID = FriendlyDisplayName
ProviderCLSID\ProgID = ProviderProgID
ProviderCLSID\VersionIndependentProgID = VersionIndependentProgID
ProviderCLSID\InprocServer32 = ProviderDLLFilename
ProviderCLSID\InprocServer32\ThreadingModel = Apartment | Free | Both
ProviderCLSID\OLE DB Provider = Description
Again, the entries for the MSDASQL ODBC provider are:
{c8b522cb-5cf3-11ce-ade5-00aa0044773d} = MSDASQL
{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\VersionIndependentProgID = MSDASQL
{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\InprocServer32 = MSDASQL.DLL
{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\InprocServer32\ThreadingModel =
? Both
{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\OLE DB Provider = Microsoft OLE DB
? Provider for ODBC DriversIn addition, the ODBC provider entry references a second key that points to another OLE object, which is used for error processing. Other providers are identified in the registry in this same way. Thankfully, the installation of ADO provides all the registry entries required, but you'll find the information here useful if you need to delve into the registry yourself.
Let’s take a moment to look at how we connect to various data sources. The ADO provides a number of ways of actually establishing a connection to a data source. Each has its advantages and disadvantages but, underneath the covers, each is limited by the ability of the provider to service the connection. As we walk through various examples, keep in mind that some of the features discussed may only be appropriate for the provider being used in that example.
In the previous chapter, we reviewed how the Connection object is used to create a connection between an ASP page and a data source. Now let's look in more detail at the Connection object and at some of the more advanced options we can take use when connecting to and working with various data sources, including SQL Server.
The Connection object is the parent object in a hierarchy of several other objects including the Errors object, Command object and Recordset object. The Connection object can be used to create a new connection to a data source, by providing it with a connection string or connection information. This connection can then be referenced by each of the other objects in the hierarchy to interact with the described data source.
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Provider="MSDASQL"
DBConn.Open "DSN=SQLForum; UID=sa; PWD=; APP=Forum; WSID=MAINFRAME;
? Database=Forum"
Set Session("DBConn") = DBConn
However, unlike the DAO and RDO libraries, the ADO doesn't require you to work your way down a hierarchy of objects in order to instantiate the one you actually require. The Command and Recordset objects can be called independently of any Connection object, allowing these objects to create a new Connection directly and interact with the database through it:
Set oRS = Server.CreateObject("ADODB.RecordSet")
oRS.Open "Select * from Message", "DSN=SQLForum;UID=sa;PW="
Response.Write "<B> Records Found: </B><P>"
Do While Not oRS.EOF
For intCount = 0 to oRS.Fields.Count -1
Response.Write oRS.Fields(intCount).Value & " - "
Next
Response.Write "<BR>"
oRS.MoveNext
Loop
In this example, we create a simple listing of each record in the Message table. You will notice that no Connection object is explicitly created, we simply create a Recordset with the Open method of the Recordset, using an SQL query and a connection string. Behind the scenes, ADO creates a Connection object and associates it with the Recordset we just created. When the Recordset goes out of scope or is set to Nothing, the Connection object is released.
Let's take another example. Here we need to call an SQL Server Stored Procedure only once per session. Rather then opening a Connection object, and passing a reference to it to a Command object, we can take advantage of the Command object’s ability to create a Connection object for us automatically in the background:
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "dsn=SQLForum;database=Forum;uid=sa;pwd=;"
oCmd.CommandText = "{call myproc}"
oCmd.Execute
In the above example, we supply a connection string to the Open method of the object. The connection string is passed directly to the underlying ODBC driver, which in turn uses it to attach to the specified data source. Here a connection object is created for temporary use and destroyed when the oCmd object variable goes out of scope, or is set to Nothing.