ADO 2.5 New Features

Dave Sussman, Wrox Press

Oh no, not another version!

ADO 2.5 is an interesting release. Many people might be confused, or even angry, about the constant supply of new versions, and they might desire some form of stability in what is an extremely important part of their applications - that of data access. But there's actually no need to worry. Although this latest version seems like a big change, it actually leaves much the same. The big change comes in the new objects that have been added, rather than in major changes to existing parts of the technology. So rest easy.

If this isn't such a major release for existing programs, then what's the big deal? Well quite simply, there are two new objects (the Record and the Stream), and support for semi-structured data. It doesn't sound very exciting but the potential is quite huge, and to understand why you need to think back to the original UDA strategy from Microsoft.

Dave Sussman David is a developer, trainer and author. He has authored and co-authored numerous titles for Wrox Press, including ADO 2.1 Programmer's Reference, Beginning Access 2000 VBA, Professional ASP 2.0, ASP 2.0 Programmer's Reference and Professional MTS MSMQ with VBA and ASP.

Universal Data Access

The whole idea behind UDA is that data is data. Through no real fault of our own we've spent many years deep in relational databases, and we've geared most of our programs around them too. We spend huge amounts of time getting data into SQL databases, just so we can get it out again, in the form of figures and reports. Where's the sense in that? Should we be diving straight to the source of the data? Yes of course we should, but until now it's always meant using a custom solution for each different set of data.

UDA proposes that we use a central set of methods to access our data. Not a particularly new idea, since ODBC managed to do this for SQL databases many years ago. Microsoft used OLEDB as a way to widen this idea of data access, but we've still really been stuck in the SQL trap. In versions 2.1 and earlier, OLEDB still only gave us access to relational data.

Version 2.5 brings us into the real of semi-structured data. What does this mean? Well, structured data is easy to imagine - just think of something like a SQL table held in a recordset:

au_id	au_lname	au_fname	phone	address
172-32-1176	White	Bob	408 496-7223	10932 Bigge Rd.
213-46-8915	Green	Marjorie	415 986-7020	309 63rd St. #411
238-95-7766	Carson	Cheryl	415 548-7723	589 Darwin Ln.
267-41-2394	O'Leary	Michael	408 286-2428	22 Cleveland Av. #14
274-80-9391	Straight	Dean	415 834-2919	5420 College Av.
341-22-1782	Smith	Meander	913 843-0462	10 Mississippi Dr.

Here each row contains the same number of columns, each with the same type of data. Even hierarchical recordsets are like this - at each level in the hierarchy, the columns contain the same data types.

Semi-Structured Data

Semi-structured data gives us a way to handle data that doesn't conform to the rigid rules of SQL data. We're talking about things such as file stores, messaging systems, and so on. Systems which do have some structure to them, but slightly looser than we're used to in the ADO world.

Generally semi-structured data is tree-like, containing node, sub-nodes and leaf elements. The key is that each node can have a different structure. It's easier to understand if you equate this to things you know, such as a directory structure, or a messaging system:

This shows a pretty standard machine configuration. It's a set of structured nodes, but each node has different properties. Some are removable media (floppy disk, and CD-Rom drive), some local drives, and some network drives. Each of these nodes can have sub-nodes of their own, each with distinct properties.

Another good example is a mailbox:

Again the structure is tree-like, with nodes and sub-nodes. Under Local Folders we have a set of folders, each with very different characteristics, whilst under the public newsgroup accounts, the folders have the same characteristics.

These two examples explain semi-structured data perfectly, where each row not necessarily the same structure as the previous row. So how does this fit into the new ADO objects:

Ø       Nodes are modeled as Records. Each node, or folder or directory, is therefore a Record. The properties for a Record are those that are distinct to the node, and these become the Fields of the Record.

Ø       Collections are modeled as Recordsets. So items such as collections of folders or directories become a Recordset. Although each individual folder might not be the same as the others, it will share a set of common properties - name, last access time, etc. These common properties become the fields of the recordset.

