none
IMultipleResults::GetResult returning data after multiple calls instead of first time RRS feed

  • Question

  • Hi Forum,

    I made a test application to run SQL queries and procedures using oledb liberaries. I am using IMultipleResults interface in the ICommand::Execute API.

    Then to retreive my results, I am using  IMultipleResults::GetResult API. I found that after calling  IMultipleResults::GetResult, HRESULT is returned S_OK but rowset pointer is set to NULL.

    After calling IMultipleResults::GetResult again(second time), HRESULT is returned S_Ok and a valid rowset pointer is returned.

    Can someone tell me the reason for this behaviour?. And What I need to do to check this problem?
    Or do I need to call IMultipleResults::GetResult API multiple times to retreive results?

    Please find the sample code from main function below

    void main(int argc, char* argv[])
    {    
        IUnknown *    pIUnknown = NULL;
        BOOL *        pbValue = FALSE;
        CLSID        clsid;
        HRESULT        hr;
        IDataInitialize*        pIDataInitialize = NULL;
        IDBInitialize *        pIDBInitialize = NULL;
        IDBProperties*        pIDBProperties = NULL;
        IMultipleResults*   pIMultipleResults=NULL;
        IRowset*            pIRowset=NULL;

        hr = CoInitialize(NULL);

        //Invoke the OLE DB Service Components with the MSDAINITIALIZE
        //provider. Session pooling is enabled while the
        //IDataInitialize interface pointer is held. CLSID_SQLOLEDB
        hr = CoCreateInstance( CLSID_SQLOLEDB,NULL,CLSCTX_INPROC_SERVER,IID_IDBInitialize,(void **) &pIDBInitialize);

        const ULONG nProps = 5;
        DBPROP InitProperties[nProps];
        DBPROPSET rgInitPropSet[1];

        for (ULONG i = 0; i < nProps; i++)
        {
            VariantInit(&InitProperties[i].vValue);
            InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
            InitProperties[i].colid = DB_NULLID;
        }

        //Level of prompting performed to complete the connection process.
        InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
        InitProperties[0].vValue.vt = VT_I2;
        InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;

        //Datasource name.
        InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
        InitProperties[1].vValue.vt = VT_BSTR;
        InitProperties[1].vValue.bstrVal = SysAllocString(OLESTR("NOIKU001AG-W1\\SQLEXPRESS"));
        //InitProperties[1].vValue.bstrVal = SysAllocString(OLESTR("TEST"));

        //Userid.
        InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
        InitProperties[2].vValue.vt = VT_BSTR;
        InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR("sa"));

        //Password.
        InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
        InitProperties[3].vValue.vt = VT_BSTR;
        InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR("sagent"));

        //Catalog : Your database name.
        InitProperties[4].dwPropertyID = DBPROP_INIT_CATALOG;
        InitProperties[4].vValue.vt = VT_BSTR;
        InitProperties[4].vValue.bstrVal = SysAllocString(OLESTR("kunal_test"));

        //Assign the property structures to the property set.
        rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
        rgInitPropSet[0].cProperties = nProps;
        rgInitPropSet[0].rgProperties = InitProperties;

        hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
        hr = pIDBProperties->SetProperties(1, rgInitPropSet);

        pIDBProperties->Release();

        ULONG i; //Infosys:64-bit change:i declared out of for loop as ULONG
        for( i=0; i<nProps; i++ )
        {
            if (InitProperties[ i ].vValue.vt == VT_BSTR)
                SysFreeString( InitProperties[ i ].vValue.bstrVal );
        }

        hr = pIDBInitialize->Initialize();
        if (FAILED(hr))
            return;

        //These variables are for Command creation.
        IDBCreateSession*       pIDBCreateSession;
        IDBCreateCommand*       pIDBCreateCommand;

        ICommandText*           pICommandText;
        LPCTSTR wSQLString1 = OLESTR("EXEC dbo.stpDataFlow_AlterTableTest2");
        LPCTSTR wSQLString2 = OLESTR("CREATE TABLE tempdb..#tmp_Testing(AcctNo VARCHAR(20) NOT NULL,TestColumn INT )")
            OLESTR("ALTER TABLE tempdb..#tmp_Testing ADD PRIMARY KEY (AcctNo)")
            OLESTR("INSERT INTO tempdb..#tmp_Testing (AcctNo, TestColumn)SELECT 'AccountTest1', '12345'")
            OLESTR("SELECT * FROM tempdb..#tmp_Testing");

        LONG                    cRowsAffected;

        //Get the DB Session object.
        hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,(void**)&pIDBCreateSession);
        if (FAILED(hr))
            return;

        //Create the session, and get an interface for command creation.
        hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,(IUnknown**)&pIDBCreateCommand);
        pIDBCreateSession->Release();
        if (FAILED(hr))
            return;

        //Create the command object.
        hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,(IUnknown**)&pICommandText);
        pIDBCreateCommand->Release();
        if (FAILED(hr))
            return;

        hr = pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString2);

        //Execute the command.
        hr = pICommandText->Execute( NULL,IID_IMultipleResults,NULL,&cRowsAffected,(IUnknown **) &pIMultipleResults );

        hr = pIMultipleResults->GetResult(NULL, DBRESULTFLAG_ROWSET,IID_IRowset,&cRowsAffected, (IUnknown **) &pIRowset);
        hr = pIMultipleResults->GetResult(NULL, DBRESULTFLAG_ROWSET,IID_IRowset,&cRowsAffected, (IUnknown **) &pIRowset);
        hr = pIMultipleResults->GetResult(NULL, DBRESULTFLAG_ROWSET,IID_IRowset,&cRowsAffected, (IUnknown **) &pIRowset);
        hr = pIMultipleResults->GetResult(NULL, DBRESULTFLAG_ROWSET,IID_IRowset,&cRowsAffected, (IUnknown **) &pIRowset);

       // Some other code here

    }

    Regards.

    Thursday, December 20, 2012 9:34 AM

