Microsoft KB Archive/247069

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


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:

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