Microsoft KB Archive/260900

Knowledge Base

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:

1. Use the following script to create a table called "TestTable":

   CREATE TABLE TestTable (
       ID int IDENTITY (1, 1) NOT NULL Primary Key,
       Name char (10) NULL 
   ) 
   GO
                       

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

   #define _ATL_STATIC_REGISTRY 
   #define _ATL_DEBUG_QI   
   #define _ATL_DEBUG_INTERFACES
   
   #include <atldbcli.h>
   #include <iostream.h>
   
   #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<CAccessor< CTestNullParamInAccessor >,CNoRowset> cmdTest;
       // Connect the database, session, and accessors.
       CoInitialize(NULL);
       CDBPropSet  dbinit(DBPROPSET_DBINIT);
       dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("srv"));
       dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("user_id"));
       dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("password"));
       dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("database_name"));
       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("SQLOLEDB.1",&dbinit))
   
       // Create the session.
       RETURNHR(hr = session.Open(connection))
   
       // Insert a record with a "NULL" value on the "name" 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, "Insert Into TestTable (Name) Values (?)"))
       cmdTest.Close();
   
       
       // Retrieve the data.
       CCommand<CAccessor< CTestOutputAccessor > > cmdTest2;
       RETURNHR(hr = cmdTest2.Open(session, "Select Name from TestTable"))
       
       RETURNHR(hr = cmdTest2.MoveFirst())
       cout << "NULL Param Insertion Demo" << endl;
       cout << "=========================\n" << endl;
       long i=1;
       do
       {
          cout << "(" << i++ << ")";
          if (cmdTest2.m_status == DBSTATUS_S_OK)
           cout << cmdTest2.m_Name<< endl;
          else if (cmdTest2.m_status == DBSTATUS_S_ISNULL)
           cout << "NULL" << endl;
          else
           cout << "ERROR" << endl;
       }while (cmdTest2.MoveNext() == S_OK);
       cmdTest2.Close();
       session.Close( );
       connection.Close();
       return S_OK;
   }
                       

3. Compile and run the application.

REFERENCES

For additional information on OLE DB consumer templates, refer to the Platform SDK documentation.

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

260310 How To Insert NULL Data with OLEDB SDK Interfaces

248799 How To Insert NULL Data with ODBC API Functions

Keywords: kbhowto KB260900

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.