How to process return codes and output parameters (OLE DB)

Microsoft® SQL Server™ stored procedures can have integer return codes and output parameters. The return codes and output parameters are sent in the last packet from the server and are therefore not available to the application until the rowset is completely released. If the command returns multiple results, output parameter data is available when IMultipleResults::GetResult returns DB_S_NORESULT or the IMultipleResults interface is completely released, whichever occurs first.

To process return codes and output parameters

Construct an SQL statement that uses the ODBC CALL escape sequence. The statement should use parameter markers for each input/output, and output parameter, and for the procedure return value (if any). For input parameters, you can use the parameter markers or hard code the values.
Call the ICommandWithParameters::SetParameterInfo method to describe parameter markers to the provider. Fill in the parameter information in an array of PARAMBINDINFO structures.
Create a set of bindings (one for each parameter maker) by using an array of DBBINDING structure.
Create an accessor for the defined parameters by using the IAccessor::CreateAccessor method. CreateAccessor creates an accessor from a set of bindings.
Fill in the DBPARAMS structure.
Call the Execute command (in this case, a call to a stored procedure).
Process the rowset and release it by using the IRowset::Release method.
Process the return code and output parameter values received from the stored procedure.

This example shows processing a rowset, a return code, and an output parameter. Result sets are not processed. Here is the sample stored procedure used by the application.

USE pubs

DROP PROCEDURE myProc

GO

CREATE PROCEDURE myProc

@inparam int,

@outparam int OUTPUT

AS

SELECT title, price

FROM titles WHERE royalty > @inparam

SELECT @outparam = 100

IF (@outparam > 0)

RETURN 999

ELSE

RETURN 888

GO

Here is the sample code:

void InitializeAndEstablishConnection();

#define UNICODE

#define DBINITCONSTANTS

#define INITGUID

#include <windows.h>

#include <stdio.h>

#include <stddef.h>

#include <iostream.h>

#include <oledb.h>

#include <oledberr.h>

#include <SQLOLEDB.h>

IDBInitialize* pIDBInitialize = NULL;

IDBCreateSession* pIDBCreateSession = NULL;

IDBCreateCommand* pIDBCreateCommand = NULL;

ICommandText* pICommandText = NULL;

IRowset* pIRowset = NULL;

ICommandWithParameters* pICommandWithParams = NULL;

IAccessor* pIAccessor = NULL;

IDBProperties* pIDBProperties = NULL;

WCHAR* pStringsBuffer;

DBBINDING* pBindings;

const ULONG nInitProps = 4;

DBPROP InitProperties[nInitProps];

const ULONG nPropSet = 1;

DBPROPSET rgInitPropSet[nPropSet];

HRESULT hr;

HACCESSOR hAccessor;

const ULONG nParams = 3; //Number of parameters in the command

DBPARAMBINDINFO ParamBindInfo[nParams];

ULONG i;

ULONG cbColOffset = 0;

ULONG ParamOrdinals[nParams];

LONG cNumRows = 0;

DBPARAMS Params;

/*

Declare an array of DBBINDING structures, one for each parameter

in the command.

*/

DBBINDING acDBBinding[nParams];

DBBINDSTATUS acDBBindStatus[nParams];

//The following buffer is used to store parameter values.

typedef struct tagSPROCPARAMS

{

long lReturnValue;

long outParam;

long inParam;

} SPROCPARAMS;

void main() {

//The command to execute.

WCHAR* wCmdString = L"{? = call myProc(?,?)}";

SPROCPARAMS sprocparams = {0,0,14};

//All the initialization activities in a separate function.

InitializeAndEstablishConnection();

//Create a new activity from the data source object.

if(FAILED(pIDBInitialize->QueryInterface(

IID_IDBCreateSession,

(void**) &pIDBCreateSession)))

{

cout << "Failed to access IDBCreateSession interface.\n";

goto EXIT;

}

if(FAILED(pIDBCreateSession->CreateSession(

NULL,

IID_IDBCreateCommand,

(IUnknown**) &pIDBCreateCommand)))

{

cout << "pIDBCreateSession->CreateSession failed.\n";

goto EXIT;

}

//Create a Command object.

if(FAILED(pIDBCreateCommand->CreateCommand(

NULL,

IID_ICommandText,

(IUnknown**) &pICommandText)))

{

cout << "Failed to access ICommand interface.\n";

goto EXIT;

}

//Set the command text.

if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString)))

{

cout << "Failed to set command text.\n";

goto EXIT;

}

