Capturing XML output from ADO using a Stream object

I will describe here the solution to a problem that taxed me at work this week, in the faint hope that it will prove useful to someone else who needs to do the same strange thing.

The web application I am working on uses SQL stored procedures to do all the work. I use the FOR XML extension of Microsoft SQL Server 2000 so that the results emerge in XML format rather than the usual collection of records.

This is all mediated through Microsoft’s ADO conventions. The way this works is that you create an ADO Command object as usual, and use the special property called Properties to set certain special parameters. (It is not obvious why these are not implemented as regular properties.) One of the special properties is XSL, which names an XSLT resource that is used to convert the raw XML emitted by SQL Server in to something more useful, such as HTML. Another special property called Output Stream is used to plumb the output straight to the ASP Response object:

cmd.Properties("XML Root") = "foo"
cmd.Properties("XSL") = xslDir & theme & "/foo.xslt"
cmd.Properties("Output Stream") = Response

You then execute the command passing the adExecuteStream flag and all is well. We can describe this with a Cocoon-style pipeline:

The advantage of this set-up is that it reduces the VBScript portion of the application to the bare minimum needed to marshal the form parameters in to data that can be fed in to a SQL procedure and then execute the proc with its output transformed in to the HTML page. The ASP pages actually contain no HTML at all: the application logic (such as it is) is all in SQL, and the appearance entirely controlled by the XSLT.

Things got tricky when suddenly I wanted to have one page generate several flavours of output beyond the usual HTML, such as PDF. I would need a software component to do this, and since ASP/VBScript was being used as the application glue, the component would have to be a COM coclass. But how to get the XML in to the component? On the face of it there are three obvious ways to do this:

• The Output Stream property is set to my component, and it implements the stream interface that the ADO command object expects.
• The ADO command might be persuaded to expose the XML as a readable stream (client pull rather than server push). The new component would then read its data from this.
• Find the equivalent of a C++ stringstream or Python StringIO—something that pretends to be an output stream but instead buffers the XML data in memory. The XML is then sent to the new component as a string parameter.

In theory the first option is the best from the point of view of pipelining:

Unfortunately the documentation was maddeningly vague as to how to implement the stream interface. The SQL Server documentation said ‘OLEDB stream’, the OLEDB documentation alluded to IStream, which turns out to mean a bundle of half-a-dozen custom COM interfaces... Blurk. No way to do this in five minutes. (This contrasts badly with Python’s use of ‘file-like objects’ which just need to implement a few simple methods like write.)

The second option, where my program reads from the ADO object, rather than it writing to stream, does not seem possible. With ADO .NET you can execute a command in a fashion that returns a reader object, but I could not crack this with ADO. Oh, well.

The last of these has the advantage of conceptual simplicity at the expense of storing the whole XML document in memory at once:

In practice this should not be a problem, since the data would not be over-large. So all I needed was the buffer object. The SQL Server documentation mentions an ADO class Stream. It has no information about the methods or properties of the Stream class, but something like this should work, right?:

Dim strm
Set strm = Server.CreateObject("ADODB.Stream")
cmd.Properties("Output Stream") = strm
cmd.Execute ,, adCmdStoredProc Or adExecuteStream
... read stream somehow ...

Unfortunately this causes the script to fail with the usual frustratingly ambiguous error message from ADO. I spent ages trying different variations, Googling for hints, and retrying the discarded ideas from earlier, before I found a page that mentioned that you have to call strm.Open before it will work. Why? You just do.

Dim strm
Set strm = Server.CreateObject("ADODB.Stream")
strm.Open
cmd.Properties("Output Stream") = strm
cmd.Execute ,, adCmdStoredProc Or adExecuteStream
strm.Position = 0
text = strm.ReadText
strm.Close

Now all that remains is the minor task of writing a COM component that exposes a method MakePdfWriteToStream along the following lines:

Dim pdfMaker
Set pdfMaker = Server.CreateObject("myproject.PdfMaker")
pdfMaker.MakePdfWriteToStream(Response)

But that’s another story.