Knowledge Base

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 "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

REFERENCES

For additional information on inserting NULL data, click the article numbers below to view the articles in the Microsoft Knowledge Base:

248799 How To Insert NULL Data with ODBC API Functions

260900 How To Insert NULL Data with OLE DB Consumer Templates

Keywords: kbhowto kbdatabase KB260310

Microsoft KB Archive/260310

Contents

How To Insert NULL Data with OLE DB SDK Interfaces

SUMMARY

MORE INFORMATION

REFERENCES