Data Set Management Using the ITableDefinition Interface

The SAS/SHARE, IOM, and local data providers implement the CreateTable and DropTable methods that are available on the OLE DB ITableDefinition interface. These methods enable you to create new SAS data sets and delete existing ones.

Note: SAS does not support ITableDefinition's AddColumn or DropColumn methods.

Creating a Data Set

To create a SAS data set, you must provide the following information:

the name of the new SAS data set (table)
a description of the SAS variables (columns in the table)

You must also indicate whether you want to only create the physical data set or if you also want to return a rowset on that data set. If you want a rowset to be returned, you must provide property values for that rowset.

Naming the Data Set

Note: For the SAS/SHARE and IOM providers, the form of the name must be libname.membername, where libname is a library defined by the server and membername is the data file or data view that you want to open within the library. The libname is not required for the local provider.

To specify the name of the new data set, you use the DBID structure defined by OLE DB. The following code fragment shows how to set up a DBID structure for a new data set named MYDATA in a library named MYLIB.

DBID TableID;
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = L"MYLIB.MYDATA";

The DBID structure uses two members, eKind and pwszName, which are the only two members supported by the SAS Data Providers.

The eKind member indicates where the provider should look within the DBID to find the name to use when the physical data set is created.
DBKIND_NAME tells the provider to look only at the pwszName member of the uName union, which contains the data set name (with library reference, if applicable). The pwszName value is not case-sensitive.

Identifying the Variables

In this example data set, the following three variables (columns) are defined:

FRIEND, a 20-byte character variable
BIRTHDAY, a SAS date variable
PHONE, an 8-byte character variable.

FRIEND and PHONE are represented as SAS character variables. BIRTHDAY must be represented as a double because that is how dates are stored in SAS data sets. SAS variable types are mapped to OLE DB DBTYPE indicators as follows:

SAS numeric data is returned as a DBTYPE_R8.
SAS character data is returned as a DBTYPE_STR.

Note: For more information about data types, see the appendix topic PROVIDER_TYPES Schema Rowset.

The following code fragment shows how to use the variable information to fill an array of DBCOLUMNDESC structures. Variable (column) names are specified using a DBID structure in the same way that a data set (table) name is specified. The dbcid member of the DBCOLUMNDESC structure is where the variable (column) name is defined. The other members of the DBCOLUMNDESC structure are filled out based on the rules outlined by the OLE DB specification.

DBCOLUMNDESC rgColumnDescs[3];
memset( rgColumnDescs, '0', sizeof( rgColumnDescs ) ); // defensive programming
rgColumnDescs[0].dbcid.uName.pwszName = L"FRIEND";
rgColumnDescs[0].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[0].wType = DBTYPE_STR;
rgColumnDescs[0].ulColumnSize = 20;
rgColumnDescs[1].dbcid.uName.pwszName = L"BIRTHDAY";
rgColumnDescs[1].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[1].wType = DBTYPE_R8;
rgColumnDescs[1].bPrecision = 15;
rgColumnDescs[2].dbcid.uName.pwszName = L"PHONE";
rgColumnDescs[2].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[2].wType = DBTYPE_STR;
rgColumnDescs[2].ulColumnSize = 8;

Returning a Rowset

To create a rowset on the new data set, you must specify the following information:

the IID of the interface you want returned
the address of an interface pointer

The following code fragment completes this example. It can be used to create and return a sequential rowset—an approach that is useful if you want to create and populate the data set at the same time, but you do not intend to immediately update the new rows.

IDBCreateSession * pCreateSession;
ITableDefinition * pTableDefinition;
IUnknown * pRowset;
DBPROP prop;
DBPROPSET PropertySet;
pDSO->QueryInterface( IID_IDBCreateSession, &pCreateSession );
pCreateSession->CreateSession( NULL,                    // no aggregation
                               IID_ITableDefinition,    // ID of the interface we want on the session object
                               &pTableDefinition );     // address of pointer to session 
// specify sequential access
prop.dwPropertyID = DBPROP_IRowsetLocate;
prop.dwOptions = DBPROPOPTIONS_OPTIONAL;
prop.colid = DB_NULLID;
prop.vValue.vt = VT_BOOL;
prop.vValue.bool = VARIANT_FALSE;
PropertySet.guidPropertySet = DBPROPSET_ROWSET;
PropertySet.cProperties = 1;
PropertySet.rgProperties = ∝
pTableDefinition->CreateTable( NULL,
                                               // no aggregation
                    &TableID,
                    3,                         // 3 columns
                    rgColumnDescs,             // column descriptions
                    IID_IRowset,               // id of interface we want on the rowset
                    1,                         // we're passing in the rowset property set
                    &PropertySets,
                    NULL,                      // ignored by the providers
                    &pRowset );                // address of pointer to rowset

Deleting Data Sets

You can use the DropTable method to delete a SAS data set from its associated data source. The definition of data source depends on the provider being used:

For the Local Data Provider, a data source is a single directory on disk, which corresponds to a single library.
For the SAS/SHARE Data Provider, a data source consists of all library names that are known to a SAS/SHARE server.
For the SAS IOM Data Provider, a data source consists of all library names that are known to an IOM server.

CAUTION: Be careful when you design an application that implements this method. If you give users the ability to erase data sets, you should also build in safeguards to prevent them from accidentally deleting data sets.

The DropTable method's single parameter is an instance of the DBID structure, which is used to specify the name of the data set (table) and its variables (columns). The following code fragment shows how to set up a DBID structure to delete a data set named MYDATA in a library named MYLIB.

DBID TableID;
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = L"MYLIB.MYDATA"

The following code fragment shows how you would use this DBID structure to delete the data set:

IDBCreateSession * pCreateSession;
ITableDefinition * pTableDefinition;
pDSO->QueryInterface( IID_IDBCreateSession, &pCreateSession );
pCreateSession->CreateSession( NULL, 			// no aggregation 
                               IID_ITableDefinition,	// ID of the interface we want on the session object 
                               &pTableDefinition );	// address of pointer to session 
pTableDefinition->DropTable( &pTableID );