So in the mail system shown above, Local Folders is a Record because it is a distinct node in the tree. However, it does contain sub-nodes, so it has Children. These children are a collection, and are therefore a Recordset. Each row in this recordset might point to another node, so each row can be a Record. This is necessary because the Contacts folder has a completely different set of properties to the Calendar folder, or the Inbox. Any items that aren’t nodes are handled as rows of the recordset.

Let's have a look at the new ADO object model to see how the Record and Recordset objects fit together:

ADO 2.5 Object Model

The existing ADO objects retain the same structure as previous ADO versions, but the Record and Stream objects interact with the Recordset object and Fields collection. What this diagram clearly shows is the recursive nature of Records and Recordsets. This makes sense when you think back to the examples shown earlier. A Record can contain children, which are stored as a Recordset. Each row in the Recordset is a Record, which in turn can contain children, and so on.

You might wonder what use all of this is. After all, there's the File System Object (FSO) that you can use to access the file system. Well, the file system isn't the only target for these new objects. It's any type of storage that's organized in this fashion. The two that will be the primary focus of this technology are web servers and mail servers. At the time of writing the new version of Exchange (6.0) isn't released, and there is no released documentation of the OLEDB Provider for Exchange, but it's expected that we'll get full access to the Exchange Directory. In the mean time, we can concentrate on the OLEDB Provider for Internet Publishing.

Variable Naming

Now that we have a Record and a Recordset object, you'll probably start to see a change in the naming conventions for variables. Historically I, like many others, tended to use something like objRec or recAuthors for the variable name of a Recordset. But now this leads to confusion. Does the rec part mean a Record or a Recordset? So, the sensible solution is to change the way we name variables. I've started using rs as a prefix for Recordsets, and rec as a prefix for Records.

It's not a big issue, but it might stop you getting confused as you read through the code that appears in this document.

URL Usage

The simplest use of the Internet Publishing Provider is that it offers us access through ADO to the structure and content of a web site. So, whilst the FSO offers access through COM objects to the file system, ADO offers us a much wider world - access to any web server. Well, any web server that supports the Distributed Authoring and Versioning (DAV) or FrontPage Server Extensions protocols. In practice this is IIS4 (to a limited extent), IIS5, and any of the increasing number of web servers that support DAV.

The simplest way to access this nodal structure is just to open a URL. For example:

Dim recNode

Set recNode = Server.CreateObject ("ADODB.Record")

recNode.Open "http:/webdev.wrox.co.uk/public"

You can also use a Connection object, just like a normal database connection:

conWeb.Open "http:/webdev.wrox.co.uk/public"

recNode.Open conWeb

At this stage all you have is a single Record object pointing to a node. What you want to do it get hold of the children:

Traversing the tree

Set rsChilren = recNode.GetChildren

You now have a normal ADO recordset containing a collection of files and folders under the top level directory. Looping through the recordset, you can tell which rows are nodes and have children of their own by checking the field RESOURCE_ISCOLLECTION. This allows you to dip further into the structure - remember that each row can be a Record of its own:

While Not rsChildren.EOF

  If rsChildren("RESOURCE_ISCOLLECTION") Then

    recChildNode.Open rsChildren

Your now back into the recursive nature of tree structures. In fact, it's pretty easy to write a recursive routine to traverse, and print, a whole tree:

Sub Traverse(recNode)

  Dim rsChildren

  Dim recChildNode

  ' display the node name

  Response.Write Space(iLevel * 2) & recNode("RESOURCE_PARSENAME")

  ' get any child nodes

  Set rsChildren = recNode.GetChildren

  ' increase the indenting level

  iLevel = iLevel + 1

  ' loop through the children

  While Not rsChildren.EOF

    ' does this child contain other children

    If rsChildren("RESOURCE_ISCOLLECTION") Then

      recChildNode.Open rsChildren

      Traverse recChildNode

      recChildNode.Close

    Else

      Response.Write Space(iLevel * 2) & rsChildren("RESOURCE_PARSENAME")

    End If

    rsChildren.MoveNext

  Wend

     

  ' decrease the indenting level

  iLevel = iLevel - 1

  ' close the child recordset

  rsChildren.Close

