|
|
SAS IOM Data Provider |
|
To process SQL statements, the SAS IOM data provider implements OLE DB command objects. Each command object encapsulates a connection to a particular database management system (DBMS) and one SQL statement. Clients can repeatedly use the same command object for different commands and queries by resetting the SQL statement. However, the command object's DBMS connection cannot be changed. If an application needs to connect to another DBMS, a new command object must be created. The DBMS is automatically disconnected when the command object is destroyed.
In addition to exposing the mandatory interfaces on the command object, the IOM provider also exposes ICommandPrepare and ISupportErrorInfo.
Command objects are spawned from OLE DB session objects. To access any of the data sets from an established data source connection using command objects, a session object first needs to be created by calling IDBCreateSession::CreateSession(). Properties set on the session object determine which SQL engine will be used to create command objects. The client can set the following properties on the session object before creating a command object:
These properties belong to the DBPROPSET_SAS_SESSION property set. If the client does not set these properties, the default property values will be used when the command object is created. Whatever properties are set on the session object pertain to all command objects that are created unless the session properties are reset.
The following example starts with an initialized data source component, pDSO, creates a session object and sets the two session properties that are relevant to command objects:
LPUNKNOWN pDSO = NULL;
LPUNKNOWN pSession = NULL;
IDBCreateSession * pIDBCreateSession = NULL;
ISessionProperties * pISessionProperties = NULL;
DBPROPSET PropSet;
DBPROP prop[2];
pDSO->QueryInterface( IID_IDBCreateSession, (void **) &pIDBCreateSession );
//-- Create a session object
pIDBCreateSession->CreateSession( NULL, IID_ISessionProperties, (LPUNKNOWN *) &pISessionProperties);
//set engine name; no connection string
prop[0].dwPropertyID = DBPROP_SAS_SQLENGINE;
prop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
prop[0].colid = DB_NULLID;
V_VT(&(prop[0].vValue)) = VT_BSTR;
V_BSTR(&(prop[0].vValue)) = SysAllocString(L"oracle");
prop[1].dwPropertyID = DBPROP_SAS_SQLCONNECTIONSTRING;
prop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
prop[1].colid = DB_NULLID;
V_VT(&(prop[1].vValue)) = VT_BSTR;
V_BSTR(&(prop[1].vValue)) = SysAllocString(L"user=bob password=tiger path=briggs_1234");
PropSet.rgProperties= ∝
PropSet.cProperties=2;
PropSet.guidPropertySet = DBPROPSET_SAS_SESSION;
pISessionProperties->SetProperties(1, &PropSet);
After properties are set, all command objects are created using the engine and connection string that are previously specified. The client may reset these session properties by repeating the above lines with a different engine and connection string. The IDBCreateCommand interface is needed to create the command object. The following lines of code return the IDBCreateCommand interface and use it to instantiate a command object:
IDBCreateCommand * pIDBCreateCommand = NULL;
ICommandText * pICommandText;
pISessionProperties->QueryInterface( IID_IDBCreateCommand, (void **)
&pIDBCreateCommand); pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(LPUNKNOWN *) &pICommandText);
Each command object may hold one SQL statement, which is referred to as its command text. The command text is set with the ICommandText::SetCommandText() method. The command text syntax is DBMS dependent. Clients should pass in DBGUID_DEFAULT as the dialect.
ICommandText * pICommandText;
hr = pICommandText->SetCommandText(DBGUID_DEFAULT, L"select * from patients where age > 66");
After the command text and command properties have been set, execute the command by invoking ICommand::Execute() . If the command text is a statement that returns rows, pass in a requested rowset interface and a rowset will be returned by this method. If the command text does not return rows (such as an SQL command) then pass in IID_NULL for the requested interface or NULL for ppRowset. Two arguments to this method, pParams and cRowsAffected, are currently ignored by the IOM provider so that the client can set them to NULL.
memset( (void *) &Params, 0, sizeof( DBPARAMS ) );
hr = pICommandText->QueryInterface( IID_ICommand, (void **) &pICommand );
hr = pICommand->Execute(
NULL, // no aggregation
IID_IRowset, // requested interface for the returned rowset
NULL, // pParams - CURRENTLY IGNORED
NULL, // cRowsAffected - CURRENTLY IGNORED
&pQueryRowset); // IUnknown ** ppRowset);
hr = pICommandPrepare->Unprepare();
The OLE DB specification requires that you close the rowset before you prepare and execute again.
If the client requested an interface for a returned rowset on the ICommand::Execute method, the returned rowset will be a read-only rowset with the the properties DBPROP_IRowsetUpdate and DBPROP_IRowsetChange both set to VARIANT_FALSE.
|
|
SAS IOM Data Provider |
|