How To Insert NULL Data with OLE DB SDK Interfaces (260310)

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:

1. Use the following script to create a table called "TestTable" 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]
   					

2. Paste the following code in a new Console Application in Microsoft Visual C++ 6.0:

   #define UNICODE
   #define _UNICODE
   #define DBINITCONSTANTS
   #define INITGUID
   
   #include <windows.h>
   #include <stdio.h>
   #include <oledb.h>
   #include <oledberr.h>
   #include <stddef.h>
   
   #define STATUS(hr) if(FAILED((HRESULT)hr)) { printf("Error Occurred."); 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( "Insert Into TestTable (colNull) Values (?)" );
   
   	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( "server=srv;database=pubs;uid=user_id;pwd=password;" ) );
   	
   	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"SQLOLEDB", & 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;
   } 
   					

3. 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