Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

showplan_text via oledb


I'm trying to get the textual result set of a showplan from a query
using OLEDB. I'm issuing an ICommandText command of "set showplan_text
on" using IID_NULL then I'm executing my query. The result set I get
back is the result of the query, not the show plan. I tried using
MultipleResults thinking that perhaps both the actual query and the
showplan might be returned but no such luck.

(and yes, I know you can do this in T-SQL and the GUI)

Doing the same thing in ODBC works fine.

Anyone know what might be happening here and a solution?

You should be getting back two result sets (assuming that you're executing a
single statement). The first result set echos back the statement, the second
contains multiple rows that make up the execution plan. The only reason it
could possibly not be working for you is if you're setting SET SHOWPLAN_TEXT
ON for one connection and then submitting your query on a second connection.

Please post your code that reproduces the issue if you still have a problem.

--
Cheers,
Stefan Delmarco | SQL Server MVP | http://www.fotia.co.uk/

-----------------------------------------------Reply-----------------------------------------------

On May 29, 4:38 pm, "Stefan Delmarco" <stefandeonl@fotia.co.uk>
wrote:

> You should be getting back two result sets (assuming that you're executing a
> single statement). The first result set echos back the statement, the second
> contains multiple rows that make up the execution plan. The only reason it
> could possibly not be working for you is if you're setting SET SHOWPLAN_TEXT
> ON for one connection and then submitting your query on a second connection.

> Please post your code that reproduces the issue if you still have a problem.

> --
> Cheers,
> Stefan Delmarco | SQL Server MVP |http://www.fotia.co.uk/

Yes, I agree, and the ODBC test I did gave me exactly that.

Without re-writing everything to simplest form here's some snapshots
of the relevant code:

...
    // Attempt to create a command object from the session object
    // See IDBCreateCommand
    CHECK_HR(hr = driverData_ptr->OLEDBObjects->g_pISession-

>QueryInterface(

                        IID_IDBCreateCommand,
                        (void**)&pIDBCreateCommand
                        ));

    CHECK_HR(hr = pIDBCreateCommand->CreateCommand(

NULL,                                       // pUnkOuter

IID_ICommandText,                           // riid

(IUnknown**)&pICommandText                  // ppCommand
                                ));
// wCmdString = "set SHOWPLAN_TEXT on"
    CHECK_HR(hr = pICommandText->SetCommandText(
                            DBGUID_DEFAULT,
                            (LPOLESTR)wCmdString
                            ));
    // Prepare the command.
    CHECK_HR(hr = pICommandText->QueryInterface(
                        IID_ICommandPrepare,
                        (void**)&pICommandPrepare));

    CHECK_HR(hr = pICommandPrepare->Prepare(0));

    CHECK_HR(hr = pICommandText->Execute(
                            NULL,
                            IID_NULL,
                            NULL,
                            &cRowsAffected,
                            NULL
                            ));
<big snip>
// CLEAN_UP (at the end of this example) run next
// incoming SELECT stored in wCmdString
</big snip>
... now the query part
    IRowset *            pRowset;

    // Attempt to create a command object from the session object
    // See IDBCreateCommand
    CHECK_HR(hr = driverData_ptr->OLEDBObjects->g_pISession-

>QueryInterface(

                        IID_IDBCreateCommand,
                        (void**)&pIDBCreateCommand
                        ));

    CHECK_HR(hr = pIDBCreateCommand->CreateCommand(

NULL,                                       // pUnkOuter

IID_ICommandText,                           // riid

(IUnknown**)&pICommandText                  // ppCommand
                                ));

