Creating and Deleting Data Sets

Goal

You want your application to create and delete data sets.
This recipe applies to the SAS/SHARE, IOM, and Base SAS providers. This recipe includes sample code for ADO and OLE DB.

Implementation

Three Methods for Creating and Deleting Data Sets

Here are three ways that you can create and delete data sets.
  • You can use SQL CREATE TABLE and DROP TABLE statements. This method can be used with ADO and OLE DB consumers.
  • You can use Microsoft ActiveX Data Object Extensions for DDL and Security (ADOX). This method is used with ADO consumers.
  • You can use the ITableDefinition interface. This method is used with OLE DB consumers.
Before deciding which method to use, review the information in the following table.
How to Select a Method
Your Situation or Preference
Recommended Method
You want to use ADO.
Use SQL or ADOX.
You want to use OLE DB.
Use SQL or the ITableDefinition interface.

Using SQL Commands with an ADO or OLE DB Consumer

You can pass SQL commands to the providers by using either ADO or OLE DB. The CREATE TABLE statement is used to create a data set. The DROP TABLE statement is used to delete a data set. For example, the statement shown below creates a data set named newtable in the sasuser library with three columns: a numeric column named i, a character column of length 40 named name, and a numeric column named age. 
create table sasuser.newtable ( i num, name char(40), age num );
The following SQL statement creates a data set named newtable in the sasuser library that is a copy of the data set oldtable in the sasuser library. 
create table sasuser.newtable as select * from sasuser.oldtable;
Note: For more examples, see Subsetting Data Sets for Read-Only Sequential Access. For more information about SQL syntax, see the documentation for PROC SQL in the Base SAS Procedures Guide.

Using ADOX with an ADO Consumer

If you are creating an ADO consumer, you can use ADOX to create new data sets and delete existing ones.
Note: To use ADOX, you must add the following two library references to your Visual Basic project: Microsoft ActiveX Data Objects Library and the Microsoft ADO Extension for DDL and Security.
The following Visual Basic code shows you how to use ADOX to create a data set, append to a data set, and delete a data set. 
' obConnection is an open Connection object.

 Dim cat As New ADOX.Catalog
 Dim table As New ADOX.table
 Dim tablename As String
	
 tablename = "sasuser.newtable"
	
 ' Create the data set.
 table.Name = tablename
 table.Columns.Append "i", adDouble, 0
 table.Columns.Append "name", adChar, 40
 table.Columns.Append "age", adDouble, 0

 ' Append the new data set to the collection of data sets.
 ' Call the provider to create the data set on disk.
 cat.ActiveConnection = obConnection
 cat.Tables.Append table

 ' Open a Recordset object and add rows to the new data set.  

 ' Delete the new table.
 cat.Tables.Delete tablename

Using the ITableDefinition Interface with an OLE DB Consumer

OLE DB consumers can use the ITableDefinition interface that is exposed by the Session object to create and delete data sets. The following C++ code shows how this task is done.
The code uses the IOM provider to create and delete a table named newtable in the sasuser library. The sample first creates the table with three columns: a numeric column named i, a character column of length 40 named name, and a numeric column named age. The code then deletes that same table.
Note: For more information, see Data Set Management Using the ITableDefinition Interface.
#include atlbase.h
#include comdef.h
#include comutil.h
#include oledb.h
#include atldbcli.h

#include <iostream>
#include <iomanip>
using std::cout;
using std::hex;
using std::setw;
using std::setfill;
using std::right;
using std::endl;

#define FAIL_IF( hr ) issue_if_failed( __FILE__, __LINE__, hr )
inline void issue_if_failed( char* file, ULONG line, HRESULT hr )
{
 /* This method throws an error if hr is a failure. */
 if( FAILED( hr ) )
 {
 ATLTRACE( %s(%d): Failure 0x%X\n, (char*)file, line, hr );
 _com_issue_error( hr );
 }
}

HRESULT InstantiateProvider( CComPtr<IUnknown>& spUnkDataSrc )
{
 /* spUnkDataSrc contains a NULL IUnknown pointer. */
 /* This method instantiates the IOM Provider Data Source object and */
 /* stores a pointer to its IUnknown interface in spUnkDataSrc. */
 CLSID clsid;

 FAIL_IF( CLSIDFromProgID( L"SAS.IOMProvider", &clsid ) );
 FAIL_IF( CoCreateInstance( clsid, NULL, CLSCTX_INPROC_SERVER, IID_IUnknown, (void**)&spUnkDataSrc ) );

 return S_OK;
}

HRESULT InitializeProvider( CComPtr<IUnknown>& spUnkDataSrc )
{
 /* spUnkDataSrc contains an IUnknown pointer to an uninitialized IOM Data Source Object. */
 /* This method initializes the IOM Data Provider. */
 
 CComQIPtr<IDBInitialize> spInit = spUnkDataSrc;
 CComQIPtr<IDBProperties> spProp = spUnkDataSrc;
 if ( spInit == NULL || spProp == NULL )
  return E_FAIL;

 CDBPropSet Props( DBPROPSET_DBINIT );
 Props.AddProperty( DBPROP_INIT_DATASOURCE, (WCHAR*)L_LOCAL_ );
 FAIL_IF( spProp->SetProperties( 1, &Props ) );

 FAIL_IF( spInit->Initialize() );

 return S_OK;
}

