Using Streams for Command Input

ADO and SQL Server

ADO and SQL Server

Using Streams for Command Input

ADO queries can be specified by setting the CommandText property on the Command object or by associating the stream with the Command object using the CommandStream property.This example demonstrates using a stream to access the Northwind database. It uses an Active Server Page (ASP) and is written in Microsoft Visual BasicĀ® Scripting Edition.

Using XML Template Queries in Streams

The application initializes the ADO Stream object to contain query text:

Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open

The application requires a reference to the XML Namespace identified by the sql: prefix of the <sql:query> tag. The SELECT statement with a reference to the sql: Namespace takes this form:

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO </sql:query>
</ROOT>

By using the FOR XML AUTO mode of the SELECT statement, this query requests that results are returned in XML format, rather than as a Recordset object. For more information, see Retrieving and Writing XML Data.

The command is then assigned to a string variable, and copied to the adoStreamQuery stream, which is associated with an ADO Command object:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO </sql:query>
</ROOT>"
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
Dim adoCmd 
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.CommandStream = adoStreamQuery
Setting the Command Language Dialect

The second requirement of the application is setting the command language dialect, which specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text received from ADO. The dialect is specified by a globally unique identifier (GUID) and is set using the Dialect property of the Command object. The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) supports these values.

ADO Value OLE DB Constant Description
{C8B521FB-5CF3-11CE-ADE5-00AA0044773D} DBGUID_DEFAULT Provider-specific default behavior
{C8B522D7-5CF3-11CE-ADE5-00AA0044773D} DBGUID_SQL Transact-SQL query
{5D531CB2-E6Ed-11D2-B252-00C04F681B71} DBGUID_MSSQLXML XML template query
{EC2A4293-E898-11D2-B1B7-00C04F680C56} DBGUID_XPATH XPath query

The command dialect for XML queries is specified as follows:

AdoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"