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
