Subsetting Data Sets for Read-Only Sequential Access

Goal

You want your application to execute SQL queries and commands in order to subset data sets for read-only sequential access.

This recipe applies to the SAS/SHARE, IOM, and Base SAS providers. This recipe includes sample code for ADO and OLE DB.

ADO Implementation

Three Methods for Executing SQL Queries and Commands

The IOM, SAS/SHARE, and Base SAS providers can be used to pass SQL statements in three ways:

calling Execute on an open Connection object
calling Execute on an open Command object
calling Open on a Recordset object and passing adCmdText as the option

Note: All three methods require that you reference the Microsoft ActiveX Data Objects Library in your Visual Basic project.

Calling Execute on an Open Connection Object

The following code uses an open ADO Connection object in order to pass SQL statements to a provider. The data set has three columns: a numeric column named i, a character column of length 40 named name, and a numeric column named age.

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset
	 
' Create a new table in the Sasuser library.
obConnection.Execute "create table sasuser.newtable ( i num, name char(40), age num );"

' Insert values into the new table.
obConnection.Execute "insert into sasuser.newtable values( 0, ""Bill"", 32 ) values( 1, ""John"", 99 );"

' Use a SELECT statement to open a Recordset object on the new table.
Set obRecordset = obConnection.Execute("select * from sasuser.newtable")

' Do something with the Recordset object.
MsgBox obRecordset.GetString
 
' Close the Recordset object.
obRecordset.Close

Calling Execute on an Open Command Object

The same SQL statements shown in the previous example can be executed through an ADO Command object, as shown in the following Visual Basic code:

' obConnection is an open Connection object.
Dim obCommand As New ADODB.Command
Dim obRecordset As New ADODB.Recordset

obCommand.ActiveConnection = obConnection
 
' Create a new table.
obCommand.CommandText = "create table sasuser.newtable ( i num, name char(40), age num );"
obCommand.Execute
 
' Insert values into the new table.
obCommand.CommandText = "insert into sasuser.newtable values( 0, ""Bill"", 32 ) values( 1, ""John"", 99 );"
obCommand.Execute
 
' Open a Recordset object on the new table.
obCommand.CommandText = "select * from sasuser.newtable"
Set obRecordset = obCommand.Execute()
 
' Do something with the Recordset object.
MsgBox obRecordset.GetString
 
' Close the Recordset object.
obRecordset.Close

Calling Open on a Recordset Object and Passing adCmdText as the Option

SQL statements that result in recordsets can be executed through an ADO Recordset object by using the adCmdText option. The following Visual Basic code shows how this task is done.

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

' Execute a command that results in a recordset.
' SQL result sets are forward-only and read-only.
obRecordset.Open "select * from sasuser.newtable", obConnection, adOpenForwardOnly, _
                                                   adLockReadOnly, adodb.adCmdText
  
' Do something with the Recordset object.
MsgBox obRecordset.GetString
 
' Close the Recordset object.
obRecordset.Close

OLE DB Implementation

OLE DB consumers can use the ICommand family of interfaces that are exposed by the Command object to execute SQL statements. The most common interfaces are ICommand and ICommandText. The following C++ code shows how this task is done. In the sample code, the first two statements return NULL for the pointer to the rowset because no rowset is created. The third statement creates a rowset and returns a pointer to its IUnknown 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 Provider specifically. Initializing the SAS/SHARE */
 /* Provider requires different properties as described in Establishing an ADO Connection. */
 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 the location for a pointer to the IUnknown interface of the newly created Session Object */
 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 CreateCommand( CComPtr<IUnknown>& spUnkSession, CComPtr<IUnknown>& spUnkCommand )
{
 /* spUnkSession contains a pointer to the IUnknown interface of an open Session Object */
 /* spUnkCommand is the location for a pointer to the IUnknown interface of the newly created Command Object */
 HRESULT hr = E_FAIL;
 CComQIPtr<IDBCreateCommand> spCreateCommand = spUnkSession;
 if( spCreateCommand == NULL )
 cout << "ERROR: IDBCreateCommand not found" << endl;
 {
 hr = spCreateCommand->CreateCommand( NULL, IID_IUnknown, &spUnkCommand.p );
 FAIL_IF( hr );
 }
 return hr;
}

HRESULT ExecuteSQL( CComPtr<IUnknown>& spUnkCommand, bstr_t bstrSQL, CComPtr<IUnknown>& spUnkRowset )
{
 /* spUnkCommand contains a pointer to the IUnknown interface of an open command */
 /* spUnkRowset is where the IUknown pointer to the created rowset (if any) will be stored */
 HRESULT hr = E_FAIL;
 CComQIPtr<ICommandText> spCommandText = spUnkCommand;
 if( spCommandText == NULL )
 cout << "ERROR: ICommandText not found" << endl;
 else
 {
 FAIL_IF( spCommandText->SetCommandText( DBGUID_DBSQL, bstrSQL ) );
 hr = spCommandText->Execute( NULL, IID_IUnknown, NULL, NULL, &spUnkRowset.p );
 FAIL_IF( hr );
 }
 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( CreateCommand( spUnkSession, spUnkCommand ) ); /* creates an instance of a Command Object */

 /* create a new data set */
 FAIL_IF( ExecuteSQL( spUnkCommand, "create table sasuser.newtable ( i num, name char(40), age num );", 
           spUnkRowset ) );

 /* add some rows to the new data set */
 FAIL_IF( ExecuteSQL( spUnkCommand, "insert into sasuser.newtable values( 0, \"Bill\", 32 ) 
           values( 1, \"John\", 99 );", spUnkRowset ) );

 /* open a Recordset on the new data set */
 FAIL_IF( ExecuteSQL( spUnkCommand, "select * from sasuser.newtable", spUnkRowset ) );

 /* do something with spUnkRowset */
 /* ... */
 }
 catch( _com_error& e )
 {
 cout << "ERROR: 0x" << hex << setw(8) << setfill('0') << right << e.Error() << endl;
 }

 CoUninitialize();
 return 0;
}