// wCmdString = SELECT COUNT(RECID) FROM XMLTEST WHERE
XMLCOLUMN.value('(/row/balance)[1]', 'NVARCHAR(max)'))=1000

    CHECK_HR(hr = pICommandText->SetCommandText(
                            DBGUID_DEFAULT,
                            (LPOLESTR)wCmdString
                            ));
    // Prepare the command.
    CHECK_HR(hr = pICommandText->QueryInterface(
                        IID_ICommandPrepare,
                        (void**)&pICommandPrepare));

    CHECK_HR(hr = pICommandPrepare->Prepare(0));

    CHECK_HR(hr = pICommandText->Execute(
                            NULL,
                            IID_IRowset,
                            NULL,
                            &cRowsAffected,
                            (IUnknown **)&pRowset
                            ));
   CHECK_HR(hr = pRowset->QueryInterface(
                        IID_IRowset,
                        (void**)&fileData_ptr->pRowset
                        ));
<big snip>
// setup accessor/bindings/etc
</big snip>

      // Attempt to get cRows row handles from the provider.
      hr = fileData_ptr->pRowset->GetNextRows(
               DB_NULL_HCHAPTER,                      // hChapter
               0,                                     // lOffset
               driverData_ptr->max_prefetch_rows,       //
MAX_PREFETCH_ROWS
               &fileData_ptr->RowsObtained,           //
pcRowsObtained
               &fileData_ptr->rghRows                 // prghRows
               );
      // nothing fetched out, return back with NULL data
      if ( hr==DB_S_ENDOFROWSET && fileData_ptr->RowsObtained <= 0 ||
fileData_ptr->rghRows == NULL )
      {
          DumpError("No more rows to fetch!");
          return jXDB_ENDOFFILE;
      }
      else
          // fetched some rows, but less than we requested, which
means this is the last round
          if (hr == DB_S_ENDOFROWSET && fileData_ptr->RowsObtained >
0)
          {
              fileData_ptr->last_round_readrowset = 1;
          }
          else
          {
            CHECK_HR(hr);
          }
   // Loop over the row handles obtained from GetNextRows,
   // actually fetching the data for these rows into our buffer.
   // each time get called, the routine returns one single row to the
caller.
   unsigned int iRow = fileData_ptr->index_rows;

   // Find the location in our buffer where we want to place
   // the data for this row.
   void * pCurData   = CoTaskMemAlloc(fileData_ptr->cbRowSize);

   // Get the data for this row handle. The provider will copy
   // (and convert, if necessary) the data for each of the
   // columns that are described in our Aaccessor into the given
   // buffer (pCurData).
   CHECK_HR(hr = fileData_ptr->pRowset->GetData(
               fileData_ptr->rghRows[iRow],            // hRow
               fileData_ptr->hAccessor,                // hAccessor
               pCurData                                // pData
               ));

   // count how many rows we have processed, in order to move the
pointer to next segment of buffer.
   fileData_ptr->index_rows++;

====== WATCH POINT =================
   // Now extract values from pCurData

<snip>
// At this point all I'm getting is the value of the SELECT COUNT(..)
// i.e. a number (and I recognize the number as matching the number of
rows)
</snip>
=====================================

CLEANUP:
    free(wCmdString);
    wCmdString = NULL;
    SAFE_RELEASE(pICommandPrepare);
    SAFE_RELEASE(pICommandText);
    SAFE_RELEASE(pIDBCreateCommand);
    if( FAILED(hr) )
        return jXDB_ERROR;
    else
        return jXDB_SUCCESS;

If you run a SQL Profiler trace you'll see that you are establishing
multiple connections to SQL Server. You must only call
IDBCreateSession->CreateSession once and call
IDBCreateCommand->CreateCommand multiple times.

Attached is some OLE DB C++ that executes a basic query, sets SHOWPLAN_TEXT
ON and gets the execution plan for the basic query.

--
Cheers,
Stefan Delmarco | SQL Server MVP | http://www.fotia.co.uk/

[ OleDbShowPlan.cpp ]
#include <stdafx.h>

class OleDbCpp
{
public:
        static void CanGetShowPlanText();

private:
        static void GetBasicResultSet(const CComPtr<IDBCreateCommand> & createCommand);
        static void SetShowPlanOn(const CComPtr<IDBCreateCommand> & createCommand);
        static void GetQueryPlan(const CComPtr<IDBCreateCommand> & createCommand);

};

