Schema Rowsets

Schema rowsets give the user metadata, including such things as

• the tables that exist in the data source
• the columns that are in those tables
• the data types that the provider supports.

The SAS providers support three schema rowsets:

• TABLES
• COLUMNS
• PROVIDER_TYPES

The TABLES schema rowset returns information about which tables (or SAS data sets) are available to the data source. The COLUMNS schema rowset returns information about all of the columns that are within those tables. The PROVIDER_TYPES schema rowset deals with what data types the provider supports, or what data types the provider uses to surface SAS data.

Further details about each schema rowset, including what columns they contain and how they map to SAS constructs, is described in the following section.

The TABLES Schema Rowset

The TABLES schema rowset returns metadata about all of the tables that are in the current data source. Each row in the TABLES schema rowset corresponds to an individual table. A table is a SAS data set.

The TABLES schema rowset contains all nine columns that are mentioned for TABLES in the OLE DB specification. Of those nine, only five columns actually contain data: TABLE_NAME (col. 3), TABLE_TYPE (col. 4), DESCRIPTION (col. 6), DATE_CREATED (col. 8), and DATE_MODIFIED (col. 9). For further information about the Type column that is shown below, see the OLE DB specification.

The value of the TABLE_NAME column is affected by the provider's concept of a data source. For this provider, data source is an IOM server, which encompasses all library names that are known to the server. Thus, libname.membername is returned for TABLE_NAME.

With regard to the TABLE_TYPE column's value, a SAS data set maps to a value of TABLE, while a view maps to VIEW.

Column name	Type	Description
TABLE_NAME	DBTYPE_WSTR	Table name. Value is libname.membername
TABLE_TYPE	DBTYPE_WSTR	Table type. Value is either TABLE or VIEW.
DESCRIPTION	DBTYPE_WSTR	A description of the table. This is the SAS data file's label.
DATE_CREATED	DBTYPE_DATE	Date when table was created.
DATE_MODIFIED	DBTYPE_DATE	Date when table was last modified.

This provider also extends the TABLES schema rowset to provide metadata that is specific to SAS. These extensions are contained in additional columns that are returned following the previously listed columns. For more information, see TABLES Schema-Rowset Extensions.

The COLUMNS Schema Rowset

The COLUMNS schema rowset returns metadata about all of the columns of tables that are in the current data source. Each row in the COLUMNS schema rowset corresponds to one column of a table (or one variable of a SAS data set). The COLUMNS schema rowset contains all 28 columns that are mentioned by the OLE DB specification, but only the following 11 of those 28 columns actually contain data:

• TABLE_NAME
• COLUMN_NAME
• ORDINAL_POSITION
• COLUMN_HASDEFAULT
• COLUMN_FLAGS
• IS_NULLABLE
• DATA_TYPE
• CHARACTER_MAXIMUM_LENGTH
• CHARACTER_OCTET_LENGTH
• NUMERIC_PRECISION
• DESCRIPTION

The COLUMN_FLAGS column is a bitmask that describes the column. The DBCOLUMNFLAGS enumerated type lists all of the the possible bits that could be set in the bitmask. For more information on DBCOLUMNFLAGS, please see the OLE DB specification, under IColumnsInfo::GetColumnInfo. The values of DBCOLUMNFLAGS that the SAS providers may return in the COLUMN_FLAGS column are

• DBCOLUMNFLAGS_ISFIXEDLENGTH - Set if all data in the column has the same length. Otherwise, not set. Thus, set for a column that contains SAS numeric data. Not set for a column that holds SAS character data.
• DBCOLUMNFLAGS_MAYBENULL - Set if the column can contain NULL values. This flag is used by OLE DB consumer programs that are reading data to determine whether or not they might encounter a missing value. (NULL in the OLE DB arena corresponds to missing data in the SAS arena.)
• DBCOLUMNFLAGS_WRITE - Set if IRowsetChange::SetData can be called for the column. Otherwise, not set.

  Column name	Type	Description
  TABLE_NAME	DBTYPE_WSTR	Table name.
  COLUMN_NAME	DBTYPE_WSTR	Name of the column. (Corresponds to the name of the SAS variable.)
  ORDINAL_POSITION	DBTYPE_UI4	Ordinal of the column. Columns are numbered beginning with one. (Corresponds to the SAS variable number.)
  COLUMN_HASDEFAULT	DBTYPE_BOOL	VARIANT_TRUE if column has a default value; VARIANT_FALSE if column does not have a default value or it is unknown whether the column has a default value.
  COLUMN_FLAGS	DBTYPE_UI4	A bitmask that describes the column. See above for details.
  IS_NULLABLE	DBTYPE_BOOL	VARIANT_TRUE if the column can be set to NULL; VARIANT_FALSE if the column cannot be set to NULL (which corresponds to a SAS missing value).
  DATA_TYPE	DBTYPE_UI2	The data type of the column.
  CHARACTER_MAXIMUM_LENGTH	DBTYPE_UI4	The maximum possible length of a value in the column, in characters.
  CHARACTER_OCTET_LENGTH	DBTYPE_UI4	The column value's maximum length, in bytes.
  NUMERIC_PRECISION	DBTYPE_UI2	For SAS numeric data columns, this is the column's maximum precision.
  DESCRIPTION	DBTYPE_WSTR	A description of the column. This is the SAS column's label.

  The IOM provider also extends the COLUMNS schema rowset to provide metadata that is specific to SAS. These extensions are contained in additional columns returned after the previously listed columns. For more information, see COLUMNS Schema-Rowset Extensions.

  PROVIDER_TYPES Schema Rowset

  The PROVIDER_TYPES schema rowset returns metadata about which data types are returned by the provider. There is one row in PROVIDER_TYPES for each data type. The provider currently returns just two data types: SAS numeric data and SAS character data. Thus, the provider currently has only two rows in PROVIDER_TYPES. For this provider, SAS numeric data is returned as a DBTYPE_R8, and SAS character data is returned as a DBTYPE_WSTR. For definitions of these DBTYPE values, please see the Microsoft OLE DB Programmer's Reference regarding "Type Indicators."

  PROVIDER_TYPES contains all 20 columns that are mentioned by the OLE DB specification but only the following 15 of those 20 columns actually contain data:

  Column name	Type	Description
  TYPE_NAME	DBTYPE_WSTR	A name for the data type. Value: either "num" or "char".
  DATA_TYPE	DBTYPE_UI2	The DBTYPE value that corresponds to this data type.
  COLUMN_SIZE	DBTYPE_UI4	For SAS numeric data, the maximum precision of this data type. For SAS character data, the maximum length of such a column in characters.
  LITERAL_PREFIX	DBTYPE_WSTR	The character that is used to prefix a literal of this type in a text command.
  LITERAL_SUFFIX	DBTYPE_WSTR	The character that is used to suffix a literal of this type in a text command.
  IS_NULLABLE	DBTYPE_BOOL	VARIANT_TRUE if this data type can be set to NULL; VARIANT_FALSE if this data type cannot be set to NULL.
  CASE_SENSITIVE	DBTYPE_BOOL	VARIANT_TRUE if the data type is character data and is case-sensitive. VARIANT_FALSE if the data type is not character data.
  SEARCHABLE	DBTYPE_UI4	Specifies whether the data type is searchable. Value: DB_SEARCHABLE if the provider supports ICommandText and the data type can be used with any relative operator in a WHERE clause; NULL if the provider does not support ICommandText.
  UNSIGNED_ATTRIBUTE	DBTYPE_BOOL	VARIANT_TRUE if the data type is unsigned; VARIANT_FALSE if the type is signed; NULL for SAS character data.
  FIXED_PREC_SCALE	DBTYPE_BOOL	VARIANT_TRUE if the precision and scale are fixed for this data type; VARIANT_FALSE if there is not a fixed precision and scale for this type.
  AUTO_UNIQUE_VALUE	DBTYPE_BOOL	VARIANT_TRUE if values of this type can automatically increment; VARIANT_FALSE if they cannot.
  GUID	DBTYPE_GUID	The GUID for this type.
  TYPELIB	DBTYPE_WSTR	The type library for this type.
  BEST_MATCH	DBTYPE_BOOL	VARIANT_TRUE if this data type is the best match between the OLE DB data type (given by the value in the DATA_TYPE column), and all types in the data source; VARIANT_FALSE if this data type is not the best match.
  IS_FIXEDLENGTH	DBTYPE_BOOL	VARIANT_TRUE if all data of this type has the same length; VARIANT_FALSE otherwise.

  Restrictions

  Restrictions allow an OLE DB consumer program to restrict which rows are returned in a schema rowset by IDBSchemaRowset::GetRowset(). A restriction is a value for a certain column. When a restriction is passed to IDBSchemaRowset::GetRowset(), all rows that are returned in the schema rowset must match the restriction value that is on the specified column.

  Restriction values do not support pattern matching or wild cards. For example, the restriction value "D_F" matches "D_F" but not "DEF."

  The OLE DB specification lists, for each schema rowset, certain columns for which a provider might support restrictions. The SAS providers actually support restrictions only on the following columns:

  • For the TABLES schema rowset, this provider supports restrictions only on the TABLE_NAME column.
  • For the COLUMNS schema rowset, this provider supports restrictions only on the TABLE_NAME column.
  • For the PROVIDER_TYPES schema rowset, this provider supports restrictions only on the DATA_TYPE column.

  An OLE DB consumer can programmatically discover which columns the providers support restrictions on by calling IDBSchemaRowset::GetSchemas().