Microsoft KB Archive/260310

= How To Insert NULL Data with OLE DB SDK Interfaces =

Article ID: 260310

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q260310



SUMMARY
This article demonstrates how to use OLE DB interfaces to insert NULL data into a Microsoft SQL Server table by using a parameterized query.



MORE INFORMATION
When you use the OLE DB interfaces to insert NULL data, you must set the dwPart property in the DBBINDING data structure to DBPART_STATUS. You must also set the status of the input parameter to DBSTATUS_S_ISNULL.

The following sample program has been tested against SQL Server version 7.0. This program inserts a record that contains a NULL value into the ColNull field. To create and run this program, perform the following steps:   Use the following script to create a table called &quot;TestTable&quot; in the Pubs database: CREATE TABLE TestTable (   colid int IDENTITY (1, 1) NOT NULL,    colNull char (10) NULL ) ON [PRIMARY] GO

ALTER TABLE TestTable WITH NOCHECK ADD CONSTRAINT PK_TestTable PRIMARY KEY NONCLUSTERED (       colid    )  ON [PRIMARY]   Paste the following code in a new Console Application in Microsoft Visual C++ 6.0:
 * 1) define UNICODE
 * 2) define _UNICODE
 * 3) define DBINITCONSTANTS
 * 4) define INITGUID


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


 * 1) define STATUS(hr) if(FAILED((HRESULT)hr)) { printf(&quot;Error Occurred.&quot;); return hr; }

int main {   CLSID clsid;

ICommandText * pICommandText; ICommand * pICommand; IDBCreateSession * pIDBCreateSession; IDBCreateCommand * pIDBCreateCommand; IDBInitialize * pIDBInitialize; IRowset * pIRowset; IDBProperties * pIDBProperties; IAccessor * pIAccessorParam; HACCESSOR hAccessorParam;

//Change your update SQL here. LPCTSTR wSQLString = OLESTR( &quot;Insert Into TestTable (colNull) Values (?)&quot; );

const ULONG nProps = 1; DBPROP InitProperties[ nProps ]; DBPROPSET rgInitPropSet;

const ULONG nParams = 1; DBPARAMS Params[ nParams ]; DBBINDSTATUS * pDBBindStatus = NULL;

HROW rghRows; HROW * prghRows = & rghRows; LONG cRowsAffected; DBBINDSTATUS * pRowStatus = NULL;

struct myData {       //char data[11]; DWORD status; };

InitProperties[ 0 ].dwPropertyID = DBPROP_INIT_PROVIDERSTRING; InitProperties[ 0 ].vValue.vt = VT_BSTR;

// Change the connection string in the following with your username and password. // Change your connection string here. InitProperties[ 0 ].vValue.bstrVal = SysAllocString( OLESTR( &quot;server=srv;database=pubs;uid=user_id;pwd=password;&quot; ) ); InitProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[ 0 ].colid = DB_NULLID; InitProperties[ 0 ].dwStatus = DBPROPSTATUS_OK;

rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT; rgInitPropSet.cProperties = nProps; rgInitPropSet.rgProperties = InitProperties;

STATUS( CoInitialize( NULL ) ); STATUS( CLSIDFromProgID( L&quot;SQLOLEDB&quot;, & clsid ) ); STATUS( CoCreateInstance( clsid, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, ( void ** ) & pIDBInitialize ) );

// Set initialization properties. STATUS( pIDBInitialize->QueryInterface(IID_IDBProperties, ( void ** ) & pIDBProperties ) ); STATUS( pIDBProperties->SetProperties( 1, & rgInitPropSet ) );

SysFreeString( InitProperties[ 0 ].vValue.bstrVal ); pIDBProperties->Release;

STATUS( pIDBInitialize->Initialize );

STATUS( pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void ** ) & pIDBCreateSession ) );

STATUS( pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, ( IUnknown ** ) & pIDBCreateCommand ) );

STATUS( pIDBCreateCommand->CreateCommand( NULL, IID_ICommand, ( IUnknown ** ) & pICommand ) );

pIDBCreateCommand->Release;

STATUS( pICommand->QueryInterface( IID_ICommandText, ( void ** ) & pICommandText ) );

STATUS( pICommandText->SetCommandText( DBGUID_DBSQL, wSQLString ) );

STATUS( pICommandText->QueryInterface( IID_IAccessor, ( void ** ) & pIAccessorParam ) );

myData         rgData[1]; DBBINDING      rgBind; rgBind.iOrdinal = 1; rgBind.obValue = 0; rgBind.obLength = 0; rgBind.obStatus = offsetof( myData, status ); rgBind.pTypeInfo = NULL; rgBind.pObject = NULL; rgBind.pBindExt = NULL; rgBind.dwPart  = DBPART_STATUS; rgBind.dwMemOwner = DBMEMOWNER_CLIENTOWNED; rgBind.eParamIO = DBPARAMIO_INPUT; rgBind.cbMaxLen = 0; rgBind.dwFlags = 0;

// Because we are not passing any value for the parameter, this setting is ignored. // We can also pass a valid value for the parameter and set the status to DBSTATUS_S_ISNULL. // This will also work. In this case rgBind.dwPart should be set to DBPART_STATUS | DBPART_VALUE // and uncheck the data part of myData structure.

rgBind.wType = DBTYPE_STR; rgBind.bPrecision = 0; rgBind.bScale = 0; rgData[0].status = DBSTATUS_S_ISNULL;

STATUS( pIAccessorParam->CreateAccessor( DBACCESSOR_PARAMETERDATA, nParams, &rgBind, sizeof( myData ), & hAccessorParam, pRowStatus ) ); Params[ 0 ].pData = rgData; Params[ 0 ].cParamSets = 1; Params[ 0 ].hAccessor = hAccessorParam;

STATUS( pICommandText->Execute(NULL, IID_IRowset, Params, &cRowsAffected, ( IUnknown ** ) & pIRowset ) );

pIAccessorParam->ReleaseAccessor( hAccessorParam, NULL ); pIAccessorParam->Release; if (pIRowset != NULL) {       pIRowset->Release; }   pICommandText->Release; pICommand->Release; pIDBCreateSession->Release; pIDBInitialize->Release; CoUninitialize;

return 0; }                     Compile and run the application.

NOTE: You can use these procedures to pass a NULL parameter with an INSERT or UPDATE statement. However, if you pass a NULL parameter in a WHERE clause to retrieve records, the query does not produce the intended result. For example, if you run the following query Select * from Table1 where fld1=? with a status of DBSTATUS_S_ISNULL for the parameter, it results in the query: Select * from Table1 where fld1 = NULL This query does not produce the intended result; you should run the following query instead: Select * from Table1 where fld1 ISNULL

<div class="references_section">