In this recipe, you learn how to execute SQL queries and commands with the IOM and SAS/SHARE providers. 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 |
The IOM and SAS/SHARE providers both enable users to execute SQL queries and commands. ADO consumers can pass SQL statements in one of several ways, including
See ADO Details for more information about this recipe.
OLE DB consumers can use the ICommand family of interfaces to pass SQL queries and commands. See OLE DB Details.
The following three sections provide sample code that you can use to execute SQL commands and queries. All three samples require that you add the Microsoft ActiveX Data Objects Library to your Visual Basic project (see Minimum System Requirements for the SAS Data Providers for supported versions).
Note: For information about how to open a Connection object, see Opening an ADO Connection Object.
The following code illustrates how you can use an open Connection object to pass SQL statements to the providers. The first statement creates a new data set named newtable in the sasuser library. 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. The second statement inserts records into the new table. The third statement creates a Recordset object from a SELECT statement.
Option Explicit
Sub Main()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the Connection Object to either the IOM or SAS/SHARE provider.
' Create a new table.
cn.Execute "create table sasuser.newtable ( i num, name char(40), age num );"
' Insert values into the new table.
cn.Execute "insert into sasuser.newtable values( 0, ""Bill"", 32 ) values( 1, ""John"", 99 );"
' Open a Recordset object on the new table.
Set rs = cn.Execute("select * from sasuser.newtable")
' Do something with the Recordset.
MsgBox rs.GetString
rs.Close
cn.Close
End Sub
The same SQL statements shown in the previous example can be executed through a Command object, as illustrated in the following code sample:
Option Explicit
Sub Main()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
' Open the Connection object to either the IOM or SAS/SHARE provider.
cmd.ActiveConnection = cn
' Create a new table.
cmd.CommandText = "create table sasuser.newtable ( i num, name char(40), age num );"
cmd.Execute
' Insert values into the new table.
cmd.CommandText = "insert into sasuser.newtable values( 0, ""Bill"", 32 ) values( 1, ""John"", 99 );"
cmd.Execute
' Open a Recordset object on the new table.
cmd.CommandText = "select * from sasuser.newtable"
Set rs = cmd.Execute()
' Do something with the recordset.
MsgBox rs.GetString
rs.Close
cn.Close
End Sub
SQL statements that result in recordsets can be executed through a Recordset object using the adCmdText option as shown in the following code:
Option Explicit
Sub Main()
Dim cn As adodb.Connection
Dim rs As adodb.Recordset
Set cn = New adodb.Connection
Set rs = New adodb.Recordset
' Open the Connection object to either the IOM or SAS/SHARE provider
' Execute a command that results in a recordset.
' SQL result sets are only provided forward-only and read-only.
rs.Open "select * from sasuser.newtable", cn, adOpenForwardOnly, adLockReadOnly, adodb.adCmdText
' Do something with the recordset.
MsgBox rs.GetString
rs.Close
cn.Close
End Sub
OLE DB consumers can use the ICommand family of interfaces that are exposed by the Command object to execute SQL statements in the IOM and SAS/SHARE providers. The most common interfaces are ICommand and ICommandText. In the sample code that follows, 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 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 specifically. Initializing the SAS/SHARE */
/* Provider requires different properties as described in Opening an ADO Connection Object. */
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 CreateCommand( CComPtr<IUnknown>& spUnkSession, CComPtr<IUnknown>& spUnkCommand )
{
/* spUnkSession contains a pointer to the IUnknown interface of an open Session Object */
/* spUnkCommand is where a pointer to the IUnknown interface of the newly created Command Object will be stored */
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 COM IOM 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;
}