Data Set Management Using the ITableDefinition Interface

About Creating and Deleting SAS Data Sets

The SAS/SHARE, IOM, and Base SAS 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 the ITableDefinition AddColumn or DropColumn methods.

Creating a Data Set

Process Overview

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: 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.

To specify the name of the new data set, you use the DBID structure defined by OLE DB. The following code 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 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 PROVIDER_TYPES Schema Rowset.

The following code shows how to use the variable information to fill an array of DBCOLUMNDESC structures. Variable (column) names are specified by 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 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->CreateSession( NULL, // no aggregation
  IID_ITableDefinition, // ID of the interface we want on the session object
   ); // 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
 ,
 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
 ,
 NULL, // ignored by the providers
  ); // 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 that is being used:

For the Base SAS provider, a data source consists of all library names that are known to a local installation of Base SAS.
For the SAS/SHARE provider, a data source consists of all library names that are known to a SAS/SHARE server.
For the IOM provider, a data source consists of all library names that are known to a SAS Workspace 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 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 shows how you would use this DBID structure to delete the data set:

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