Microsoft KB Archive/281430

= How to create a user-defined class to retrieve XML data from SQL Server 2000 in Visual C++ 6.0 =

Article ID: 281430

Article Last Modified on 6/2/2005

-

APPLIES TO


 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q281430



SUMMARY
To retrieve tokenized XML data from SQL Server 2000, you must request an ISequentialStream interface during the call to the ICommand::Execute method.

The Visual C++ 6.0 Active Template Library (ATL) OLE DB Consumer Templates do not provide a way to request this interface during the Execute call. This article provides information on how to create your own class to provide this functionality.



MORE INFORMATION
To get XML data from SQL Server 2000 using the OLE DB consumer templates, perform the following steps:  In Visual C++, create a new empty Win32 Console Application project.  Create a CRowsetStream class using the following code. This is a user-defined class that derives from CRowset and asks for an ISequentialStream interface, instead of an IRowset interface, to be returned from ICommand::Execute. Save this class in a new file named RowsetStream.h. class CRowsetStream : public CRowset {   public:   // Constructors and destructors. CRowsetStream {             m_spRowsetStream = NULL; }   ~CRowsetStream {             Close; }      // Methods. void Close {      if (m_spRowsetStream != NULL) m_spRowsetStream.Release; }

// Implementation. static const IID& GetIID {     return IID_ISequentialStream; }  IRowset* GetInterface const {     return (IRowset*)(ISequentialStream*)m_spRowsetStream; }  IRowset** GetInterfacePtr {     return (IRowset **)&m_spRowsetStream; }

CComPtr m_spRowsetStream; }; NOTE: When you construct the command class in step 4, you will specify CRowsetStream as the rowset template type.   Create a CDBDemoAccessor accessor class using the following code. CDBDemoAccessor is a standard OLE DB consumer templates accessor (similar to a wizard-generated class) except it does not have a column map. To retrieve XML data, you must request and use an ISequentialStream interface in ICommand::Execute to retrieve a stream of data, instead of using the output columns. Therefore, binding columns using the column map would cause errors. Add this class to the RowsetStream.h header file that you created in step 2. class CDBDemoAccessor { public: LONG m_RETURNVALUE; LONG m_percentage;

BEGIN_PARAM_MAP(CDBDemoAccessor) SET_PARAM_TYPE(DBPARAMIO_OUTPUT) COLUMN_ENTRY(1, m_RETURNVALUE) SET_PARAM_TYPE(DBPARAMIO_INPUT) COLUMN_ENTRY(2, m_percentage) END_PARAM_MAP

DEFINE_COMMAND(CDBDemoAccessor, _T(&quot;{ ? = CALL dbo.byroyaltyxml;1 (?) }&quot;))

// You may wish to call this function if you are inserting a record and wish to  // initialize all the fields, if you are not going to explicitly set all of them. void ClearRecord {     memset(this, 0, sizeof(*this)); } }; Note that CDBDemoAccessor uses a stored procedure called dbo.byroyaltyxml; this is the same as the sample SQL Server byroyalty stored procedure in the pubs sample database, with a FOR XML AUTO clause added to the end. Use the following SQL code to create this stored procedure: CREATE PROCEDURE byroyaltyxml @percentage int AS select au_id from titleauthor where titleauthor.royaltyper = @percentage FOR XML AUTO GO   Create a command class CDBDemo using the following code. CDBDemo is slightly modified from what the wizard generates. It specifies the CRowsetStream class that you created earlier as the template type for the rowset. CRowsetStream behaves just like CNoRowset (it does not support iterating through the rows, and so on), but you can use the m_spRowsetStream member variable or the GetInterface method to access the returned stream pointer and read the data. Thus, it lets you request the ISequentialStream interface easily. Add this class to the RowsetStream.h header file that you created in step 2.

NOTE: Change the user ID, password, and data source below to refer to a valid SQL Server. class CDBDemo : public CCommand, CRowsetStream > { public: HRESULT Open {     HRESULT      hr;

hr = OpenDataSource; if (FAILED(hr)) return hr;

return OpenRowset; }  HRESULT OpenDataSource {     HRESULT      hr; CDataSource db; CDBPropSet  dbinit(DBPROPSET_DBINIT);

dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR(&quot;myUserId&quot;)); dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(&quot;myPassword&quot;)); dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR(&quot;pubs&quot;)); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR(&quot;myServer&quot;)); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); hr = db.Open(_T(&quot;SQLOLEDB.1&quot;), &dbinit); if (FAILED(hr)) return hr;

return m_session.Open(db); } HRESULT OpenRowset {     return CCommand, CRowsetStream >::Open(m_session); }  CSession   m_session; };   Create a new C++ source file in the project and paste the following code:
 * 1) include <atldbcli.h>
 * 2) include &quot;RowsetStream.h&quot;


 * 1) define BUFFER_SIZE 1000

int main(int /*argc*/, char* /*argv*/[]) {       ::CoInitialize(NULL); {

CDBDemo mydemo; HRESULT hr = S_OK;

mydemo.ClearRecord; mydemo.m_percentage = 50; hr = mydemo.Open; if (hr == S_OK) {              char szBuf[BUFFER_SIZE]; ULONG ulRead = 0;

if (mydemo.m_spRowsetStream != NULL) {                  hr = mydemo.m_spRowsetStream->Read(szBuf, BUFFER_SIZE - 1, &ulRead); while(hr == S_OK) {                       printf(&quot;Read %ld bytes\n&quot;, ulRead); // The string we just read is not NULL terminated so we'll add one in. szBuf[ulRead] = '\0'; printf(&quot;%s\n&quot;, szBuf);

hr = mydemo.m_spRowsetStream->Read(szBuf, BUFFER_SIZE - 1, &ulRead); }              }            }

}

return 0; } </li> Build the project and then run it. The XML string from the database should be displayed in the console window.</li></ol>

<div class="references_section">