End Sub

You can call this from an ASP page like this:

  Dim iLevel

  Dim recRoot

  Set recRoot = Server.CreateObject ("ADODB.Record")

  recRoot.Open "http://webdev.wrox.co.uk/public"

  Traverse recRoot

  recRoot.Close

Methods of the Record Object

GetChildren isn't the only method of the Record object. In fact, some of the other methods offer some great functionality:

Ø       CopyRecord, copies a file, or a node and its children to a new location.

Ø       MoveRecord, moves a file, or a node and its children to a new location

Ø       DeleteRecord, deletes a file, or a node and its children

These three methods offer you the ability to remotely manipulate files and directories on a web site. Pretty cool huh? You could easily build an administration tool using just these three commands. In fact, using CopyRecord, you can get a file from a remote location, edit it, and use CopyLocation to copy it back to the remote server. This is remote authoring at its very easiest.

The Stream Object

The Stream object provides access to the binary data. In practice this means the contents of a file, or the body of a message. Whilst the Record object allows you to copy an entire file from one location to another, the Stream gives you access to the contents of a file directly, without the file leaving its location. Ultimately this gives you the ability to edit files in place. Cool.

On it's own this is pretty powerful stuff, but the Stream interacts with a whole bunch of other objects which extends its power even further:

Ø       Response and Request objects

Ø       Recordset, Save and Open

Ø       MSXML, transformNodeToObject

Ø       CDO

These few interactions offer us a load of possibilities:

Streaming Binary Recordsets

The Save method of the Recordset has been enhanced, so that instead of just saving to a file you can save to a Stream. In conjunction with that, the Response object now supports the Stream interface. That gives you the possibility to save recordsets directly to the Response, and therefore directly up to the browser. Consider an asp file called ADOBinaryStream.ASP:

Response.ContentType = "multipart/mixed"

Response.Expires = 0

rs.Open "authors", . . .

rs.Save Response, adPersistADTG

This uses the standard binary format for persisting a recordset, but instead of using a file this recordset is being pushed to the browser. Now consider the following HTML file:

<OBJECT ID="dsoAuthors" CLASSID=". . .">

  <PARAM NAME="URL" VALUE="ADOBinaryStream.asp">

</OBJECT>

This uses a standard RDS Data Source Object, and for the source of the data it uses the ASP page, because the output of that ASP page is the stream of the binary data from the recordset.

Streaming XML Recordsets

The same sort of thing is possible with XML. In previous versions of ADO we've been able to persist recordsets as XML to a file, but only if they were normal recordsets. Hierarchical (shaped) recordsets weren't catered for, until now.

The following code creates an XML file in the browser:

Response.ContentType = "text/xml"

Response.Buffer = False

Response.Write "<?xml version='1.0' encoding='ISO-8859-1'?>

rs.Open "authors", . . .

rs.Save Response, adPersistXML

Streaming XML Data Islands

Another possible use for streaming XML to the client is the creation of data islands:

<HTML>

<BODY>

<%

    rs.Open "authors", . . .

    rs.Save Response, adPersistXML

  %>

</BODY>

</HTML>

This looks really promising, but at the time of writing (using Windows 2000 beta 3), this didn't quite work as expected. Whilst it created an island of XML in the HTML file, this wasn't recognized as an XML DSO. It just appeared as a normal element which, not being a standard HTML element, is correctly ignored by the browser. It exists in the DOM, but not as a DSO.

The existing method of creating data islands still works perfectly:

<XML ID="dsoAuthors" SRC="Authors.xml"></XML>

Although this too, has a problem if the XML is created by the Save method of a recordset, because the XML DSO doesn't recognize schemas as a way of describing the XML data. It still uses DTDs, so you generally have to go three levels deep to actually get to your XML data. At the top level you have the root XML element. Under than you have two siblings - one for the schema, and one for the data. Then under the data, you have an element for each row of data. Phew!

