Microsoft KB Archive/247069

= FIX: Using ICommandPrepare->Prepare May Cause ICommand->Execute not to Report Errors =

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:

http://www.microsoft.com/downloads/details.aspx?familyid=F62F45E9-24ED-4FA6-BD74-8A26606F96D8

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= 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 )
 * 1) define UNICODE
 * 2) define _UNICODE
 * 3) define DBINITCONSTANTS
 * 4) define INITGUID


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include 
 * 7) include 

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= ;pwd= ;" ) );

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.