How To
Execute user-defined function and process return code (OLE DB)
In this example a user-defined function is executed and the return code is printed. Here is the sample user-defined function used by the application.
drop function fn_RectangleArea
go
CREATE FUNCTION fn_RectangleArea
(@Width int,
@Height int )
RETURNS int
AS
BEGIN
RETURN ( @Width * @Height )
END
GO
Here is a sample code.
/*
Example shows how to execute user-defined functions and process return code value.
*/
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; //No. 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 inParam1;
long inParam2;
} SPROCPARAMS;
void main() {
//The command to execute.
WCHAR* wCmdString = L"{? = CALL fn_RectangleArea(?, ?) }";
// WCHAR* wCmdString = L"EXEC ? = fn_RectangleVolume(?, ?)";
SPROCPARAMS sprocparams = {0,5,10};
//All the initialization stuff in a separate function.
InitializeAndEstablishConnection();
//Let us create a new session 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
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 etc.) in an array of
DBPARAMBINDINFO structures. This information is then used by
SetParameterInfo().
*/
ParamBindInfo[0].pwszDataSourceType = L"DBTYPE_I4";
ParamBindInfo[0].pwszName = NULL; // 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 = NULL; //L"@inparam1";
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 = NULL; // L"@inparam2";
ParamBindInfo[2].ulParamSize = sizeof(long);
ParamBindInfo[2].dwFlags = DBPARAMFLAGS_ISINPUT;
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 info.(SetParameterInfo)\\n";
goto EXIT;
}
//Let us 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;
} //for
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, inParam1);
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, inParam2);
acDBBinding[2].eParamIO = DBPARAMIO_INPUT;
acDBBinding[2].cbMaxLen = sizeof(long);
acDBBinding[2].wType = DBTYPE_I4;
acDBBinding[2].bPrecision = 11;
//Let us 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
specify the parameter values in the command. This structure 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(" Return value = %d\n", sprocparams.lReturnValue);
//we are not interested in the resultset so release it
//without processing.
if (pIRowset != NULL)
pIRowset->Release();
//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 not been released. This can be used for debugging.
cout << "Problem uninitializing\\n";
} //if
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();
} //if 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";
}
/*
Let us 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 user name (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 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 we establish connection to the data source.
if(FAILED(pIDBInitialize->Initialize()))
{
cout << "Problem in initializing\\n";
}
} //end of InitializeAndEstablishConnection.