FIX: Using ICommandPrepare->Prepare() May Cause ICommand->Execute() not to Report Errors (247069)

MORE INFORMATION

If the query you are executing contains INSERT INTO and it attempts to put a NULL value in a field that is defined as NOT NULL, the call to Execute returns S_OK.

For example, use the following table and code to reproduce the behavior:

Note This code has been tested with SQl Server backend.

Note You must change uid=<username> and pwd=<strong password> to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.

create table nulltest
(
f1 INTEGER NOT NULL
)
				

#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID

#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <oledberr.h>
#include <stddef.h>
#include <ks.h>
#include <atldbcli.h>

typedef struct MYPARAMSTRUCT
{
   long param;
   DWORD status;
} MyParamStruct;

void main()
{


HRESULT			      hr;
CLSID					clsid;
ICommandText 			*pICommandText = NULL;
ICommand 				*pICommand = NULL;
IDBCreateSession 		      *pIDBCreateSession = NULL;
IDBCreateCommand 		      *pIDBCreateCommand = NULL;
IDBInitialize 			*pIDBInitialize = NULL;
IDBProperties 			*pIDBProperties = NULL;
ICommandPrepare			*pICommandPrepare = NULL;
IRowset 				*pIRowset = NULL;
ICommandProperties 	      *pICommandProperties = NULL;
IAccessor 				*pIAccessor;
HACCESSOR				hAccessor;

const			            ULONG nProps = 1;
LONG			            cRowsAffected;
DBBINDSTATUS                  *pRowStatus = NULL;
DBPROPSET		            dbPropSet;
DBPROP		            dbProp[2];
DBPROP		            InitProperties[ nProps ];
DBPROPSET		            rgInitPropSet;
ULONG			            ulErrorBinding[2];


MyParamStruct                 paramstruct;
DBPARAMS                      Params[1];
DBBINDING                     ParamBinding[1];

	dbProp[0].dwPropertyID       = DBPROP_SERVERCURSOR;
	dbProp[0].dwOptions          = DBPROPOPTIONS_REQUIRED;
	dbProp[0].colid              = DB_NULLID;
	V_VT(&(dbProp[0].vValue))    = VT_BOOL;
	V_BOOL(&(dbProp[0].vValue))  = VARIANT_TRUE;

	dbProp[1].dwPropertyID       = DBPROP_COMMITPRESERVE;  
	dbProp[1].dwOptions          = DBPROPOPTIONS_REQUIRED;
	dbProp[1].colid              = DB_NULLID;
	V_VT(&(dbProp[1].vValue))    = VT_BOOL;
	V_BOOL(&(dbProp[1].vValue))  = VARIANT_TRUE;

	dbPropSet.rgProperties       = dbProp;
	dbPropSet.cProperties        = 2;
	dbPropSet.guidPropertySet    = DBPROPSET_ROWSET;

	InitProperties[ 0 ].dwPropertyID = DBPROP_INIT_PROVIDERSTRING;
	InitProperties[ 0 ].vValue.vt = VT_BSTR;
	InitProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	InitProperties[ 0 ].colid = DB_NULLID;
	InitProperties[ 0 ].dwStatus = DBPROPSTATUS_OK;
	InitProperties[ 0 ].vValue.bstrVal = 
	SysAllocString( OLESTR( "SERVER=RoseValley;DATABASE=Northwind;uid=<username>;pwd=<strong password>;" ) );

	rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
	rgInitPropSet.cProperties = nProps;
	rgInitPropSet.rgProperties = InitProperties;

	if( FAILED( hr = CoInitialize( NULL ) ) )
	{
		printf( "CoInitialize failed\n " );
		return;
	}

	hr = CLSIDFromProgID( L"SQLOLEDB", & clsid );

	if( FAILED( hr = CoCreateInstance( clsid, 
		NULL, 
		CLSCTX_INPROC_SERVER,
		IID_IDBInitialize,
		( void ** ) & pIDBInitialize ) ) )
	{
		printf( "CoCreateInstance failed\n " );
		return;
	}

	hr = pIDBInitialize->QueryInterface( IID_IDBProperties,
		( void ** ) & pIDBProperties );
	hr = pIDBProperties->SetProperties( 1, & rgInitPropSet );
	SysFreeString( InitProperties[ 0 ].vValue.bstrVal );
	pIDBProperties->Release();
	if( FAILED( hr = pIDBInitialize->Initialize() ) )
	{
		printf( "Failed to initializa Database\n " );
		return;
	}	
	hr = pIDBInitialize->QueryInterface( IID_IDBCreateSession,
		( void ** ) & pIDBCreateSession );

	if( FAILED( hr = pIDBCreateSession->CreateSession( NULL,
		IID_IDBCreateCommand,
		( IUnknown ** ) & pIDBCreateCommand ) ) )
	{
		printf( "Session Initialization failed\n " );
		return;
	}

	if( FAILED( hr = pIDBCreateCommand->CreateCommand( NULL,
		IID_ICommand,
		( IUnknown ** ) & pICommand ) ) )
	{
		printf( "Create Command failed\n " );
		return;
	}

	pIDBCreateCommand->Release();




paramstruct.param = 0;
paramstruct.status = DBSTATUS_S_ISNULL;

ParamBinding[ 0 ].iOrdinal = 1;
ParamBinding[ 0 ].obValue = offsetof( MyParamStruct, param );
ParamBinding[ 0 ].obLength = 0;
ParamBinding[ 0 ].obStatus = offsetof( MyParamStruct, status );
ParamBinding[ 0 ].pTypeInfo = NULL;
ParamBinding[ 0 ].pObject = NULL;
ParamBinding[ 0 ].pBindExt = NULL;
ParamBinding[ 0 ].dwPart = DBPART_VALUE | DBPART_STATUS;
ParamBinding[ 0 ].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
ParamBinding[ 0 ].eParamIO = DBPARAMIO_INPUT;
ParamBinding[ 0 ].cbMaxLen = 4;
ParamBinding[ 0 ].dwFlags = 0;
ParamBinding[ 0 ].wType = DBTYPE_I4;
ParamBinding[ 0 ].bPrecision = 0;
ParamBinding[ 0 ].bScale = 0;

LPCTSTR wSQLString = OLESTR( "INSERT INTO nulltest values(?)" );

hr = pICommand->QueryInterface( IID_ICommandText, ( void ** )
   & pICommandText );

hr = pICommandText->SetCommandText( DBGUID_DBSQL, wSQLString );

hr = pICommand->QueryInterface( IID_ICommandPrepare, 
   (LPVOID*) &pICommandPrepare);

if( SUCCEEDED( hr ) )
   hr = pICommandPrepare->Prepare( 0 );

pICommandPrepare->Release();

hr = pICommandText->QueryInterface( IID_IAccessor, 
   ( void ** ) & pIAccessor );

hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA,1, 
	ParamBinding, 
	sizeof( ParamBinding ),
	& hAccessor,
	&ulErrorBinding[0] );

Params[ 0 ].pData = & paramstruct;
Params[ 0 ].cParamSets = 1;
Params[ 0 ].hAccessor = hAccessor;
	
hr = pICommand->Execute( NULL, 
	IID_NULL, 
	Params,
	& cRowsAffected, 
	NULL );  //You should receive the error message hr=DB_E_ERRORSOCCURED. Instead, you receive the message S_OK.

	pIAccessor->ReleaseAccessor( hAccessor, NULL );
	pIAccessor->Release();
	pICommandText->Release();
	pICommand->Release();
	pIDBCreateSession->Release();
	pIDBInitialize->Release();
	CoUninitialize();
}


				

The call to Execute returns S_OK when it should return DB_E_ERRORSOCCURED.