Article ID: 247069
Article Last Modified on 8/7/2006
APPLIES TO
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft OLE DB Provider for SQL Server 7.01
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
This article was previously published under Q247069
SYMPTOMS
When you are trying to execute a query which contains a constraint violation and should return the error message DB_E_ERRORSOCCURED and you call ICommandPrepare->Prepare prior to calling ICommand->Execute, ICommand->Execute returns S_OK.
To work around this problem do not call ICommandPrepare->Prepare.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site at:
For more information, contact your primary support provider.
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= 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.
Keywords: kbbug kbfix kbsqlprog KB247069