ADO Problem

What exactly would you aspect to return?

Using SQL Server 2000, Query Analizer and executing the following statemets:

use somedb;
update TableName set Field1='Bogdan' where id = 678

Here's what apears in the output window:

(1 row(s) affected)

And in MSDN July 2001 at URL: "mk:@MSITStore:D:\MSDN-J~1\MSDN\ado270.chm::/htm/mdmthcmdexecute.htm" (to go to that URL in MSDN chose URL.. option from the Go menu). So here's what MSDN sais:

"If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset. Some application languages allow you to ignore this return value if no Recordset is desired."

So the flag you were talking about is adExecuteNoRecords.

Happy coding,

Adi

But there are multiple statements in the stored procedure - for instance an update followed by a select.  Say I wanted to update a value by 1 and return that to the caller.  The query analyzer shows it successfully returning the record, but a closed recordset is returned to _RecordsetPtr.open().  How can I get that record?  Thanks for your help.

Here's a seemingly simple stored procedure (I'd add some locking to it if this was the real thing):
CREATE PROCEDURE update_test AS
UPDATE misc
SET data = data + 1
WHERE name = 'count'

SELECT data FROM misc
WHERE name = 'count'
GO

This returns no rows (throws an exception on MoveFirst) if the update statement is there.  If I remove it, the data comes back just fine.  Why is this?  I used the code Adrian provided, as well as a straight _RecordsetPtr::open, and I'm still getting the same result.

are you sure that a record with the fiels name set to 'count' exists?

_RecorsetPtr::MoveFirst() cause a exception if the recordset is empty. And I think the reason for which "Execute returns a closed Recordset" is that your recordset is in fact empty (see first comment, with the quote from MSDN).

Please, post me the EXACT table structure and the EXACT C++ code.

Adrian

i forgot to tell you that I made a table with the fields data of type int and name of type nvarchar; and I used your EXACT code for the stored procedure.

And using the EXACT C++ code posted above, EXCEPT the line

_bstr_t bstrSP(L"sp_mysp");

modified to

_bstr_t bstrSP(L"update_test");

And everythig worked just fine: the update was done and the code outputed the modified row.

And if I change the strings from 'count' to 'anyother string that don't exist in any row' in the stored procedure, using query analizer, the 'Grids' tab is empty and 'Messages' tab shows:

(0 row(s) affected)


(0 row(s) affected)

(as it should).

And that's why your _RecordsetPtr is empty.

i forgot to tell you that I made a table with the fields data of type int and name of type nvarchar; and I used your EXACT code for the stored procedure.

And using the EXACT C++ code posted above, EXCEPT the line

_bstr_t bstrSP(L"sp_mysp");

modified to

_bstr_t bstrSP(L"update_test");

And everythig worked just fine: the update was done and the code outputed the modified row.

And if I change the strings from 'count' to 'anyother string that don't exist in any row' in the stored procedure, using query analizer, the 'Grids' tab is empty and 'Messages' tab shows:

(0 row(s) affected)


(0 row(s) affected)

(as it should).

And that's why your _RecordsetPtr is empty.

So I think your bug is rather in the SP...

Here's my table:

CREATE TABLE [dbo].[misc] (
     [name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [data] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

There is one record (for this test):
name 'count' data '10'

The stored procedure is:
CREATE PROCEDURE update_test AS
UPDATE misc
SET data = data + 1
WHERE name = 'count'

SELECT data FROM misc
WHERE name = 'count'
GO

All of this runs fine interactively.  The C++ code is:
  HRESULT hr = di_conn.CreateInstance("ADODB.Connection");

  char connection[1024] = {0};
  sprintf(connection, "Provider=%s;Server=%s;Database=%;UID=%s;PWD=%s", provider.c_str(), server_name.c_str(), database.c_str(), user.c_str(), password.c_str());
  di_result = di_conn->Open(connection, "", "", ADODB::adConnectUnspecified);
  if(FAILED(di_result))
  {
    return false;
  }

  _bstr_t sp(L"update_test");
  char query[1024] = {0};

  ADODB::_CommandPtr comm;
  _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

  comm.CreateInstance(__uuidof(ADODB::Command));
  comm->ActiveConnection = di_conn;
  comm->CommandText = sp;
  comm->CommandType = ADODB::adCmdStoredProc;
  ADODB::_RecordsetPtr rs = comm->Execute(&vtEmpty, &vtEmpty, ADODB::adCmdStoredProc);
  rs->MoveFirst();
  while(! rs->adoEOF)
  {
    _variant_t data = rs->Fields->Item["data"]->Value;
    char status_code[100] = {0};
    sprintf(status_code, "%d", rs->Fields->Item["data"]->Value.intVal);
    rs->MoveNext();
  }
  rs->Close();


I'm actually combining 2 functions here, so I may have missed changing a variable name, but I'm sure you can get the idea.  I'm using SQL Server 2000.  Thanks for your time.

First of all, I don't think that the code you posted is quite the EXACT code:
- db is OK
- SP is OK
- but ubfortunaly I don't have on my SQL Server a db called "%" :), see your connection string...

Any way the code works perfectly, I mean the EXACT code:

#import "C:\Program Files\Common Files\System\ado\msado15.dll" \
     rename_namespace("ADODB") rename( "EOF", "adoEOF" )

// I prefer <iostream> :))
#include <iostream>

void main()
{
     struct InitOle
     {
          InitOle()
          {
               if (FAILED(::CoInitialize(NULL)))
               {
                    std::cout << "Failed to initialize OLE" <<std::endl;
                    return;
               };
          }
          ~InitOle() { ::CoUninitialize();   }
     } _init_InitOle_;

     
     ADODB::_ConnectionPtr     di_conn;

     char connection[1024] = "driver={sql server};server=192.168.1.34;Database=and_test_db;";
     try
     {
          di_conn.CreateInstance("ADODB.Connection");
          di_conn->Open(connection, "", "", ADODB::adConnectUnspecified);
     }
     catch(_com_error &e)
     {
          _bstr_t er = e.Description();
          std::cout << std::endl << (LPCTSTR)er << std::endl;
     }

     _bstr_t sp(L"update_test");

     ADODB::_CommandPtr comm;
     _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

     comm.CreateInstance(__uuidof(ADODB::Command));
     comm->ActiveConnection = di_conn;
     comm->CommandText = sp;
     comm->CommandType = ADODB::adCmdStoredProc;

     ADODB::_RecordsetPtr rs = comm->Execute(&vtEmpty, &vtEmpty, ADODB::adCmdStoredProc);

     rs->MoveFirst();
     while(! rs->adoEOF)
     {
          std::cout << (LPCTSTR)(_bstr_t)rs->Fields->Item["data"]->Value.bstrVal;
          rs->MoveNext();
     }

     rs->Close();
}

So, if the problem persists, what is your EXACT code

Adrian, I used your code (except for changing the connection string), and I'm still throwing an exception on the MoveFirst.  It still works fine (and returns a row) when I run it interactively.  Again, thanks for taking the time for this.  I'm upping the points because of it.

are you sure that the exception is thrown on MoveFirst?
changing the connection string, could mean changing it to a invalid connection string...

Did you try to debug the code step by step to see where the exception is thrown?

MSDN July 2001 "mk:@MSITStore:D:\MSDN-J~1\MSDN\ado270.chm::/htm/mdmthmovefirst.htm" says:
"A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error."

So.. To debug your code I would follow this alogorithm:

IF an exception is thrown
    - debug the C++ code step by step to see where it is thrown
         IF it thrown by MoveFirst() then you probabily have an empty record set; to be sure replace the line
          rs->MoveFirst();
           with the code
          if (rs->adoEOF)
          {
               std::cout << "the recordset is empty";
               return;
          }
          rs->MoveFirst();
              IF your output shows "recordset is empty" then see the stored procedure
         ELSE
               repare the code where the exception is thrown



I'm really out of ideas with this question. what can I say: I used your EXACT db structure, your EXACT SP code, I showed you my EXACT C++ code; and guess what: it works.

Of course you don't help me much if you you don't send me your exact code; and the fact that your db name was "%" tells me that you don't really post the exact code.

Yes, the exception is thrown in MoveFirst (I'm very familiar with C++).  If I move the select statement to be before the update statement in the stored procedure, I get the data just fine.  And I used the last C++ code you posted.  Here it is:

#import "C:\Program Files\Common Files\System\ado\msado15.dll" \
    rename_namespace("ADODB") rename( "EOF", "adoEOF" )

// I prefer <iostream> :))
#include <iostream>

void main()
{
    struct InitOle
    {
         InitOle()
         {
              if (FAILED(::CoInitialize(NULL)))
              {
                   std::cout << "Failed to initialize OLE" <<std::endl;
                   return;
              };
         }
         ~InitOle() { ::CoUninitialize();   }
    } _init_InitOle_;

   
    ADODB::_ConnectionPtr     di_conn;

    //char connection[1024] = "driver={sql server};server=192.168.1.34;Database=and_test_db;";
    char connection[1024] = "provider=SQLOLEDB;server=143.116.88.38;database=OMS;uid=my_user;pwd=my_pwd";
    try
    {
         di_conn.CreateInstance("ADODB.Connection");
         di_conn->Open(connection, "", "", ADODB::adConnectUnspecified);
    }
    catch(_com_error &e)
    {
         _bstr_t er = e.Description();
         std::cout << std::endl << (LPCTSTR)er << std::endl;
    }

    _bstr_t sp(L"update_test");

    ADODB::_CommandPtr comm;
    _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

    comm.CreateInstance(__uuidof(ADODB::Command));
    comm->ActiveConnection = di_conn;
    comm->CommandText = sp;
    comm->CommandType = ADODB::adCmdStoredProc;

    ADODB::_RecordsetPtr rs = comm->Execute(&vtEmpty, &vtEmpty, ADODB::adCmdStoredProc);

    rs->MoveFirst();
    while(! rs->adoEOF)
    {
         std::cout << rs->Fields->Item["data"]->Value.intVal;
         rs->MoveNext();
    }

    rs->Close();
}


If the stored procedure looks like this I get the data:
CREATE PROCEDURE update_test AS
SELECT data + 1 AS data FROM misc
WHERE name = 'count'

UPDATE misc
SET data = data + 1
WHERE name = 'count'
GO

If I switch around the statements, I get an exception on MoveFirst:
CREATE PROCEDURE update_test AS

UPDATE misc
SET data = data + 1
WHERE name = 'count'

SELECT data + 1 AS data FROM misc
WHERE name = 'count'

GO

Only the user id and password were changed to protect the innocent.

any parameters set for your SP from the C++ client code?

What I'm saying is: I would have a chance to solve your problem if I'd, at least, have the same problem.

But there's a very strange thing: from the information you gave me on the SP and the C++ code, everything's working fine.

may be a virus (just kidding :) ); maybe we're working on diffrent ADO versions... but since we're using the same interfaces on the same COM object...

_RecordsetPtr is just a wrapper you could have diffrent versions...

Why do I have the feelling that in fact you are more experienced in C++ & ADO.. :)

I'm using MDAC 2.6, Visual C++ 6.0 SP5, and SQL Server 2000 SP1.  I'm not sure why I'm getting these results, either.  I've worked on several other databases (Informix and Oracle), and this was always a very useful thing to do, so I assumed that SQL Server would be able to do it.  

I do have a decent amount of experience with C++, but I've only been doing ADO for about 8 months now (I always worked on Unix until I took this job in January).  I've been able to successfully implement a few (10 or 15) interfaces into different databases using it, but I've never been able to get this to work, which is why I posted the question.  Again, I do appreciate the help.  It's probably a virus.

we're using the same versions; but the posted code works on my PC; the virus is what of the question (hope you're kidding too);

as you see in my code i don't set any flags, just use the defaults.

maybe you're doing something which I don't know...

we're using the same versions; but the posted code works on my PC; the virus is what of the question (hope you're kidding too); did you try to run the code on diffrent win boxes?

as you see in my code i don't set any flags, just use the defaults.

maybe you're doing something which I don't know...

Adrian, I'm going to give you the points even though I still can't seem to make it work.  Thanks for your help.

You didn't had too do that.. but 10x anyway..
Hope, you'll find your answear, I'm out of ideas..