Openning a Stored Procedures with CRecordset 
Author Message
 Openning a Stored Procedures with CRecordset

Here is an example of how to open a recordset and return some data. Now, I
would like to bring it one step further and use a stored procedure instead
of the SQL statement (SELECT * FROM User). I have been looking into BOL, and
I cannot find what I need. I just want to replace
s.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) ); with this
 rs.Open( CRecordset::forwardOnly, _T( "usp_FetchAllUsers" ) ); which would
have the SELECT * FROM User in it. I can use a stored procedure that does
updates and inserts (db.ExecuteSQL), but nothing that return data.

So many thanks in advance,

Richard

----------------------------------------------------------------------------
--------------------
 CDatabase db;

 db.OpenEx( _T("driver={SQL Server};server=.;database=MyDB;uid=sa;pwd=;"));

 db.ExecuteSQL(_T("UPDATE User SET Name = 'Username' WHERE UserID = 1000"));

 CRecordset rs( &db );

 rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) );

 CDBVariant csStr;
 CODBCFieldInfo fieldinfo ;
 short nFields = rs.GetODBCFieldCount( );
 while( !rs.IsEOF( ) )
 {
    for( short index = 0; index < nFields; index++ )
    {
    rs.GetFieldValue( index, csStr );
    cout << index << _T("  -  ");

    rs.GetODBCFieldInfo( index, fieldinfo);
    cout << (LPCTSTR)fieldinfo.m_strName << _T(" : ");
    if(csStr.m_dwType == DBVT_STRING){
   cout << (LPCTSTR)*csStr.m_pstring ;
    }
    cout << endl ;
    }
    rs.MoveNext();
 }
 rs.Close();
 db.Close();

Quote:
}

----------------------------------------------------------------------------
--------------------


Sun, 21 Dec 2003 01:00:10 GMT  
 Openning a Stored Procedures with CRecordset
Hi there,

If you are using ADO i suggest you to take a look at the following article:
http://www.codeproject.com/database/simpleado.asp#xx34833xx

Cheers,
--
M. Shokuie Nia,
MSVC++ Programmer,

Quote:

> Here is an example of how to open a recordset and return some data. Now, I
> would like to bring it one step further and use a stored procedure instead
> of the SQL statement (SELECT * FROM User). I have been looking into BOL,
and
> I cannot find what I need. I just want to replace
> s.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) ); with this
>  rs.Open( CRecordset::forwardOnly, _T( "usp_FetchAllUsers" ) ); which
would
> have the SELECT * FROM User in it. I can use a stored procedure that does
> updates and inserts (db.ExecuteSQL), but nothing that return data.

> So many thanks in advance,

> Richard

> --------------------------------------------------------------------------
--
> --------------------
>  CDatabase db;

>  db.OpenEx( _T("driver={SQL

Server};server=.;database=MyDB;uid=sa;pwd=;"));

- Show quoted text -

Quote:

>  db.ExecuteSQL(_T("UPDATE User SET Name = 'Username' WHERE UserID =
1000"));

>  CRecordset rs( &db );

>  rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) );

>  CDBVariant csStr;
>  CODBCFieldInfo fieldinfo ;
>  short nFields = rs.GetODBCFieldCount( );
>  while( !rs.IsEOF( ) )
>  {
>     for( short index = 0; index < nFields; index++ )
>     {
>     rs.GetFieldValue( index, csStr );
>     cout << index << _T("  -  ");

>     rs.GetODBCFieldInfo( index, fieldinfo);
>     cout << (LPCTSTR)fieldinfo.m_strName << _T(" : ");
>     if(csStr.m_dwType == DBVT_STRING){
>    cout << (LPCTSTR)*csStr.m_pstring ;
>     }
>     cout << endl ;
>     }
>     rs.MoveNext();
>  }
>  rs.Close();
>  db.Close();
> }
> --------------------------------------------------------------------------
--
> --------------------



Mon, 22 Dec 2003 15:57:55 GMT  
 Openning a Stored Procedures with CRecordset
If you use ADO you need a _CommandPtr object to call stored procedures.
If you use DAO you need a CQueryDef object.
Quote:

> Here is an example of how to open a recordset and return some data. Now, I
> would like to bring it one step further and use a stored procedure instead
> of the SQL statement (SELECT * FROM User). I have been looking into BOL, and
> I cannot find what I need. I just want to replace
> s.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) ); with this
>  rs.Open( CRecordset::forwardOnly, _T( "usp_FetchAllUsers" ) ); which would
> have the SELECT * FROM User in it. I can use a stored procedure that does
> updates and inserts (db.ExecuteSQL), but nothing that return data.

