You can retrieve column metadata by using either the COLUMNS schema rowset or the IColumnsInfo::GetColumnsInfo() method. Choose the method that best fits your current needs:
In OLE DB, a rowset is a type of cursor over a table that consists of rows and columns. The intersection of each row and column identifies a cell of data. Every cell in a specific column is of the same data type. The OLE DB concept of a table corresponds directly to the SAS data set. A data set consists of variables and observations. A variable defines a cell of data in an observation. Variables are like columns, and observations are like rows. In fact, these OLE DB terms and SAS terms can be used interchangeably. The following table compares the members of the DBCOLUMNINFO structure with the COLUMNS schema rowset columns and describes how they all correspond to SAS constructs:
| DBCOLUMNINFO member | COLUMNS schema rowset column | SAS data model | Comments |
|---|---|---|---|
| pwszName columnid |
COLUMN_NAME | variable name | n/a |
| pTypeInfo | n/a | n/a | Reserved by Microsoft for future use |
| iOrdinal | ORDINAL_POSITION | variable number | n/a |
| dwFlags | n/a | n/a | n/a |
| ulColumnSize | CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH | variable length | Defines the maximum length of this column/variable |
| wType | DATA_TYPE | variable type | Character variables are mapped to DBTYPE_STR, and numeric variables are mapped to DBTYPE_R8 |
| bPrecision | NUMERIC_PRECISION | n/a | n/a |
| bScale | n/a | n/a | n/a |
| n/a | DESCRIPTION | variable label | n/a |
| n/a | IS_NULLABLE | n/a | Indicates that a cell in this column could be set to "missing" |
Members of the DBCOLUMINFO structure and columns in the COLUMNS schema rowset that are not represented in this table either do not map to SAS constructs and are not supported, or they do not map directly to SAS column metadata.
Accessors are managed through the rowset interface IAccessor. The client application uses the IAccessor interface to specify a set of column bindings and to associate them with an accessor handle. The handle can be passed into other rowset methods that manipulate individual rows of data. When you specify column bindings and create accessors, consider these questions:
If you have a DBCOLUMNINFO structure for a column, you can easily transfer that information into a DBBINDING structure, as shown in the following table:
| DBCOLUMNINFO members | DBBINDING members |
|---|---|
| iOrdinal | iOrdinal |
| ulColumnSize | cbMaxLen |
| wType | wType |
| bPrecision | bPrecision |
| bScale | bScale |
The value of iOrdinal must be the same in both the DBBINDING and DBCOLUMNINFO structures. However, the other members in a DBBINDING structure do not have to match the corresponding members that are returned in a DBCOLUMINFO structure. For example, the wType value in the DBBINDING structure can have any meaningful DBTYPE because the data providers support converting data between types as defined by the OLE DB specification. In addition, the values of the DBBINDING members cbMaxLen, bPrecision, and bScale are more closely related to the wType member of the DBBINDING structure than are the corresponding members of the DBCOLUMNINFO structure.
If the column metadata is known at the time that you are creating the client application, then you do not need to query the data provider. Instead, you can build static DBBINDING structures, which can increase run-time performance. To build static DBINDING structures, you must know how to map data set variable attributes to DBBINDING structure members. The following table explains the mapping:
| DBBINDING structure member | Corresponding SAS variable attribute |
|---|---|
| iOrdinal | Set this member to the variable number that you want to bind to. The member should be a value from 1 to N, where N is the number of variables that are defined in the data set. (The value 0 is reserved for the self-bookmark column.) |
| obValue
obLength obStatus |
These three members determine the offset of the column value, its length, and its status of coercion in the buffer that you pass to the provider when you read and update. If the corresponding bit is not set in the dwPart member, these members are ignored. |
| pTypeInfo | Ignored. Reserved for future use. |
| pObject | Unsupported. OLE objects cannot be embedded in SAS data sets. |
| pBindExt | Used for formatting data. |
| dwPart | This bitmask defines which obValue, obLength, and obStatus members are meaningful. See the OLE DB specification for more information about this field. |
| dwMemOwner | This member does not map to any SAS variable attributes. You use as described by the OLE DB specification. |
| eParamIO | Ignored. |
| cbMaxLen | The number of bytes allocated in your buffer for the data value. This is determined by the value of wType. |
| dwFlags | Ignored. |
| wType | A DBTYPE value that indicates the type that is used to represent the data value. This can directly correspond to the SAS variable's type (DBTYPE_WSTR for character variables; DBTYPE_R8 for numeric variables), or it can be any type that the provider can convert the data to. The OLE DB specification lists the valid conversions between DBTYPEs, and the SAS Data Providers support all of these conversions. At run-time you can discover which conversions are allowed by calling the <em> </em>IConvertType::CanConvert() rowset method. |
| bPrecision | The IOM provider only looks at this member when wType is set to DBTYPE_NUMERIC. In this case, this field defines the maximum precision to use when you get the data. It is ignored when you set and update data. |
| bScale | Ignored. |
In the following example, you open a rowset component named pRowset on a known data set named AUTOS, which contains these variables:
Bind MAKE as DBTYPE_BSTR and the remaining variables as DBTYPE_STR. This means that the first column is a fixed length and the other three columns are varying lengths. So, cbMaxLen and obLength are not considered when you bind MAKE, but they are considered when you bind to MODEL, MPG, and COST.
Put the values of MODEL, MPG, and COST into 15-byte buffers. Buffer your consumer output to place the status and value of MAKE first, followed by the lengths, status, and values of MODEL, MPG and COST. The following code fills in an array of DBBINDING structures to match this described binding and calls the appropriate method to create the OLE DB accessor handle:
IAccessor * pAccessor;
DBBINDING rgBindings[4];
DBBINDSTATUS rgStatus[4];
HACCESSOR hAccessor;
ULONG ulOffset;
// Lay out each column in memory.
struct COLUMNDATA
{
DWORD dwLength; // length of data returned
DWORD dwStatus; // status of column
BYTE bData[1]; // data here and beyond
};
// Rounding amount is always a power of two.
#define ROUND_UP( Size, Amount ) (((DWORD)(Size) + ((Amount) - 1)) & ~((Amount) - 1))
// Alignment for placement of each column within memory.
// Rule of thumb is "natural" boundary, i.e. 4-byte member should be
// aligned on address that is multiple of 4.
// Worst case is double or __int64 (8 bytes).
#define COLUMN_ALIGNVAL 8
memset( rgBindings, 0, sizeof( rgBindings ) ); // defensive programming
// Start laying out data at the beginning of our buffer
ulOffset = 0;
rgBindings[0].iOrdinal = 1;
rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS;
rgBindings[0].obValue = ulOffset + offsetof( COLUMNDATA, bData );
rgBindings[0].obStatus = ulOffset + offsetof( COLUMNDATA, dwStatus );
rgBindings[0].wType = DBTYPE_BSTR;
// Account for space taken by actual cell value
ulOffset += sizeof( BSTR ) + offsetof( COLUMNDATA, bData );
ulOffset = ROUND_UP( ulOffset, COLUMN_ALIGNVAL ); // round up for alignment
rgBindings[1].iOrdinal = 2;
rgBindings[1].cbMaxLen = 15;
rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[1].obValue = ulOffset + offsetof( COLUMNDATA, bData );
rgBindings[1].obLength = ulOffset + offsetof( COLUMNDATA, dwLength );
rgBindings[1].obStatus = ulOffset + offsetof( COLUMNDATA, dwStatus );
rgBindings[1].wType = DBTYPE_STR;
// Account for space taken by actual cell value
ulOffset += rgBindings[1].cbMaxLen + offsetof( COLUMNDATA, bData );
ulOffset = ROUND_UP( ulOffset, COLUMN_ALIGNVAL ); // round up for alignment
rgBindings[2].iOrdinal = 3;
rgBindings[2].cbMaxLen = 15;
rgBindings[2].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[2].obValue = ulOffset + offsetof( COLUMNDATA, bData );
rgBindings[2].obLength = ulOffset + offsetof( COLUMNDATA, dwLength );
rgBindings[2].obStatus = ulOffset + offsetof( COLUMNDATA, dwStatus );
rgBindings[2].wType = DBTYPE_STR;
// Account for space taken by actual cell value
ulOffset += rgBindings[2].cbMaxLen + offsetof( COLUMNDATA, bData );
ulOffset = ROUND_UP( ulOffset, COLUMN_ALIGNVAL ); // round up for alignment
rgBindings[3].iOrdinal = 4;
rgBindings[3].cbMaxLen = 15;
rgBindings[3].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[3].obValue = ulOffset + offsetof( COLUMNDATA, bData );
rgBindings[3].obLength = ulOffset + offsetof( COLUMNDATA, dwLength );
rgBindings[3].obStatus = ulOffset + offsetof( COLUMNDATA, dwStatus );
rgBindings[3].wType = DBTYPE_STR;
// Account for space taken by actual cell value
ulOffset += rgBindings[3].cbMaxLen + offsetof( COLUMNDATA, bData );
ulOffset = ROUND_UP( ulOffset, COLUMN_ALIGNVAL ); // round up for alignment
pRowset->QueryInterface( IID_IAccessor, &pAccessor );
pAccessor->CreateAccessor( DBACCESSOR_ROWDATA, // we are binding to columns in a rowset
4, // binding 4 columns
rgBindings,
0, // ignored for rowset bindings
&hAccessor,
rgStatus );
// If the above call fails you can look at rgStatus to identify which column's binding info
// was invalid. When the call succeeds, hAccessor is a valid accessor handle which can be
// used as input to IRowset::GetData(), IRowsetChange::SetData() and IRowsetChange::InsertRow()
// When you are done with the accessor handle, release it.
pAccessor->ReleaseAccessor( hAccessor, 0 );
pAccessor->Release();