Displaying Metadata That Is Specific to SAS Data Sets

Goal

You want your application to display metadata that is specific to SAS data sets.

Sample code for ADO is included.

ADO Implementation

How Metadata Is Exposed

Available metadata for a SAS data set includes persisted formats and informats, number of records, and whether an index exists. To extract the metadata, you use extensions to the COLUMNS and TABLES schema rowsets.

Note: For more information, see COLUMNS Schema Rowset and TABLES Schema Rowset.

Sample Code for Displaying Information about Formats and Informats

The following Visual Basic code shows how to display information about SAS formats and informats that are persisted on a data set named Shoes in the SASUSER library.

This sample code applies to the local, SAS/SHARE, IOM, and Base SAS providers.

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

' Get schema information for Sasuser.Shoes. 
Set obRecordset = obConnection.OpenSchema(adSchemaColumns, 
  Array(Empty, Empty, "Sasuser.Shoes"))

' Display fields pertaining to SAS formats and informats.
Do Until obRecordset.EOF
  Debug.Print "Table name: " & obRecordset!TABLE_NAME & vbCr & _
  "Column Name: " & obRecordset!COLUMN_NAME & vbCr & _
  "Format name: " & obRecordset!FORMAT_NAME & vbCr & _
  "Format length: " & obRecordset!FORMAT_LENGTH & vbCr & _
  "Informat name: " & obRecordset!INFORMAT_NAME & vbCr & _
  "Informat length: " & obRecordset!INFORMAT_LENGTH
  obRecordset.MoveNext
Loop

A Closer Look at the Parameters for the OpenSchema Method

The following line of code specifies the parameters for the OpenSchema Method:

Set obRecordset= obConnection.OpenSchema(adSchemaColumns, 
  Array(Empty, Empty, "SASUSER.SHOES"))

For the first parameter, which is Query Type, the sample code specifies adSchemaColumns. adSchemaColumns returns information for all columns on all data sets in the open connection.
For the second parameter, which is Criteria, the sample code passes in an array that limits the returned information to just SASUSER.SHOES columns. To achieve this result, the array specifies a nonempty value for the TABLE_NAME, which is the third constraint available for adSchemaColumns. (adSchemaColumns has four available constraints: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME.)

Tip

To return metadata for all data sets in the open connection, write similar code and specify adSchemaTables (instead of adSchemaColumns) as the value of the QueryType parameter.

Sample Code for Displaying SAS Data Set Information

The following C# code shows how to display information about the SAS data set type, label, and encoding for the data set named Shoes in the SASUSER library.

This code applies to the local provider.

// obConnection is an open Connection object.
Recordset rs = new Recordset();
try {
  rs.ActiveConnection = obConnection;
  rs.Open("shoes", Type.Missing, CursorTypeEnum.adOpenForwardOnly,
      LockTypeEnum.adLockReadOnly,
      (int)CommandTypeEnum.adCmdTableDirect);

  // Recordset Properties for the input table
  Console.WriteLine("SAS Data Set Type property: " + 
      rs.Properties["SAS Data Set Type"].Value);

  Console.WriteLine("SAS Data Set Label property: " + 
      rs.Properties["SAS Data Set Label"].Value);

  Console.WriteLine("SAS Data Set Encoding property: " + 
      rs.Properties["SAS Data Set Encoding"].Value);

  Console.WriteLine("SAS Data Set Windows Code Page property: " +
      rs.Properties["SAS Data Set Windows Code Page"].Value);   

  rs.Close();
} catch (Exception e) {
  // exception handling
}

The following C# code produces similar results to the previous example, but accesses the Schema Rowset Tables. The code applies to the local provider and SAS/SHARE provider.

// obConnection is an open Connection object.
Recordset rs = new Recordset();
try {
  rs.ActiveConnection = obConnection;
  object[] restrictions = new object[] { null, null, "shoes" };
  rs = obConnection.OpenSchema( SchemaEnum.adSchemaTables, 
      restrictions, Type.Missing);

  // Fields of the Tables schema rowset for the input table
  Console.WriteLine("SAS_DATASET_TYPE field:" + 
     rs.Fields["SAS_DATASET_TYPE"].Value);

  Console.WriteLine("SAS_DATASET_LABEL field:" + 
     rs.Fields["SAS_DATASET_LABEL"].Value);

  Console.WriteLine("SAS_DATASET_ENCODING field:" + 
     rs.Fields["SAS_DATASET_ENCODING"].Value);

  Console.WriteLine("SAS_DATASET_WINDOWS_CODEPAGE field: " + 
      rs.Fields["SAS_DATASET_WINDOWS_CODEPAGE"].Value);

  rs.Close();

} catch (Exception e) {
  // exception handling
}

OLE DB Implementation

When you are programming directly to the OLE DB interface, you can obtain SAS metadata through schema rowset extensions or through the custom OLE DB rowset interfaces listed below. Schema rowset extensions and these custom interfaces return the same information.