Contents SAS IOM Data Provider Previous Next

Data Set Management via ITableDefintion

The SAS IOM data provider implements the OLE DB ITableDefinition interface for managing tables, or data sets, within a data source. This interface has methods to create new tables, delete existing ones, and add/drop columns from existing tables. The IOM provider implements the methods that enable applications to create and delete tables (CreateTable() and DropTable() respectively) but does not implement the methods to add and drop columns (AddColumn() and DropColumn(), respectively) because these are not features that are supported by the SAS data model.

Creating New Data Sets

When you create a data set, you must provide

To specify the name of the data set that you are creating, use a DBID structure. This structure is defined by OLE DB. Because the IOM provider only looks at two members in this structure, they are all that is discussed here. When setting up an instance of this structure, include the member name (physical data set) to open. The following code example demonstrates how to set up a DBID structure to indicate that you want to call the new data set GEORGE in library MYDATA:

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

The eKind member indicates where the provider should look within the DBID to find the name to use when it creates the physical data set. DBKIND_NAME indicates that the provider should only look at the pwszName member of the uName union. Other naming combinations in the DBID structure are not supported.

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

FRIEND and PHONE are represented as SAS character variables; BIRTHDAY will need to be a double because that is how dates are stored in data sets. SAS variable types are mapped to OLE DB DBTYPEs. In the IOM provider, character types are represented as DBTYPE_WSTR and numeric data is represented as DBTYPE_R8. To describe the columns in our new data set, take this information and fill out an array of OLE DB DBCOLUMNDESC structures as follows:

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_WSTR;
rgColumnDescs[0].ulColumnSize = 40;
rgColumnDescs[0].dbcid.uName.pwszName = L"BIRTHDAY";
rgColumnDescs[0].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[0].wType = DBTYPE_R8;
rgColumnDescs[0].bPrecision = 15;
rgColumnDescs[0].dbcid.uName.pwszName = L"PHONE";
rgColumnDescs[0].dbcid.eKind = DBKIND_NAME;
rgColumnDescs[0].wType = DBTYPE_WSTR;
rgColumnDescs[0].ulColumnSize = 16;

The names of the columns are specified using the same DBID structure that specifies the data set name. The remaining fields of the DBCOLUMNDESC structure are completed according to the rules outlined by the OLE DB specification.

The last piece of information that is needed for ITableDefinition::CreateTable() is whether you want a rowset component returned over the new data set. To simply create a new data set on disk with no records, you do not need to request that a rowset be created. However, if you plan to populate the new data set, request that a rowset be created on the data set.  Do this by giving the IID of the interface that you would like returned, and the address of an interface pointer.  You can also pass rowset properties to CreateTable() if you want the rowset behavior to be different than the default.

The following example requests a sequential rowset be created and returned. You would want to do this if you are going to create and populate the data set at the same time, but do not plan to immediately update any rows that you just added. Given an initialized data source component, pDSO, a DBID like the one created above to specify a table name,TableID, and the column descriptor array, rgColumnDescs, the code example below shows how to create a table using the ITableDefinition interface:

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 component
                               &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 = ∝
pTableDefition->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 IOM provider
                    &pRowset );                // address of pointer to rowset

Deleting Existing Data Sets

The SAS IOM data provider supports the DropTable() method on the ITableDefinition interface. This method removes a table from the associated data source, which physically deletes a data set from the associated library in the server. Use caution when exposing this method in your applications to prevent users from erasing production data sets.

The DropTable() method takes an instance of a DBID structure as its single parameter. As we have seen before, the DBID structure is used by OLE DB methods to name objects like tables (data sets) and columns (variables). If you want to delete a data set named MYLIB.MYDATA from your data source, you can declare and initialize a DBID structure as follows:

DBID TableID;

TableID.eKind = DBKIND_NAME;

TableID.uName.pwszName = L"MYLIB.MYDATA";

Given this DBID and a reference to an initialized data source component, pDSO, you can acquire a session component and an instance of the ITableDefinition interface that is implemented by that session component to delete MYLIB.MYDATA from your data source. The following code demonstrates how this is done:

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 component

&pTableDefinition ); // address of pointer to session

pTableDefinition->DropTable( &TableID );


Contents SAS IOM Data Provider Previous Next