> So many thanks in advance,

> Richard

> ----------------------------------------------------------------------------
> --------------------
>  CDatabase db;

>  db.OpenEx( _T("driver={SQL Server};server=.;database=MyDB;uid=sa;pwd=;"));

>  db.ExecuteSQL(_T("UPDATE User SET Name = 'Username' WHERE UserID = 1000"));

>  CRecordset rs( &db );

>  rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) );

>  CDBVariant csStr;
>  CODBCFieldInfo fieldinfo ;
>  short nFields = rs.GetODBCFieldCount( );
>  while( !rs.IsEOF( ) )
>  {
>     for( short index = 0; index < nFields; index++ )
>     {
>     rs.GetFieldValue( index, csStr );
>     cout << index << _T("  -  ");

>     rs.GetODBCFieldInfo( index, fieldinfo);
>     cout << (LPCTSTR)fieldinfo.m_strName << _T(" : ");
>     if(csStr.m_dwType == DBVT_STRING){
>    cout << (LPCTSTR)*csStr.m_pstring ;
>     }
>     cout << endl ;
>     }
>     rs.MoveNext();
>  }
>  rs.Close();
>  db.Close();
> }
> ----------------------------------------------------------------------------
> --------------------



Tue, 23 Dec 2003 00:00:02 GMT  
 Openning a Stored Procedures with CRecordset
Actually I am trying to use the MFC libs. Not ADO, I am trying to get away
from ADO, building some sort of small ADO, lighter usually mean faster.
After some discussion with colleges in here, I have been discourage from
using the MFC.
    MFC = Feature Rich = Piggy performance...
I have been recommended to go straitgh to SQL ODBC Direct (API calls).

In the example below I was hopping I could just change the following line:

  CRecordset rs( &db );
   rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" )

with

  CRecordset rs( &db );
   rs.Open( CRecordset::forwardOnly, _T( "exec usp_FetchAllUsers" )

and nothing else to change. But from what I understand, it is just not that
easy, I will have to start dealing with Commands and parameters just like
ADO, which is what I am trying to avoid. I just need to have a way to
quickly and easily involque a SP and get the data out of there, that's all.

So if i understand what you are saying here, assuming I don't want to use
ADO, I would use CDAODatabase and then CQueryDef. Using CQueryDef, I would
then still have to declare all the parameters of the Stored proc. and then
start using it once defined.

Thanks you all for you replies,

Richard


Quote:
> If you use ADO you need a _CommandPtr object to call stored procedures.
> If you use DAO you need a CQueryDef object.


> > Here is an example of how to open a recordset and return some data. Now,
I
> > would like to bring it one step further and use a stored procedure
instead
> > of the SQL statement (SELECT * FROM User). I have been looking into BOL,
and
> > I cannot find what I need. I just want to replace
> > s.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) ); with this
> >  rs.Open( CRecordset::forwardOnly, _T( "usp_FetchAllUsers" ) ); which
would
> > have the SELECT * FROM User in it. I can use a stored procedure that
does
> > updates and inserts (db.ExecuteSQL), but nothing that return data.

> > So many thanks in advance,

> > Richard

> --------------------------------------------------------------------------
--
> > --------------------
> >  CDatabase db;

> >  db.OpenEx( _T("driver={SQL

Server};server=.;database=MyDB;uid=sa;pwd=;"));

- Show quoted text -

Quote:

> >  db.ExecuteSQL(_T("UPDATE User SET Name = 'Username' WHERE UserID =
1000"));

> >  CRecordset rs( &db );

> >  rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) );

> >  CDBVariant csStr;
> >  CODBCFieldInfo fieldinfo ;
> >  short nFields = rs.GetODBCFieldCount( );
> >  while( !rs.IsEOF( ) )
> >  {
> >     for( short index = 0; index < nFields; index++ )
> >     {
> >     rs.GetFieldValue( index, csStr );
> >     cout << index << _T("  -  ");

> >     rs.GetODBCFieldInfo( index, fieldinfo);
> >     cout << (LPCTSTR)fieldinfo.m_strName << _T(" : ");
> >     if(csStr.m_dwType == DBVT_STRING){
> >    cout << (LPCTSTR)*csStr.m_pstring ;
> >     }
> >     cout << endl ;
> >     }
> >     rs.MoveNext();
> >  }
> >  rs.Close();
> >  db.Close();
> > }