/*

Describe the command parameters (parameter name, provider

specific name of the parameter's data type, and so on.) in an array of

DBPARAMBINDINFO structures. This information is then used by

SetParameterInfo().

*/

ParamBindInfo[0].pwszDataSourceType = L"DBTYPE_I4";

ParamBindInfo[0].pwszName = L"ReturnVal"; //return value from sp

ParamBindInfo[0].ulParamSize = sizeof(long);

ParamBindInfo[0].dwFlags = DBPARAMFLAGS_ISOUTPUT;

ParamBindInfo[0].bPrecision = 11;

ParamBindInfo[0].bScale = 0;

ParamOrdinals[0] = 1;

ParamBindInfo[1].pwszDataSourceType = L"DBTYPE_I4";

ParamBindInfo[1].pwszName = L"@inparam";

ParamBindInfo[1].ulParamSize = sizeof(long);

ParamBindInfo[1].dwFlags = DBPARAMFLAGS_ISINPUT;

ParamBindInfo[1].bPrecision = 11;

ParamBindInfo[1].bScale = 0;

ParamOrdinals[1] = 2;

ParamBindInfo[2].pwszDataSourceType = L"DBTYPE_I4";

ParamBindInfo[2].pwszName = L"@outparam";

ParamBindInfo[2].ulParamSize = sizeof(long);

ParamBindInfo[2].dwFlags = DBPARAMFLAGS_ISOUTPUT;

ParamBindInfo[2].bPrecision = 11;

ParamBindInfo[2].bScale = 0;

ParamOrdinals[2] = 3;

//Set the parameters information.

if(FAILED(pICommandText->QueryInterface(

IID_ICommandWithParameters,

(void**)&pICommandWithParams)))

{

cout << "Failed to obtain ICommandWithParameters.\n";

goto EXIT;

}

if(FAILED(pICommandWithParams->SetParameterInfo(

nParams,

ParamOrdinals,

ParamBindInfo)))

{

cout << "Failed in setting parameter

information.(SetParameterInfo)\n";

goto EXIT;

}

/*Describe the consumer buffer by filling in the array

of DBBINDING structures. Each binding associates

a single parameter to the consumer's buffer.*/

for(i = 0; i < nParams; i++)

{

acDBBinding[i].obLength = 0;

acDBBinding[i].obStatus = 0;

acDBBinding[i].pTypeInfo = NULL;

acDBBinding[i].pObject = NULL;

acDBBinding[i].pBindExt = NULL;

acDBBinding[i].dwPart = DBPART_VALUE;

acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;

acDBBinding[i].dwFlags = 0;

acDBBinding[i].bScale = 0;

} //endfor

acDBBinding[0].iOrdinal = 1;

acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);

acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;

acDBBinding[0].cbMaxLen = sizeof(long);

acDBBinding[0].wType = DBTYPE_I4;

acDBBinding[0].bPrecision = 11;

acDBBinding[1].iOrdinal = 2;

acDBBinding[1].obValue = offsetof(SPROCPARAMS, inParam);

acDBBinding[1].eParamIO = DBPARAMIO_INPUT;

acDBBinding[1].cbMaxLen = sizeof(long);

acDBBinding[1].wType = DBTYPE_I4;

acDBBinding[1].bPrecision = 11;

acDBBinding[2].iOrdinal = 3;

acDBBinding[2].obValue = offsetof(SPROCPARAMS, outParam);

acDBBinding[2].eParamIO = DBPARAMIO_OUTPUT;

acDBBinding[2].cbMaxLen = sizeof(long);

acDBBinding[2].wType = DBTYPE_I4;

acDBBinding[2].bPrecision = 11;

//Create an accessor from the above set of bindings.

hr = pICommandWithParams->QueryInterface(

IID_IAccessor,

(void**)&pIAccessor);

if (FAILED(hr))

{

cout << "Failed to get IAccessor interface.\n";

}

hr = pIAccessor->CreateAccessor(

DBACCESSOR_PARAMETERDATA,

nParams,

acDBBinding,

sizeof(SPROCPARAMS),

&hAccessor,

acDBBindStatus);

if (FAILED(hr))

{

cout << "Failed to create accessor for the defined parameters.\n";

}

/*

Fill in DBPARAMS structure for the command execution. This structure

specifies the parameter values in the command and is then passed

to Execute.

*/

Params.pData = &sprocparams;

Params.cParamSets = 1;

Params.hAccessor = hAccessor;

//Execute the command.

if(FAILED(hr = pICommandText->Execute(

NULL,

IID_IRowset,

&Params,

&cNumRows,

(IUnknown **) &pIRowset)))

