SAMPLE: How to Retrieve Data Using a Template File in ATL OLE DB (272181)

MORE INFORMATION

The following file is available for download from the Microsoft Download Center:
Release Date: Oct. 12, 2000

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The OLEDBTemplate.exe file contains the following files:

File name	Size
EULA.txt	2 KB
Products.xml	1 KB
Products.xsl	3 KB
SeqStream.h	4 KB
OLEDBTemplate.cpp	5 KB

This sample shows how an Active Template Library (ATL) OLE DB client can execute a SQL XML query stored in a template file and retrieve the XML data from a SQL Server 2000 database using the Microsoft SQL Server OLE DB Provider (SQLOLEDB) that ships with Microsoft Data Access Components (MDAC) version 2.6. The Products.xml template file contains the following parameterized SQL XML query:

<?xml version="1.0" ?> 
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:header>
      <sql:param name="ProdName">%</sql:param> 
   </sql:header>
   <sql:query>SELECT * FROM Products WHERE ProductName like '%' + @ProdName + '%' ORDER BY ProductName FOR XML AUTO</sql:query> 
</root>
				

This query uses the wildcard % to ensure that if no parameter is passed to it, the query is effectively reduced to a non-parameterized query.

In addition, this sample provides an ISequentialStream derived class, CSequentialStream, that provides the capability to read from a file. This provides the input stream object for the ICommandStream interface. The CSequentialStream class can be found in SQL Server 2000 Books Online as well.

The sample configures an input XML stream by means of a CSequentialStream object, executes a SQL XML query stored within a template file, and retrieves the XML data in an ISequentialStream stream. Depending on whether the Products.xsl stylesheet is used for processing the output XML stream, the following output files are generated:

• Queryout.htm, when XSL is used.
• Queryout.xml, when XSL is not used.

The sample does the following to set up an ATL OLE DB client to retrieve XML data as a result of executing a SQL XML query stored in a template file:

1. It configures an input stream to load the Products.xml template file:

   CSequentialStream *pXMLInput;				
   pXMLInput = new CSequentialStream(L"Products.xml");	
   					

2. It sets up the SQLOLEDB property-set DBPROPSET_SQLSERVERSTREAM for configuring the input stream:

   		
   CDBPropSet	propset(DBPROPSET_SQLSERVERSTREAM);
   					

3. It sets the Base Path property to the folder that contains the template file and the optional .xsl file:

   propset.AddProperty(SSPROP_STREAM_BASEPATH, (LPCWSTR)m_path);
   					

4. (Optional) It specifies the Products.xsl XSL file to process the XML data that is retrieved and output it in HTML format:

   propset.AddProperty(SSPROP_STREAM_XSL, OLESTR("Products.xsl"));
   					

5. It sets the ICommandStream interface to contain the input stream:

   if(FAILED(hr = m_spCommand->QueryInterface(&pCommandStream)))
   {
     printf("Failed to get an ICommandStream interface...\n");
     return hr;
   }
   if(FAILED(hr = pCommandStream->SetCommandStream(IID_ISequentialStream, DBGUID_DEFAULT, (ISequentialStream*) pXMLInput )))
   {
     printf("Failed to set command stream.\n");
     return hr;
   }
   					

6. It sets up an output stream to retrieve the XML data and execute:

   m_spCommand->Execute(NULL, IID_ISequentialStream, NULL, NULL, (IUnknown **) &pIXMLOutput );
   					

Steps to Run the Sample

1. Create an empty Win32 console application.
2. Insert the OLEDBTemplate.cpp file into the project.
3. Insert the SeqStream.h file into the project.
4. Copy Products.xml into the project folder.
5. Copy Products.xsl into the project folder.
6. Modify the connection string to refer to a valid SQL Server 2000 database.
7. Compile and then run the application.