Answers

  • Hello Nakul,

    Your SQL statements show that you are issuing an INSERT statement and then a SELECT statement. Since the INSERT statement happens first, the pcRowsAffected pointer is updated with the number of records affected. And since the result is not a rowset, the ppRowset pointer would be null. This behavior is by design since INSERT, UPDATE and DELETE don't return a rowset.

    The reason you see the valid ppRowset pointer the next time you run the IMultipleResults::GetResult again(second time) is because you are processing records from the SELECT statement which does return a rowset.

    Take a look at the information in this article below and you can make use of the code snippet. http://msdn.microsoft.com/en-us/library/windows/desktop/ms723081(v=vs.85).aspx

    Thanks


    Abdulwahab Suleiman

    • Marked as answer by Nakul Virmani Sunday, October 9, 2016 12:09 PM
    Tuesday, January 8, 2013 4:16 PM

All replies

  • Hi Nakul,

    Welcome to the MSDN forum.

    I am trying to involve a senior expert into your thread to help you solve this problem. Please wait for the response. Sorry for any inconvenience.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 21, 2012 8:19 AM
  • Hello Nakul,

    Your SQL statements show that you are issuing an INSERT statement and then a SELECT statement. Since the INSERT statement happens first, the pcRowsAffected pointer is updated with the number of records affected. And since the result is not a rowset, the ppRowset pointer would be null. This behavior is by design since INSERT, UPDATE and DELETE don't return a rowset.

    The reason you see the valid ppRowset pointer the next time you run the IMultipleResults::GetResult again(second time) is because you are processing records from the SELECT statement which does return a rowset.

    Take a look at the information in this article below and you can make use of the code snippet. http://msdn.microsoft.com/en-us/library/windows/desktop/ms723081(v=vs.85).aspx

    Thanks


    Abdulwahab Suleiman

    • Marked as answer by Nakul Virmani Sunday, October 9, 2016 12:09 PM
    Tuesday, January 8, 2013 4:16 PM