> --------------------------------------------------------------------------
--
> > --------------------



Tue, 23 Dec 2003 00:56:59 GMT  
 Openning a Stored Procedures with CRecordset
Syntax for calling a stored procedure with a CRecordset  (Extract from MSDN
Help):

rs.Open( CRecordset::forwardOnly, _T( "{call MyProcedure}")

I guess that  call a stored procedure with parameter would look like :

rs.Open( CRecordset::forwardOnly, _T( "{call MyProcedure
ConvertedParameterValue1}")

Quote:

> Actually I am trying to use the MFC libs. Not ADO, I am trying to get away
> from ADO, building some sort of small ADO, lighter usually mean faster.
> After some discussion with colleges in here, I have been discourage from
> using the MFC.
>     MFC = Feature Rich = Piggy performance...
> I have been recommended to go straitgh to SQL ODBC Direct (API calls).

> In the example below I was hopping I could just change the following line:

>   CRecordset rs( &db );
>    rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" )

> with

>   CRecordset rs( &db );
>    rs.Open( CRecordset::forwardOnly, _T( "exec usp_FetchAllUsers" )

> and nothing else to change. But from what I understand, it is just not that
> easy, I will have to start dealing with Commands and parameters just like
> ADO, which is what I am trying to avoid. I just need to have a way to
> quickly and easily involque a SP and get the data out of there, that's all.

> So if i understand what you are saying here, assuming I don't want to use
> ADO, I would use CDAODatabase and then CQueryDef. Using CQueryDef, I would
> then still have to declare all the parameters of the Stored proc. and then
> start using it once defined.

> Thanks you all for you replies,

> Richard



> > If you use ADO you need a _CommandPtr object to call stored procedures.
> > If you use DAO you need a CQueryDef object.


> > > Here is an example of how to open a recordset and return some data. Now,
> I
> > > would like to bring it one step further and use a stored procedure
> instead
> > > of the SQL statement (SELECT * FROM User). I have been looking into BOL,
> and
> > > I cannot find what I need. I just want to replace
> > > s.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) ); with this
> > >  rs.Open( CRecordset::forwardOnly, _T( "usp_FetchAllUsers" ) ); which
> would
> > > have the SELECT * FROM User in it. I can use a stored procedure that
> does
> > > updates and inserts (db.ExecuteSQL), but nothing that return data.

> > > So many thanks in advance,

> > > Richard

> > --------------------------------------------------------------------------
> --
> > > --------------------
> > >  CDatabase db;

> > >  db.OpenEx( _T("driver={SQL
> Server};server=.;database=MyDB;uid=sa;pwd=;"));

> > >  db.ExecuteSQL(_T("UPDATE User SET Name = 'Username' WHERE UserID =
> 1000"));

> > >  CRecordset rs( &db );

> > >  rs.Open( CRecordset::forwardOnly, _T( "SELECT * FROM User" ) );

> > >  CDBVariant csStr;
> > >  CODBCFieldInfo fieldinfo ;
> > >  short nFields = rs.GetODBCFieldCount( );
> > >  while( !rs.IsEOF( ) )
> > >  {
> > >     for( short index = 0; index < nFields; index++ )
> > >     {
> > >     rs.GetFieldValue( index, csStr );
> > >     cout << index << _T("  -  ");

> > >     rs.GetODBCFieldInfo( index, fieldinfo);
> > >     cout << (LPCTSTR)fieldinfo.m_strName << _T(" : ");
> > >     if(csStr.m_dwType == DBVT_STRING){
> > >    cout << (LPCTSTR)*csStr.m_pstring ;
> > >     }
> > >     cout << endl ;
> > >     }
> > >     rs.MoveNext();
> > >  }
> > >  rs.Close();
> > >  db.Close();
> > > }

> > --------------------------------------------------------------------------
> --
> > > --------------------



Tue, 23 Dec 2003 20:00:03 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Calling store procedure using CDatabase/CRecordset

2. CRecordset and stored procedures with params that default to NULL

3. Stored procedures accessing more than one server in a single CDatabase and CRecordset object

4. CRecordset updated via stored procedure

5. Getting the return value of a stored procedure using CRecordset

6. CRecordSet/Stored Procedure Problems

7. stored procedures and CRecordset

8. Populating a CRecordset from a stored procedure

9. CRecordset and stored procedure return code

10. CRecordset and output params from a stored procedure

11. CRecordset & Store Procedure

12. CRecordset - stored procedure

 

 
Powered by phpBB® Forum Software