|
|
SAS IOM Data Provider |
|
This section describes how metadata for SAS variables maps to metadata for OLE DB columns. It is not directly related to a specific OLE DB task. Rather, it discusses these mappings in a general and practical matter.
In OLE DB, a rowset is a type of cursor over a rectangular table of data. The table consists of rows and columns. The intersection of any particular row and column identifies a cell of data. Every cell in a given 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.
This section will help you understand how SAS constructs like variable names and types are applied to the OLE DB model and implemented in the SAS IOM data provider. You will also look at how column information is exposed by OLE DB and how a client application can bind to columns in a rowset for read/update operations.
Column metadata can be discovered in two ways:
The same kind of information is returned in both methods, but each method has its own strengths. The COLUMNS schema rowset can be used to return column metadata about all columns in all tables that are in the data source, or it can be filtered to return data from a subset of the tables. This enables you to learn about a lot of the details of your data store without having to open explicitly all the data sets that are in the data source component.
The second method is available when you have a rowset that is opened on a specific data set. The GetColumnsInfo method returns the same kind of information that is found in the COLUMNS schema rowset, but it is for a specific data set. Moreover, it is returned in an array of DBCOLUMNINFO structures that can be more efficiently assessed than a schema rowset. the best method is to delay discovery of column metadata until after a rowset has been opened.
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 | |
| pTypeInfo | This member in the DBCOLUMNINFO structure is reserved by Microsoft for future use. | ||
| iOrdinal | ORDINAL_POSITION | variable number | |
| dwFlags | |||
| ulColumnSize | CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH | variable length | This 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 | ||
| bScale | |||
| DESCRIPTION | variable label | ||
| IS_NULLABLE | 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 do not map to SAS constructs and are not supported, or they do not map directly to SAS column metadata.
The OLE DB constructs that are discussed above are used by the provider to return to the client information about the columns that are available. Before reading, updating, or inserting a row in a table, the client must describe to the provider which columns will be manipulated, what type they will be accessed as, and where they will be stored in the client's memory space. The process of communicating this information to the provider is called column binding and is done through the OLE DB accessors.
Accessors are managed through the rowset interface IAccessor. The client uses this interface to specify a set of column bindings and to associate them with an accessor handle. This handle can then be passed into other rowset methods that manipulate individual rows of data. When you specify column bindings and create accessors, the important questions are:
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 structures. The other members in a DBBINDING structure may not necessarily have the same values as the corresponding members returned in a DBCOLUMINFO structure. The wType value in the DBBINDING structure can have any meaningful DBTYPE; the IOM provider supports coercing data between types as defined by the OLE DB specification. The values of the DBBINDING members cbMaxLen, bPrecision, and bScale are more closely related to the wType member of the DBBINDING structure than the corresponding members of the DBCOLUMNINFO structure.
If you are creating a client program that uses a well known data set, there may be no need to query the IOM provider for the data sets column metadata. It may be known at the time the client program is written so that you could build static DBBINDING structures and save some run-time performance. In such a situation it is important to know how to map data set variable attributes to DBBINDING structure members. The following table further explains the basis of this mapping:
| DBBINDING structure member | Corresponding SAS variable attribute |
| iOrdinal | Set this member to the variable number you want to bind to. This should be a value from 1 to N, where N is the number of variables defined in the data set. (The value zero (0) is reserved for the self bookmark column.) |
| obValue
obLength obStatus | These three members determine the offset of the column value, its length and status of coercion (respectively) in the buffer you pass to the provider when you perform reads/updates. These members are ignored if the corresponding bit is not set in the dwPart member. |
| pTypeInfo | Ignored; reserved for future use. |
| pObject | Unsupported; OLE objects can not be embedded in SAS data sets. |
| pBindExt | Used for formatting data. |
| dwPart | This bitmask defines which of the obValue, obLength, and obStatus members are meaningful. See the Microsoft OLE DB Programmer's Reference for more information on the usage of this field. |
| dwMemOwner | Use as described by the OLE DB specification; this member does not map to any SAS variable attributes. |
| eParamIO | Ignored. |
| cbMaxLen | The number of bytes allocated in your buffer for the data value. This is dictated by the value of wType. |
| dwFlags | Ignored. |
| wType | A DBTYPE value that indicates the type used to represent the data value. This may directly correspond to the SAS variable's type (DBTYPE_WSTR for character variables; DBTYPE_R8 for numeric variables), or it may be any type that the provider can convert the data to. The OLE DB specification lists the valid conversions between DBTYPEs; the IOM provider supports all of these conversions. At run-time you can discover which conversions are allowed by calling the 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. |
for further explanation of the binding process, see the following a bit further, let's look at a concrete example. You open a rowset component, pRowset, on a known data set AUTOS that contains the variables
For example, you want to bind the first variable as a DBTYPE_BSTR and the remaining variables as DBTYPE_STR. By the definition of these types, the bound type of the first column is fixed length and the remaining three are varying. 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 latter three variables values into 15 byte buffers. Buffer your consumer output so that you have the status, and value of MAKE first followed by the MODEL, MPG and COST lengths, status and values respectively. The following code fills in an array of DBBINDING structures to match the binding that was described 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();
|
|
SAS IOM Data Provider |
|