void OleDbCpp::CanGetShowPlanText()
{
        CLSID CLSID_SQLOLEDB = {};
        HRESULT hr = ::CLSIDFromProgID(L"SQLNCLI.1", &CLSID_SQLOLEDB);
        if(FAILED(hr))
                throw smd::ComException("CLSIDFromProgID(L\"SQLOLEDB\", ...)", hr);

        CComPtr<IDBInitialize> initialize;
        hr = initialize.CoCreateInstance(CLSID_SQLOLEDB, 0, CLSCTX_INPROC_SERVER);
        if(FAILED(hr))
                throw new smd::ComException("CoCreateInstance(CLSID_SQLOLEDB, ...) failed", hr);

        DBPROP InitProperties[2] = {};

        ::VariantInit(&InitProperties[0].vValue);
        InitProperties[0].dwPropertyID = DBPROP_INIT_PROVIDERSTRING;
        InitProperties[0].vValue.vt = VT_BSTR;
        InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[0].colid = DB_NULLID;
        InitProperties[0].dwStatus = DBPROPSTATUS_OK;
        InitProperties[0].vValue.bstrVal = CComBSTR("Server=tcp:chenbro,18980;Trusted_Connection=yes");

        ::VariantInit(&InitProperties[1].vValue);

        DBPROPSET rgInitPropSet[1] = {};
        rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
        rgInitPropSet[0].cProperties = 2;
        rgInitPropSet[0].rgProperties = InitProperties;

        CComQIPtr<IDBProperties> properties = initialize;
        hr = properties->SetProperties(1, rgInitPropSet);
        if(FAILED(hr))
                throw smd::ComException("properties->SetProperties(...) failed", hr);

        hr = initialize->Initialize();
        if(FAILED(hr))
                throw smd::ComException("intialize->Initialize() failed", hr);

        CComQIPtr<IDBCreateSession> createSession = initialize;
        CComPtr<IUnknown> createCommandUnknown;
        hr = createSession->CreateSession(NULL, IID_IDBCreateCommand, &createCommandUnknown);
        if(FAILED(hr))
                throw smd::ComException("createSession->CreateSession(...) failed", hr);

        CComQIPtr<IDBCreateCommand> createCommand = createCommandUnknown;

        GetBasicResultSet(createCommand);
        SetShowPlanOn(createCommand);
        GetQueryPlan(createCommand);

}