HRESULT CreateSession( CComPtr<IUnknown>& spUnkDataSrc, CComPtr<IUnknown>& spUnkSession )
{
 /* spUnkDataSrc contains a pointer to the IUnknown interface of an initialized Data Source Object */
 /* spUnkSession is where a pointer to the IUnknown interface of the newly created 
      Session Object will be stored */
 HRESULT hr = E_FAIL;
 CComQIPtr<IDBCreateSession> spCreateSession = spUnkDataSrc;
 if( spCreateSession == NULL )
 cout << "ERROR: IDBCreateSession not found" << endl;
 else
 {
 hr = spCreateSession->CreateSession( NULL, IID_IUnknown, &spUnkSession.p );
 FAIL_IF( hr );
 }
 return hr;

}

HRESULT CreateNewTable( CComPtr<IUnknown>& spUnkSession )
{
 /* spUnkSession contains a pointer to the IUnknown interface of an open Session Object */
 /* This method creates a table named newtable from the sasuser library */
 HRESULT hr = E_FAIL;
 CComQIPtr<ITableDefinition> spTableDef = spUnkSession;
 if( spTableDef == NULL )
 cout << "ERROR: ITableDefinition not found" << endl;
 else
 {
 CComPtr<IUnknown> spUnkNewTable = NULL;
 DBORDINAL cColumnDescs = 3;
 DBCOLUMNDESC rgColumnDescs[3];
 DBID in_table;

 in_table.eKind = DBKIND_NAME; /* only DBKIND_NAME supported */
 in_table.uName.pwszName = L"sasuser.newtable";

 memset( rgColumnDescs, 0, 3*sizeof(DBCOLUMNDESC) );

 rgColumnDescs[0].wType = DBTYPE_R8;
 rgColumnDescs[0].ulColumnSize = 8;
 rgColumnDescs[0].dbcid.eKind = DBKIND_NAME; /* only DBKIND_NAME supported */
 rgColumnDescs[0].dbcid.uName.pwszName = L"i";

 rgColumnDescs[1].wType = DBTYPE_WSTR;
 rgColumnDescs[1].ulColumnSize = 40;
 rgColumnDescs[1].dbcid.eKind = DBKIND_NAME; /* only DBKIND_NAME supported */
 rgColumnDescs[1].dbcid.uName.pwszName = L"name";

 rgColumnDescs[2].wType = DBTYPE_R8;
 rgColumnDescs[2].ulColumnSize = 8;
 rgColumnDescs[2].dbcid.eKind = DBKIND_NAME; /* only DBKIND_NAME supported */
 rgColumnDescs[2].dbcid.uName.pwszName = L"age";

 hr = spTableDef->CreateTable(
 NULL /* outer unknown */,
 &in_table /*id of table to create */,
 cColumnDescs /* number of columns */,
 rgColumnDescs /* description of columns */,
 IID_IUnknown /* interface to get on new data set */,
 0 /* number of property sets */,
 NULL /* property sets */,
 NULL /* id of table created, always equal to the requested id */,
 &spUnkNewTable.p /* pointer to new data set */ );
 }
 return hr;
}

HRESULT DeleteNewTable( CComPtr<IUnknown>& spUnkSession )
{
 /* spUnkSession contains a pointer to the IUnknown interface of an open Session Object */
 /* This method deletes a table named newtable from the sasuser library */
 HRESULT hr = E_FAIL;
 CComQIPtr<ITableDefinition> spTableDef = spUnkSession;
 if( spTableDef == NULL )
 cout << "ERROR: ITableDefinition not found" << endl;
 else
 {
 DBID tableid;
 tableid.eKind = DBKIND_NAME; /* only DBKIND_NAME supported */
 tableid.uName.pwszName = L"sasuser.newtable";
 hr = spTableDef->DropTable( &tableid );
 }
 return hr;
}

int main(int argc, char* argv[])
{
 CoInitialize(NULL);
 
 try
 {
 HRESULT hr = E_FAIL;
 CComPtr<IUnknown> spUnkDataSrc = NULL; /* pointer to IUnknown interface of a Data Source Object */
 CComPtr<IUnknown> spUnkSession = NULL; /* pointer to IUnknown interface of a Session Object */
 CComPtr<IUnknown> spUnkCommand = NULL; /* pointer to IUnknown interface of a Command Object */
 CComPtr<IUnknown> spUnkRowset = NULL; /* pointer to IUnknown interface of a Rowset Object */
 FAIL_IF( InstantiateProvider( spUnkDataSrc ) ); /* creates an instance of the IOM Provider Data Source */
 FAIL_IF( InitializeProvider( spUnkDataSrc ) ); /* initializes the Data Source object to 
                                                   connect to the local workspace server */
 FAIL_IF( CreateSession( spUnkDataSrc, spUnkSession ) ); /* creates an instance of a Session Object */
 FAIL_IF( CreateNewTable( spUnkSession ) ); /* create a data set named newtable in the sasuser library */
 FAIL_IF( DeleteNewTable( spUnkSession ) ); /* delete a data set named newtable in the sasuser library */
 }
 catch( _com_error& e )
 {
 cout << "ERROR: 0x" << hex << setw(8) << setfill('0') << right << e.Error() << endl;
 }

 CoUninitialize();
 return 0;
}
Copyright © SAS Institute Inc. All rights reserved.