Microsoft KB Archive/260900

= How To Insert NULL Data with OLE DB Consumer Templates =

Article ID: 260900

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 7.01
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q260900



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



MORE INFORMATION
When you use the OLE DB consumer templates to insert NULL data, you must set the status of the input parameter to DBSTATUS_S_ISNULL. This sends the NULL value to the provider, and lets the provider ignore the contents of the value and length portions of the consumer buffer.

The following sample code is an OLE DB consumer application that uses OLE DB consumer template classes to insert a NULL value into a column by using a parameterized query. The application then retrieves all records in the table to verify that the NULL value has been inserted.

To create and run this sample program, perform the following steps:   Use the following script to create a table called &quot;TestTable&quot;: CREATE TABLE TestTable (   ID int IDENTITY (1, 1) NOT NULL Primary Key,    Name char (10) NULL ) GO   Paste the following code in a new Console Application in Visual C++ 6.0:
 * 1) define _ATL_STATIC_REGISTRY
 * 2) define _ATL_DEBUG_QI
 * 3) define _ATL_DEBUG_INTERFACES


 * 1) include 
 * 2) include 


 * 1) define RETURNHR(hr) if(FAILED((HRESULT)hr)) { AtlTraceErrorRecords((HRESULT)hr); return E_FAIL; }

// The user record. class CTestNullParamInAccessor { public: //Parameter variable. CHAR m_InParam[2]; ULONG m_status;

// Parameter binding. BEGIN_PARAM_MAP(CTestNullParamInAccessor) SET_PARAM_TYPE(DBPARAMIO_INPUT) COLUMN_ENTRY_STATUS(1,m_InParam,m_status) END_PARAM_MAP };

class CTestOutputAccessor { public:

// Data elements. CHAR m_Name[10]; ULONG m_status;

// Output binding. BEGIN_COLUMN_MAP(CTestOutputAccessor) COLUMN_ENTRY_STATUS(1,m_Name, m_status) END_COLUMN_MAP };

int main(void) {   HRESULT hr; CDataSource connection; CSession session; CCommand,CNoRowset> cmdTest; // Connect the database, session, and accessors. CoInitialize(NULL); CDBPropSet dbinit(DBPROPSET_DBINIT); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR(&quot;srv&quot;)); dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR(&quot;user_id&quot;)); dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(&quot;password&quot;)); dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR(&quot;database_name&quot;)); dbinit.AddProperty(DBPROP_INIT_MODE, (long) DB_MODE_READWRITE); dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); RETURNHR(hr = connection.Open(&quot;SQLOLEDB.1&quot;,&dbinit))

// Create the session. RETURNHR(hr = session.Open(connection))

// Insert a record with a &quot;NULL&quot; value on the &quot;name&quot; field. // DBSTATUS_S_ISNULL - the OLE DB provider uses a null value // and proceed to next input parameter if there is one. cmdTest.m_status = DBSTATUS_S_ISNULL;

// Open call executes the insert statement. RETURNHR(hr = cmdTest.Open(session, &quot;Insert Into TestTable (Name) Values (?)&quot;)) cmdTest.Close;

// Retrieve the data. CCommand > cmdTest2; RETURNHR(hr = cmdTest2.Open(session, &quot;Select Name from TestTable&quot;)) RETURNHR(hr = cmdTest2.MoveFirst) cout << &quot;NULL Param Insertion Demo&quot; << endl; cout << &quot;=========================\n&quot; << endl; long i=1; do   { cout << &quot;(&quot; << i++ << &quot;)&quot;; if (cmdTest2.m_status == DBSTATUS_S_OK) cout << cmdTest2.m_Name<< endl; else if (cmdTest2.m_status == DBSTATUS_S_ISNULL) cout << &quot;NULL&quot; << endl; else cout << &quot;ERROR&quot; << endl; }while (cmdTest2.MoveNext == S_OK); cmdTest2.Close; session.Close; connection.Close; return S_OK; }                    Compile and run the application.