void OleDbCpp::GetBasicResultSet(const CComPtr<IDBCreateCommand> & createCommand)
{
        std::wcout << L"Attempting to retrieve a basic resulset.." << std::endl;

        CComPtr<IUnknown> commandTextUnknown;
        HRESULT hr = createCommand->CreateCommand(NULL, IID_ICommandText, &commandTextUnknown);
        if(FAILED(hr))
                throw smd::ComException("createCommand.CreateCommand(...) failed", hr);

        CComQIPtr<ICommandText> commandText = commandTextUnknown;
        hr = commandText->SetCommandText(DBGUID_DEFAULT, L"SELECT TOP 10 id, name FROM sysObjects");
        if(FAILED(hr))
                throw smd::ComException("commandText->SetCommandText(...) failed", hr);

        LONG numRows = 0;
        CComPtr<IUnknown> rowsetUnknown;
        hr = commandText->Execute(NULL, IID_IRowset, NULL, &numRows, &rowsetUnknown);
        if(FAILED(hr))
                throw smd::ComException("commandText->Execute(...) failed", hr);

        CComQIPtr<IRowset> rowset = rowsetUnknown;

        DBBINDING bindings[2] = {};

        struct Row
        {
                int id;
                WCHAR name[128];
        };

        bindings[0].iOrdinal = 1;
        bindings[0].dwPart = DBPART_VALUE;
        bindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        bindings[0].cbMaxLen = 4;
        bindings[0].dwFlags = 0;
        bindings[0].wType = DBTYPE_I4;

        bindings[1].iOrdinal = 2;
        bindings[1].dwPart = DBPART_VALUE;
        bindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        bindings[1].cbMaxLen = 128 * 2;
        bindings[1].dwFlags = 0;
        bindings[1].wType = DBTYPE_WSTR;

        CComQIPtr<IColumnsInfo> columnsInfo = rowset;
        ULONG numColumns = 0;
        DBCOLUMNINFO * dbColumnInfo;
        WCHAR* stringsBuffer;
        hr = columnsInfo->GetColumnInfo(&numColumns, &dbColumnInfo, &stringsBuffer);
        if(FAILED(hr))
                throw smd::ComException("columnsInfo->GetColumnInfo(...) failed", hr);

        for(ULONG i = 0; i < numColumns; ++i)
        {
                std::wcout.width(40);
                std::wcout << std::left << dbColumnInfo[i].pwszName;
        }
        std::wcout << std::endl;

        CComQIPtr<IAccessor> accessor = rowset;
        HACCESSOR hAccessor;
        hr = accessor->CreateAccessor(DBACCESSOR_ROWDATA, numColumns, bindings, 0, &hAccessor, NULL);
        if(FAILED(hr))
                throw smd::ComException("accessor->CreateAccessor(...) failed", hr);

        ULONG rowsRetrieved = 0;
        HROW hRows[10];
        HROW * rows = &hRows[0];
        hr = rowset->GetNextRows(NULL, 0, 10, &rowsRetrieved, &rows);
        if(FAILED(hr))
                throw smd::ComException("rowset->GetNextRows(...) failed", hr);

        Row row;
        while(rowsRetrieved > 0)
        {
                for(ULONG i = 0; i < rowsRetrieved; ++i)
                {
                        ::ZeroMemory(&row, sizeof(row));
                        hr = rowset->GetData(hRows[i], hAccessor, &row);
                        if(FAILED(hr))
                                throw smd::ComException("rowset->GetData(...) failed", hr);

                        std::wcout.width(40);
                        std::wcout << std::left << row.id;
                        std::wcout.width(40);
                        std::wcout << std::left << row.name << std::endl;
                }
                hr = rowset->ReleaseRows(rowsRetrieved, hRows, NULL, NULL, NULL);
                if(FAILED(hr))
                        throw smd::ComException("rowset->ReleaseRows(...) failed", hr);

                hr = rowset->GetNextRows(NULL, 0, 10, &rowsRetrieved, &rows);
                if(FAILED(hr))
                        throw smd::ComException("rowset->GetNextRows(...) failed", hr);

                std::wcout << L"OK!\r\n" << std::endl;
        }

}

void OleDbCpp::SetShowPlanOn(const CComPtr<IDBCreateCommand> & createCommand)
{
        std::wcout << L"Attempting to SET SHOWPLAN_TEXT ON..." << std::endl;

        CComPtr<IUnknown> commandTextUnknown;
        HRESULT hr = createCommand->CreateCommand(NULL, IID_ICommandText, &commandTextUnknown);
        if(FAILED(hr))
                throw smd::ComException("createCommand.CreateCommand(...) failed", hr);

        CComQIPtr<ICommandText> commandText = commandTextUnknown;
        hr = commandText->SetCommandText(DBGUID_DEFAULT, L"SET SHOWPLAN_TEXT ON");
        if(FAILED(hr))
                throw smd::ComException("commandText->SetCommandText(...) failed", hr);

        LONG numRows = 0;
        hr = commandText->Execute(NULL, IID_NULL, NULL, &numRows, NULL);
        if(FAILED(hr))
                throw smd::ComException("commandText->Execute(...) failed", hr);

        std::wcout << L"OK!\r\n" << std::endl;

}