Opening Recordsets from Streams

It may seem exciting to pass recordsets up to the browser, but the excitement soon wears off if they get stuck there. You really need a way to get the data back to the server, and this is where the Request object comes in, as streaming has been added to this too:

Imagine your HTML page with an XML data island:

<XML ID="dsoAuthors" SRC="authors.xml"></XML>

function PostXML()

{

  var xmlHTTP = new ActiveXObject("Microsoft.XMLHTTP");

  xmlHTTP.open("POST", "http:/webdev.wrox.co.uk/public/SendData.asp);

  xmlHTTP.send(dsoAuthors.XMLDocument);

}

This uses the XMLHTTP object to create HTTP requests, to POST the XML data stored in the DSO back to an ASP page, which looks like this:

rs.Open Request, , , , adCmdFile

Pretty cool. So you can now easily get back a recordset from a browser.

Transforming XML

If you're only interested in the XML Data as the source for displaying information, then you can use XSL to transform the XML before sending it to the browser. It wasn't too difficult to do before, but it's now even easier.

The first thing to do is create an XML DOM object, and save the recordset into this:

Set xmlData = Server.CreateObject ("Microsoft.XMLDOM")

rs.Save xmlData, adPersistXML

As you can see, you just specify the DOM object as the destination to save the XML data to.

Then you create another DOM object, to contain the stylesheet:

Set xmlStyle = Server.CreateObject ("Microsoft.XMLDOM")

xmlStyle.Load("RecordsetToTable.xsl")

Then you transform the data directly into the stream being sent to the browser:

xmlData.transformNodeToObject xmlStyle, Response

CDO and ADO

Although we're not going to cover it in detail here, it's worth mentioning that CDO and ADO are now closer than ever. Whilst the OLEDB Provider for Exchange may well provide all of the functionality of CDO, that doesn't mean to say that CDO is dead. The fact that it's been enhanced proved it's still very much a worthwhile technology. Like many of these things, there's often a bit of an overlap, and it's sometimes horses for course. Which one you use depends upon preference or what you're trying to achieve.

The first point to note is that many CDO objects now provide a Fields collection. This doesn't directly relate to ADO, but it's just the same Fields collection, so it's worth mentioning.

The second, and more important, point is that the Stream interface has been added to CDO, to give you access to the content of a message. That can be the entire message or just parts of the body content. So what does this really mean? Well you've already seen that you can open a recordset directly from stream. Yeah, that's right - if a message contains an XML fragment, you can open a recordset directly from this. So now you can easily mail around recordsets. You might not want to, but heck, since when has need stood in the way of progress?

Changes to Existing Objects

Well that's a brief look at the new objects and how they can be used, and there's very little change to the existing objects.

XML Persistence now supports hierarchical recordsets. You should be aware though that at this stage, the XML DSO relies upon a DTD or heuristics to define the structure of the XML, therefore the schema becomes part of the XML data.

The other change, which is currently under debate as I write this, is a possible change to error handling. It's not a fundamental change to the way error handling works, but rather a change as to what's reported. Currently, many ADO errors are just defined as 'Errors Occurred', which is not particularly useful. The current discussion is whether this error message should be replaced with more meaningful message, at the risk of breaking code. So far the overwhelming choice amongst beta testers is to change the error messages - the more information we get as developers the better. And besides, as someone said - anyone who relies on a single error message in their code gets what they deserve!

Summary

The greatest changes to the new 2.5 version of ADO actually brings a great deal of potential to us as programmers. The ability to handle semi-structured storage means that the dream of universal data access is truly here. All we have to do is grasp that dream and bring it to reality.

The Record object, in conjunction with the Recordset, gives us the way to handle semi-structured data. We can easily navigate through tree-like structures, accessing Nodes and leaf elements simply by the records in a recordset.

Using the Stream we can transfer data between ASP pages and other applications more easily than before. Although I've shown using the Response and Request objects, you can use any object that supports the COM IStream interface.

It's an extensible world, and you've just joined it.