C++ ADO Stored Procedure Using Command Object

February 14, 2010 muthu C++ Database 0

Stored Procedures are the fastest way of manipulating data inside a database. Using stored procedures in ADO requires a little bit of extra code than a normal SQL execution. It requires the ADO Command object. This Command object can accept any valid parameter types and execute the stored procedures by applying their parameters.

This article deals with how to insert data into a table using a stored procedure. The data types integer, char, date and currency are dealt in here. For the purpose of the sample, the SQL Server database is used. A table has been created with the name TestTable. The stored procedure created is named as spTestTable and its functionality is to insert data into table.

C++ ADO Stored Procedure Using Command Object – Table:

CREATE TABLE [dbo].[TestTable] (
[Person] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [int] NULL ,
[DateofBirth] [datetime] NULL ,
[Salary] [money] NULL
) ON [PRIMARY]
GO

The above table is used for the ADO C++ Stored Procedure sample.

C++ ADO Stored Procedure Using Command Object – Stored Procedure script:

The script generated in SQL Server for the sample is as follows.

CREATE PROCEDURE [dbo].[spTestTable] ( @strCompanyName char(50), @iAge int, @dob datetime, @mSalary money) As Insert into TestTable(Person,Age,DateofBirth,Salary) Values (@strCompanyName, @iAge, @dob, @mSalary);
GO

C++ ADO Stored Procedure Using Command Object – Writing the code in C++:

The following are the steps to write C++ ADO code.

  1. Import the msadoX.dll from the right folder.
  2. Create the connection string.
  3. Open the ADO connection.
  4. Create the C++ ADO Command Object and set the type as adCmdStoredProc.
  5. Append the parameters to the Command object using CreateParameter and Append function.
  6. Execute the command.
  7. Close the connection.

C++ ADO Stored Procedure Using Command Object – Handling different types of Parameters:

Usually handling the parameters will create some issues of syntax and runtime errors. The following set of code snippets explain how to append parameters of different type to the command object.

C++ ADO type – Integer:

A variant should be used to initialize the parameters. Assign VT_I2 to indicate this is an integer parameter and add the parameter by calling Append and CreateParameter for ADO Command object.

VARIANT vIntegerType;
vIntegerType.vt = VT_I2; //Variant type for Integer
vIntegerType.intVal = 10;

pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("IntegerParameter"),adInteger,adParamInput,4,vIntegerType));

C++ ADO type – String:

As usual, a variant should be initialized with VT_BSTR and should be added to the command object using CreateParameter and Append methods.

VARIANT vName;
vName.vt = VT_BSTR; //Variant type for BSTR
vName.bstrVal = _bstr_t("CoderSource C++ ADO Stored Procedure Sample for String");

pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("strCompanyName"),adChar,adParamInput,50,vName));

C++ ADO type – Date:

It is easier to use the OLE class COleDateTime for date type of variables. So the following variable definition initializes this type. The header file Afxdisp.h should be used for this class.

COleDateTime vOleDOB( 2004, 2,1 , 0, 0 , 0 ) ;

C++ ADO type – Currency:

It is easier to use the OLE class COleCurrency for currency type of variables. So the following variable definition initializes this type.The header file Afxdisp.h should be used for this class.

COleCurrency vOleSalary(5000,55);

It is also possible to use the VARIANT types for date and currency types. But it is much easier if we use COleCurrency and COleDateTime because these classes support formatting and string extractions.

C++ ADO Stored Procedure Using Command Object – Sample Program:

The following sample shows how to use the spTestTable stored procedure to insert data into a table. The sample scripts for the TestTable and spTestTable have to be run in the SQL Server first. This will create the required table and stored procedure.

After creating the table and stored procedure, the following sample can be used to insert data into the table.

#include <afxwin.h> //This program uses MFC
#include <Afxdisp.h>
#include <stdio.h>

#import "C:Program FilesCommon FilesSystemADOmsado15.dll"
no_namespace rename("EOF", "EndOfFile")

int main()
{
/*The following variables will be initialized with necessary values and appended to the strSQL values*/
_bstr_t strName;
_bstr_t strAge;
_bstr_t strDOB;
_bstr_t strSalary;

_ConnectionPtr pConn = NULL;
_CommandPtr pCom;
// Define string variables for connection
_bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=username;Password=passwordvalue;Initial Catalog=database;Data Source=(local);Integrated Security=SSPI;");

HRESULT hr = S_OK;

//Initialize the COM Library
CoInitialize(NULL);

try
{
//Create the Connection pointer
hr = pConn.CreateInstance((__uuidof(Connection)));
if(FAILED(hr))
{
printf("Error instantiating Connection objectn");
goto cleanup;
}

//Open the SQL Server connection
hr = pConn->Open(strCon,"","",0);
if(FAILED(hr))
{
printf("Error Opening Database objectn");
goto cleanup;
}

//Create the C++ ADO Command Object
pCom.CreateInstance(__uuidof(Command));
pCom->ActiveConnection = pConn;

//Make the ADO C++ command object to accept stored procedure
pCom->CommandType = adCmdStoredProc ;

//Tell the name of the Stored Procedure to the command object
pCom->CommandText = _bstr_t("dbo.spTestTable");

//Prepare the Name VARIANT for ADO C++ Command Object Parameter
VARIANT vName;
vName.vt = VT_BSTR; //Variant type for BSTR
vName.bstrVal = _bstr_t("CoderSource C++ ADO Stored Procedure Sample");

//Prepare the Age VARIANT for ADO C++ Command Object Parameter
VARIANT vAge;
vAge.vt = VT_I2; //Variant type for Integer
vAge.intVal = 10;

//Prepare the Salary VARIANT for ADO C++ Command Object Parameter
COleCurrency vOleSalary(5000,55);

//Use COleDateTime class for Date type
COleDateTime vOleDOB( 2004, 2,1 , 0, 0 , 0 ) ;

//Add Parameters to the C++ ADO Command Object
//This adds the string parameter
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("strCompanyName"),adChar,adParamInput,50,vName));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("iAge"),adInteger,adParamInput,4,vAge));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("dob"),adDate,adParamInput,8,_variant_t(vOleDOB)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("mSalary"),adCurrency,adParamInput,8,_variant_t(vOleSalary)));

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

pCom->Execute(NULL,NULL,adCmdStoredProc);

printf("Data Added Successfullyn");
//Close the database
pConn->Close();

}

catch(_com_error & e)
{

_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
printf("n Source : %s n Description : %s n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);

}
cleanup:
CoUninitialize();
return 0;
}

The above sample needs to be linked with the MFC Library in the Visual Studio properties dialog. The values high-lighted in blue have to be changed with the local database configurations. The values high-lighted in green can be changed for different table values.