This article demonstrates how to use OLE DB interfaces to insert NULL data into a Microsoft SQL Server table by using a parameterized query.
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 "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]
- 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;
}
- 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
For additional information on inserting NULL data, click the article numbers below
to view the articles in the Microsoft Knowledge Base:
248799Â
(http://kbalertz.com/Feedback.aspx?kbNumber=248799/EN-US/
)
How To Insert NULL Data with ODBC API Functions
260900Â
(http://kbalertz.com/Feedback.aspx?kbNumber=260900/EN-US/
)
How To Insert NULL Data with OLE DB Consumer Templates