In this recipe, you learn how to use the IOM and SAS/SHARE providers to create and delete data sets. Sample code for both the ADO and OLE DB interfaces is included.
| Applies to: | SAS IOM Data Provider, SAS/SHARE Data Provider |
| Implement using: | ADO or OLE DB |
There are three different ways to create and delete data sets using the IOM and SAS/SHARE providers.
You can create and delete data sets by passing SQL statements to the providers. The CREATE TABLE statement is used to create a data set and the DROP TABLE statement is used to delete a data set.
For example, the statement shown next 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 next 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: You can pass SQL commands to the providers using either ADO or OLE DB. For examples, see Executing SQL Queries and Commands. For more information about SQL syntax, see the documentation for PROC SQL in the Base SAS 9.1 Procedures Guide.
You also can use ADOX to create new data sets and delete existing ones. To use ADOX, you must add following two library references to your Visual Basic project (see Minimum System Requirements for the SAS Data Providers for supported versions):
The following sample code illustrates how to use ADOX to create a data set, append to a data set, and delete a data set.
Note: For information about how to open a Connection object, see Opening an ADO Connection Object.
Option Explicit
Sub Main()
Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim table As ADOX.table
Dim tablename As String
Set cn = New ADODB.Connection
Set cat = New ADOX.Catalog
Set table = New ADOX.table
tablename = "sasuser.newtable"
' Open the Connection Object to either the IOM or SAS/SHARE provider.
' 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.
' At this point, calls are made into the provider to
' actually create the data set on disk.
cat.ActiveConnection = cn
cat.Tables.Append table
' Records can now be added to the newly created data set
' by opening a Recordset Object and adding rows.
' Delete the new table.
cat.Tables.Delete tablename
cn.Close
End Sub
OLE DB consumers can use the ITableDefinition interface that is exposed by the Session object to create and delete data sets. The following sample code illustrates how to use 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 sample then deletes that same table.
Note: For more information about the ITableDefinition interface, 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 it's IUnknown interface in spUnkDataSrc. */
CLSID clsid;
FAIL_IF( CLSIDFromProgID( L"SAS.IOMProvider.1", &clsid ) );
FAIL_IF( CoCreateInstance( clsid, NULL, CLSCTX_INPROC_SERVER, IID_IUnknown, (void**)&spUnkDataSrc ) );
return S_OK;
}
HRESULT InitializeProvider( CComPtr<IUnknown>& spUnkDataSrc )
{
/* spUnkDataSrc contains a IUnknown pointer to an uninitialized IOM Data Source Object. */
/* This method initializes the IOM 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;
ULONG 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 crated, 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 COM IOM 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;
}