Executing a Command

After the connection to a data source is established, the consumer calls the IDBCreateSession::CreateSession method to create a session. The session acts as a command, rowset, or transaction factory.

To work directly with individual tables or indexes, the consumer requests the IOpenRowset interface. The IOpenRowset::OpenRowset method opens and returns a rowset that includes all rows from a single base table or index.

To execute a command (such as SELECT * FROM Authors), the consumer requests the IDBCreateCommand interface. The consumer can execute the IDBCreateCommand::CreateCommand method to create a command object and request for the ICommandText interface. The ICommandText::SetCommandText method is used to specify the command that is to be executed.

The Execute command is used to execute the command. The command can be any SQL statement or procedure name. Not all commands produce a result set (rowset) object. Commands such as SELECT * FROM Authors produce a result set.

OLE DB Extensions for XML

The ICommandText::SetCommandText and ICommand::Execute statements can be used to set XML documents as command text, execute the command, and retrieve the result as a stream, which can then be used in further processing, such as passing the XML to the Document Object Model (DOM).

Templates are valid XML documents that contain one or more SQL command tags. These XML templates can be passed to ICommandText::SetCommandText. When XML templates are set as command text using ICommandText::SetCommandText, the consumer must pass DBGUID_MSSQLXML as the globally unique identifier (GUID) of the command syntax. This GUID indicates that the command text is an XML template.

The following sample shows the use of DBGUID_MSSQLXML.

void ValidMSSQLXML::Variation_1 () {
   int status;
   Ptr<ISequentialStream, &IID_ISequentialStream> pIStream;
   ULONG ulNumRead = 0;
   string strCmd;
   string strCmp;
   CHAR szBuf[1000];

   try {
      // Reset the connection, just in case.
      if ( FAILED( g_pCommand->Reset() ) )
         throw 0;

      // Format the command string.
      strCmd = "";
      strCmd << L"<root xmlns:sql=\"urn:schemas-microsoft-com:xml-sql\"><sql:query> select * from " << (LPCWSTR) *pValidMSSQLXML << L" for XML AUTO</sql:query></root>";

      // Set the command text.
      if ( FAILED( g_pCommand->SetCommandText( strCmd, DBGUID_MSSQLXML ) ) )
         throw 0;

      // Execute the command.
      if ( FAILED( g_pCommand->Execute( (LPUNKNOWN*) &pIStream, &IID_ISequentialStream ) ) )
         throw 0;

      // Read from the stream.
      ZeroMemory( szBuf, 1000 );
      if ( FAILED( pIStream->Read( szBuf, 1000, &ulNumRead ) ) )
         throw 0;

      // Format the compare string.
      strCmp = "";
      strCmp << "<root xmlns:sql=\"urn:schemas-microsoft-com:xml-sql\"><" << (LPCSTR) *pValidMSSQLXML 
         << " a=\"1\" b=\"Testing\"/><" << (LPCSTR) *pValidMSSQLXML << " a=\"2\" b=\"Another Test\"/></root>";

      // Was it the expecetd stream?
      if ( strcmp( szBuf, (LPCSTR) strCmp ) != 0 )
         throw 0;

   }
   catch( int statusIn ) {
      status = statusIn;
   }

   pIStream.Release();
   return status;
}

The consumer must call ICommand::Execute to execute XML templates. To obtain XML documents as a result set, riid must be set to IStream.

See Also

Concepts

Creating a SQL Native Client OLE DB Provider Application

Help and Information

Getting SQL Server 2005 Assistance

Change History