Writing Missing Values to a Data Set

In this recipe, you learn how to use the IOM and SAS/SHARE providers to write missing values to a data set by using either ADO or OLE DB. Sample code is provided for both interfaces.

Applies to:	SAS IOM Data Provider, SAS/SHARE Data Provider
Implement using:	ADO or OLE DB

Overview

You can write missing values (character or numeric) to a SAS data set using either the ADO or the OLE DB interface.

When you are writing to the ADO interface, missing values of both types can be written to a data set by setting the value in the empty field to Null. For missing character values, you also can set the value of the field to a string that contains all spaces (including the empty string ""). See ADO Details for sample Visual Basic code.

When you are writing to the OLE DB interface, missing values of both types can be written to a data set by setting the dwStatus value of the column in the client's row data buffer to DBSTATUS_S_ISNULL. You do this before passing that buffer to the IRowsetChange::SetData method. For missing character values, you also can 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. See OLE DB Details for sample Visual C++ code.

ADO Details

ADO generally represents missing values of both types as Null. Missing character values also can be represented by a string of spaces.

The following Visual Basic code uses the SAS IOM Data Provider to set every field in the data set to missing. (The IOM provider is running locally, which is done by using "_LOCAL_" as the "Data Source" property value.)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim f As Field

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
cn.Open
rs.LockType = adLockPessimistic
' Note that rs.LockType was set above and
' therefore no LockType is passed to the Open call
rs.Open "sashelp.shoes", cn, adOpenDynamic, , adCmdTableDirect
rs.MoveFirst
While Not rs.EOF
    For Each f in rs.Fields
	If f.Type = adWChar Then 'character value
            f.Value = ""
            ' or f.Value = Null
            ' or f.Value = "       "
        Else 'numeric value
            f.Value = Null
        End If
    Next f
    rs.MoveNext
Wend

OLE DB Details

In OLE DB, you would use dwStatus to enable your application to write a missing value of either type into the data set. In addition, a string of spaces can be written to fill in a missing character value.

The following Visual C++ code is an example of 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.

Here is a summary of the tasks that must be done before the sample code starts:

Call IColumnsInfo::GetColumnInfo to get a DBCOLUMNINFO array and to determine the number of columns in the data set.
Use the DBCOLUMNINFO array to create a DBBINDING array.
Call IAccessor::CreateAccessor to request an accessor.
Call IAccessor::GetBindings to recover the bindings for the created accessor.
Use the bindings to determine how much space to allocate for the row data.
Call IRowsetLocate::GetRowsAt or IRowset::GetNextRows to get a HROW.
Use 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 specification.

hr = pIRowset->GetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }
for (int 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 alternative method for writing character values to a data set is illustrated by the following example.

hr = pIRowset->GetData(hRow, hAccessor, pRowData);
if (FAILED(hr)){ /* an error occurred */ }
for (int 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 */ }

Resources

Writing Missing Values to a Data Set

Overview

ADO Details

OLE DB Details

See Also: