Writing Missing Values to a Data Set

Goal

You want your application to write missing values to a data set.

This recipe applies to the SAS/SHARE, IOM, and Base SAS providers. This recipe includes sample code for ADO and OLE DB.

ADO Implementation

To write missing values to a data set, you specify null as the value of the empty field. For missing character values, you can also set the value of the field to a string that contains all spaces (including the empty string " "). The following Visual Basic code sets every field in the data set to missing:

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset
Dim field As Field

' Specify the LockType.
obRecordset.LockType = adLockPessimistic

' The fourth parameter is empty because the LockType has already been specified.
obRecordset.Open "sashelp.shoes", obConnection, adOpenDynamic, , adCmdTableDirect

obRecordset.MoveFirst
While Not obRecordset.EOF
 For Each field in obRecordset.Fields
	If field.Type = adWChar Then 'character value
 field.Value = ""
 ' or field.Value = Null
 ' or field.Value = " "
 Else 'numeric value
 field.Value = Null
 End If
 Next field
 obRecordset.MoveNext
Wend

OLE DB Implementation

To write missing values to a data set, you specify DBSTATUS_S_ISNULL as the value of the dwStatus of the column in the client's row data buffer. You perform this task before passing that buffer to the IRowsetChange::SetData method.

For missing character values, you can also fill the bData member of the column in the client's row data buffer with a null-terminated string of spaces (including the empty string " "). The length of the string of spaces can be up to the length indicated by the dwLength value.

The following C++ code shows how to set all the fields of one observation in a data set to Null. The iteration through the fields starts at 1 rather than 0 because the first column contains the bookmark for that row.

This example assumes that the rowset has been opened in immediate update mode. If a delayed update is used instead, an additional call to IRowsetUpdate::Update must be made before the changes will be accepted. The code assumes that you have completed the following tasks:

called IColumnsInfo::GetColumnInfo to get a DBCOLUMNINFO array and to determine the number of columns in the data set
used the DBCOLUMNINFO array to create a DBBINDING array
called IAccessor::CreateAccessor to request an accessor
called IAccessor::GetBindings to recover the bindings for the created accessor
used the bindings to determine how much space to allocate for the row data
called IRowsetLocate::GetRowsAt or IRowset::GetNextRows to get an HROW
used the HROW and the accessor in the call to GetData

Note: The exact steps for getting a row of data from an OLE DB provider can be found in the OLE DB Programmer’s Reference and Data Access SDK.

hr = pIRowset->GetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }
for (DBORDINAL I = 1; i < cNumColumns; i++)
{
	pCol = (COLUMNDATA*)(pRowData +
	pBindings[i].obLength);pCol->dwStatus = DBSTATUS_S_ISNULL;
}
hr = pIRowset->QueryInterface(IID_IRowsetChange, (void**)&pIRowsetChange);
if (FAILED(hr)){ /* an error occurred */ }
hr = pIRowsetChange->SetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }

The following example shows an alternative method for writing character values to a data set:

hr = pIRowset->GetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }
for (DBORDINAL i = 1; i < cNumColumns; i++)
{
	pCol = (COLUMNDATA*)(pRowData + pBindings[i].obLength);
	if (pBindings[i].wType == DBTYPE_STR)
	{
		memset(pCol->bData, ' ', pCol->dwLength);
	}
	else // numeric column
	{
		pCol->dwStatus = DBSTATUS_S_ISNULL;
	}
}
hr = pIRowset->QueryInterface(IID_IRowsetChange, (void**)&pIRowsetChange);
if (FAILED(hr)){ /* an error occurred */ }
hr = pIRowsetChange->SetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }

Writing Missing Values to a Data Set

Goal

ADO Implementation

OLE DB Implementation

See Also