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

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read