Using stored procedures with ADO

ActiveX Data Objects (ADO) enables you to write a client application to access and manipulate data in a database server through a provider.
ADO's primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint.
This sample project is for ADODB, an implementation of ADO optimized for use with Microsoft OLE DB providers, including the Microsoft ODBC provider for OLE DB.
Using this we can execute stored procedure, pass arguments and retrieve value. To use this sample you will have to create the two stored procedures mentioned below.
For using this project you need MFC 5.0 OR above + ADO in your machine.


{  
   CString strTmp;
  
   CString m_sdatasource; // Data source name
   CString m_sUserID;     // User Id
   CString m_sPassword;   // Password

   // GET the above values from the user 
   //Without creating Datasource we can use database by the following   code
   /* strTmp.Format( "driver={sql server};"
                           "server=%s;"
                           "Database=%s;""UID=%s;""PWD=%s;",
                           m_server,m_sdatabase,m_sUserID,m_sPassword );*/

   strTmp.Format( "dsn=%s;""UID=%s;""PWD=%s;",m_sdatasource,m_sUserID,m_sPassword );
   _bstr_t         bstrSQLServerConnect;
   _bstr_t bstrProc =( L"sp_StartByteImport" );; //Stored procedure name
   _variant_t Final;
   bstrSQLServerConnect = (LPCTSTR) strTmp;
   m_status="Empty File";
   _ConnectionPtr  Conn1; // connection object pointer
   _CommandPtr     Cmd1;  // command object pointer
   _RecordsetPtr   Rs1; // recordset object pointer
   bool            bvalid = false;
   try
   {
      Conn1.CreateInstance( __uuidof( Connection ) ); // Instantiating connection object
	  Conn1->ConnectionString = bstrSQLServerConnect; // giving the sqlconnection
	  Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty ); // open the connection object
	  Cmd1.CreateInstance( __uuidof( Command ) ); // creating command object
	  Cmd1->ActiveConnection = Conn1;             // giving the connection handle
	  Cmd1->CommandText      = _bstr_t( bstrProc ); // passing the stored procedue
	  Cmd1->CommandType      = adCmdStoredProc;     // type
	  Cmd1->Parameters->Refresh();                 // passing string value as argument to stored procedure
	  Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (LPCTSTR)m_sfilename );
	  Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown ); // executing the stored procedure and storing the recordset value
	  bvalid = true;
	  Final  = Rs1->Fields->GetItem( _variant_t( 0L ) )->Value; // getting the first column value of the result row
	  strTmp.Format( "%s", CrackStrVariant( Final) ); // to see the value
	  // put your code to see all column values   
   }
   catch( CException *e ) // trapping all error messages
   {
	  TCHAR    szCause[255];    
      e->GetErrorMessage(szCause, 255);
	  m_status=szCause;
   }
   catch( _com_error &e )
   {
	m_status=e.ErrorMessage( );
   }
   catch(...)
   {
	m_status="Error while executing the Import";

   }
    //we need to create the stored procedures below before running the application
	//CREATE PROCEDURE sp_AddAccountingInfo @nfinal int, @pcDate datetime,
	//@pcURL varchar (250), @pcTop varchar (250),
	//@pcQueryString varchar (250), @pcBytes int, @pcRequests int AS
       /*
		Do your operation here
	*/
	//CREATE PROCEDURE sp_AddAccountingInfo
	//@nfinal int,
	//@pcDate datetime,
	//@pcURL varchar (250),
	//@pcTop varchar (250),
	//@pcQueryString varchar (250),
	//@pcBytes int,
	//@pcRequests int
	//AS
	/*
		Put your code here
	*/
}

Download demo project - [125 KB] KB



Comments

  • given a table name how to get the names of triggers and procedures associated with the table

    Posted by Legacy on 11/05/2003 12:00am

    Originally posted by: josh

    given a table name how to get the names of triggers and procedures associated with the table

    Reply
  • how do in Oracle

    Posted by Legacy on 11/20/2002 12:00am

    Originally posted by: weijsh

    Hi,
    I have tried using your code with Oracle, and am having problems with things like specifying the input/output parameters.

    Has anyone had any success calling Oracle Stored procedures?

    thanks

    Reply
  • ADO - VC++ Calling Stored Procedures with Parameters

    Posted by Legacy on 11/07/2002 12:00am

    Originally posted by: Chuck Iyer

    Thanks Shellie, I will try with your example. Regards.

    Chuck

    Reply
  • ADO - VC++ Calling Stored Procedures with Parameters

    Posted by Legacy on 11/05/2002 12:00am

    Originally posted by: Chuck Iyer

    Is there someone out there who has an example of a VC++ program which uses ADO to call a Stored Procedure with Parameters. I seem to have created the individual parameters and I think I have created the "PARAMETERS" by appending the individual paremeters. I have set the command type to adCmdStoredProcedure and the cmdText to the name of the Stored Procedure. Do I have to use the Execute command and if so what parameters do I pass it? Help would be appreciated. Thanks.
    

    Reply
  • Can I create database in Visual C++?

    Posted by Legacy on 08/14/2002 12:00am

    Originally posted by: kong siang

    Can i create database in Visual C++? Do you have any idea how to do it? Can i create the database using the SQL statement or any classes?

    Reply
  • Thanks

    Posted by Legacy on 07/29/2002 12:00am

    Originally posted by: Max

    I found it very helpful !!

    Reply
  • Nice One

    Posted by Legacy on 07/18/2002 12:00am

    Originally posted by: Sujit Kumar Behera

    The code is extremely convinient...for devlopers like me,who are new to the world of MFC..
    
    

    Comments are also explained nicely...

    Thank U...

    Reply
  • Calling Stored Procedures Using CT Libraries

    Posted by Legacy on 01/02/2002 12:00am

    Originally posted by: Venugopal

    Hi,
    
    Can anybody tell me how do I call a stored procedure using CT Libraries? I need to display the data returned by the Stored Procedure in a DBGrid Control.

    Thanks,

    Venu.

    Reply
  • ADO Recordset

    Posted by Legacy on 11/07/2001 12:00am

    Originally posted by: hemant

    I have a recorset, I want to convert the row column format in the recordset into column row format.
    Is this possible, if yes, How? Please explain in detail.

    Reply
  • ImportDlg.cpp(644) : error C2660: 'Open' : function does not take 3 parameters

    Posted by Legacy on 11/02/2001 12:00am

    Originally posted by: abche1

    ImportDlg.cpp(644) : error C2660: 'Open' : function does not take 3 parameters

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • CentreCorp is a fully integrated and diversified property management and real estate service company, specializing in the "shopping center" segment, and is one of the premier retail service providers in North America. Company executives travel a great deal, carrying a number of traveling laptops with critical current business data, and no easy way to back up to the network outside the office. Read this case study to learn how CentreCorp implemented a suite of business continuity services that included …

Most Popular Programming Stories

More for Developers

RSS Feeds