{

cout << "Failed to execute command.\n";

goto EXIT;

}

printf("After command execution but before rowset processing.\n\n");

printf(" Return value = %d\n", sprocparams.lReturnValue);

printf(" Output parameter value = %d\n", sprocparams.outParam);

printf(" These are the same default values set in the

application.\n\n\n");

/*The result set does not matter in this example, so release it

without processing.*/

pIRowset->Release();

printf("After processing the result set...\n");

printf(" Return value = %d\n", sprocparams.lReturnValue);

printf(" Output parameter value = %d\n\n", sprocparams.outParam);

//Free up memory.

pIAccessor->ReleaseAccessor(hAccessor, NULL);

pIAccessor->Release();

pICommandWithParams->Release();

pICommandText->Release();

pIDBCreateCommand->Release();

pIDBCreateSession->Release();

if(FAILED(pIDBInitialize->Uninitialize()))

{

/*Uninitialize is not required, but it fails if an interface

has not been released. This can be used for debugging.*/

cout << "Problem uninitializing.\n";

} //endif

pIDBInitialize->Release();

//Release COM.

CoUninitialize();

return;

EXIT:

if(pIAccessor != NULL)

pIAccessor->Release();

if(pICommandWithParams != NULL)

pICommandWithParams->Release();

if(pICommandText != NULL)

pICommandText->Release();

if(pIDBCreateCommand != NULL)

pIDBCreateCommand->Release();

if(pIDBCreateSession != NULL)

pIDBCreateSession->Release();

if (pIDBInitialize != NULL)

{

if (FAILED(pIDBInitialize->Uninitialize()))

{

/*Uninitialize is not required, but it fails if an

interface has not been released. This can be used

for debugging.*/

cout << "Problem in uninitializing.\n";

} //if inner

pIDBInitialize->Release();

} //endif outer

CoUninitialize();

};

//---------------------------------------------------------------------

void InitializeAndEstablishConnection()

{

//Initialize the COM library.

CoInitialize(NULL);

//Obtain access to the SQLOLEDB provider.

hr = CoCreateInstance(

CLSID_SQLOLEDB,

NULL,

CLSCTX_INPROC_SERVER,

IID_IDBInitialize,

(void **) &pIDBInitialize);

if (FAILED(hr))

{

cout << "Failed in CoCreateInstance().\n";

}

/*

Initialize the property values needed

to establish the connection.

*/

for(i = 0; i < nInitProps; i++)

VariantInit(&InitProperties[i].vValue);

//Specify server name.

InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;

InitProperties[0].vValue.vt = VT_BSTR;

InitProperties[0].vValue.bstrVal =

SysAllocString(L"server");

InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;

InitProperties[0].colid = DB_NULLID;

//Specify database name.

InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;

InitProperties[1].vValue.vt = VT_BSTR;

InitProperties[1].vValue.bstrVal =

SysAllocString(L"database");

InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;

InitProperties[1].colid = DB_NULLID;

//Specify username (login).

InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;

InitProperties[2].vValue.vt = VT_BSTR;

InitProperties[2].vValue.bstrVal =

SysAllocString(L"login");

InitProperties[3].dwOptions = DBPROPOPTIONS_REQUIRED;

InitProperties[3].colid = DB_NULLID;

//Specify password.

InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;

InitProperties[3].vValue.vt = VT_BSTR;

InitProperties[3].vValue.bstrVal = SysAllocString(L"password");

InitProperties[3].dwOptions = DBPROPOPTIONS_REQUIRED;

InitProperties[3].colid = DB_NULLID;

/*

Now that properties are set, construct the DBPROPSET structure

(rgInitPropSet). The DBPROPSET structure is used to pass an array

of DBPROP structures (InitProperties) to the SetProperties method.

*/

rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;

rgInitPropSet[0].cProperties = 4;

rgInitPropSet[0].rgProperties = InitProperties;

//Set initialization properties.

hr = pIDBInitialize->QueryInterface(

IID_IDBProperties,

(void **)&pIDBProperties);

if (FAILED(hr))

{

cout << "Failed to obtain IDBProperties interface.\n";

}

hr = pIDBProperties->SetProperties(

nPropSet,

rgInitPropSet);

if(FAILED(hr))

{

cout << "Failed to set initialization properties.\n";

}

pIDBProperties->Release();

//Now establish a connection to the data source.

if(FAILED(pIDBInitialize->Initialize()))

{

cout << "Problem in initializing.\n";

}

} //end of InitializeAndEstablishConnection.