How To Insert NULL Data with OLE DB Consumer Templates (260900)

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.