void OleDbCpp::GetQueryPlan(const CComPtr<IDBCreateCommand> & createCommand)
{
        std::wcout << L"Attempting to get query plan..." << std::endl;

        CComPtr<IUnknown> commandTextUnknown;
        HRESULT hr = createCommand->CreateCommand(NULL, IID_ICommandText, &commandTextUnknown);
        if(FAILED(hr))
                throw smd::ComException("createCommand.CreateCommand(...) failed", hr);

        CComQIPtr<ICommandText> commandText = commandTextUnknown;
        hr = commandText->SetCommandText(DBGUID_DEFAULT, L"SELECT TOP 10 id, name FROM sysObjects");
        if(FAILED(hr))
                throw smd::ComException("commandText->SetCommandText(...) failed", hr);

        CComPtr<IUnknown> multipleRowSetsUnknown;
        LONG numRows = 0;
        hr = commandText->Execute(NULL, IID_IMultipleResults, NULL, &numRows, &multipleRowSetsUnknown);
        if(FAILED(hr))
                throw smd::ComException("commandText->Execute(...) failed", hr);

        CComQIPtr<IMultipleResults> multipleRowSets = multipleRowSetsUnknown;

        DBBINDING bindings[1] = {};

        struct Row
        {
                WCHAR step[128];
        };

        bindings[0].iOrdinal = 1;
        bindings[0].dwPart = DBPART_VALUE;
        bindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        bindings[0].cbMaxLen = 128 * 2;
        bindings[0].dwFlags = 0;
        bindings[0].wType = DBTYPE_WSTR;

        for(;;)
        {
                CComPtr<IUnknown> rowsetUnknown;
                hr = multipleRowSets->GetResult(NULL, 0, IID_IRowset, &numRows, &rowsetUnknown);
                if(FAILED(hr))
                        throw smd::ComException("pIMultipleResults->GetResult(...) failed", hr);
                if(DB_S_NORESULT == hr)
                        break;

                CComQIPtr<IRowset> rowset = rowsetUnknown;
                CComQIPtr<IColumnsInfo> columnsInfo = rowset;
                ULONG numColumns = 0;
                DBCOLUMNINFO * dbColumnInfo;
                WCHAR* stringsBuffer;
                hr = columnsInfo->GetColumnInfo(&numColumns, &dbColumnInfo, &stringsBuffer);
                if(FAILED(hr))
                        throw smd::ComException("columnsInfo->GetColumnInfo(...) failed", hr);

                for(ULONG i = 0; i < numColumns; ++i)
                {
                        std::wcout.width(40);
                        std::wcout << std::left << dbColumnInfo[i].pwszName;
                }
                std::wcout << std::endl;

                CComQIPtr<IAccessor> accessor = rowset;
                HACCESSOR hAccessor;
                hr = accessor->CreateAccessor(DBACCESSOR_ROWDATA, numColumns, bindings, 0, &hAccessor, NULL);
                if(FAILED(hr))
                        throw smd::ComException("accessor->CreateAccessor(...) failed", hr);

                ULONG rowsRetrieved = 0;
                HROW hRows[10];
                HROW * rows = &hRows[0];
                hr = rowset->GetNextRows(NULL, 0, 10, &rowsRetrieved, &rows);
                if(FAILED(hr))
                        throw smd::ComException("rowset->GetNextRows(...) failed", hr);

                Row row;
                while(rowsRetrieved > 0)
                {
                        for(ULONG i = 0; i < rowsRetrieved; ++i)
                        {
                                ::ZeroMemory(&row, sizeof(row));
                                hr = rowset->GetData(hRows[i], hAccessor, &row);
                                if(FAILED(hr))
                                        throw smd::ComException("rowset->GetData(...) failed", hr);

                                std::wcout.width(40);
                                std::wcout << std::left << row.step << std::endl;
                        }
                        hr = rowset->ReleaseRows(rowsRetrieved, hRows, NULL, NULL, NULL);
                        if(FAILED(hr))
                                throw smd::ComException("rowset->ReleaseRows(...) failed", hr);

                        hr = rowset->GetNextRows(NULL, 0, 10, &rowsRetrieved, &rows);
                        if(FAILED(hr))
                                throw smd::ComException("rowset->GetNextRows(...) failed", hr);
                }
        }
        std::wcout << L"OK!\r\n" << std::endl;

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc