We help IT Professionals succeed at work.

ADO Problem

mnashadka
mnashadka asked
on
I'm trying to open a _RecordsetPtr object on a stored procedure.  If a stored procedure does nothing but select statements, this works just fine.  But if the stored procedure does some modification (insert, update, or delete) and then returns a recordset from a select statement, it looks like the recordset is open but I can't retrieve any rows.  Does anyone know why?  Is it maybe a flag or something that I'm missing?  Any help would be greatly appreciated.
Comment
Watch Question

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

Author

Commented:
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.
sorry for the above answear.. :) didn't pay attension to "then returns a recordset from a select statement"

but, anyway everything seems to work, just fine. Here's the stored precedure:

CREATE PROCEDURE sp_mysp AS
UPDATE TableName SET nume='Another name' WHERE id=678
SELECT * FROM TabelName
GO

And here's the code:

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

#include <iostream>

void main()
{
     struct InitOle
     {
          InitOle()  { ::CoInitialize(NULL); }
          ~InitOle() { ::CoUninitialize();   }
     } _init_InitOle_;

     _ConnectionPtr con;
     _bstr_t     bstrConnect(L"driver={sql server};server=192.168.1.34;Database=dbname;" );
     _bstr_t bstrSP(L"sp_mysp");
     _RecordsetPtr rs;
     _CommandPtr comm;
     
     _bstr_t bstrEmpty("");
     _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

     try
     {
          con.CreateInstance(__uuidof(Connection));
          con->ConnectionString = bstrConnect;
          con->Open(bstrEmpty, bstrEmpty, bstrEmpty, -1);
         
          comm.CreateInstance(__uuidof(Command));
          comm->ActiveConnection = con;
          comm->CommandText = bstrSP;
          comm->CommandType = adCmdStoredProc;
          rs = comm->Execute(&vtEmpty, &vtEmpty, adCmdStoredProc);

          long limit = rs->GetFields()->Count;
     
          for (long x = 0; x < limit; x++)
               std::cout << ((LPCTSTR) rs->GetFields()->Item[x]->Name) << '\t';

          rs->MoveFirst();
          while (!rs->adoEOF)
          {
               for (x = 0; x < limit; x++)
                    std::cout << ((LPCTSTR) (_bstr_t)rs->GetFields()->Item[x]->Value) << '\t';
               rs->MoveNext();
               std::cout << std::endl;
          }

          rs->Close();

          std::cout << "\n\nEverything's just fine.." << std::endl;
     }
     catch(...)
     {

          std::cout << "Some error..";

     }
}


And the ouput is ok; the code is diplayng all rows fron TableName, with the field

nume='Another name' WHERE id=678

updated

Maybe there's another problem in your code...
If your problem persists, post some sources, please.

Adi

Author

Commented:
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...

Author

Commented:
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

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
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.. :)

Author

Commented:
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...

Author

Commented:
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..