Microsoft KB Archive/247069

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 13:51, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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