Steve Starck
Developer
Microsoft Data Access Group
July 1998
Summary: Provides tips on using the OLE DB Provider for Oracle. (27 printed pages) Includes:
The Microsoft® OLE DB Provider for Oracle is a native provider: It only exposes whatever Oracle can expose through the Oracle Call Interface API.
A native provider does not have as much overhead as one that exposes all the interfaces and behaviors defined for OLE DB. Thus, applications that do not need the full set of functionality gain performance. The disadvantage is that it limits the flexibility of the application developer.
Fortunately, several service components provide functionality not available from the Microsoft OLE DB Provider for Oracle. Because they add a layer of overhead, however, they should be avoided whenever possible to get the best performance.
This article explains some tricks you can use to squeeze the best performance from the provider and describes how to use the service components shipping with Microsoft Data Access Components (MDAC) 2.0 to get behavior not implemented by the provider. Then, it covers some issues about how to work with stored procedures. Finally, this article lists some Oracle features that are not supported in the provider at this time.
Here are some tips on how to achieve the best performance with the Microsoft OLE DB Provider for Oracle.
In any Oracle application, a significant amount of time is spent establishing connections to the Oracle server itself. The Session Pooling service component manages pools of similar connections to optimize them for a high-volume application. It is recommended that this component be used in all cases.
Session pooling is automatically enabled if you use ADO, but when writing an application directly to OLE DB you must initialize your data source by using the IDataInitialize interface instead of using CoCreateInstance on the provider itself. Here's an example of how to write this in C++:
#define DBINITCONSTANTS
#include <oledb.h>
#include <msdasc.h>
#include <crtdbg.h>
static LPCWSTR s_pwszDataSource =
L"Provider=MSDAORA;"
L"Data Source=goliath;"
L"User ID=scott;"
L"Password=tiger;";
HRESULT UseSession (IUnknown* pUnkSession);
main ()
{
HRESULT hr;
IUnknown* pUnkDataSource = NULL;
IDataInitialize* pIDataInitialize = NULL;
hr = CoInitialize (NULL);
_ASSERT ( SUCCEEDED (hr) );
hr = CoCreateInstance
(
CLSID_MSDAINITIALIZE,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(LPVOID*)&pIDataInitialize
);
if (pIDataInitialize)
{
hr = pIDataInitialize->GetDataSource
(
NULL,
CLSCTX_INPROC_SERVER,
s_pwszDataSource,
IID_IUnknown,
&pUnkDataSource
);
pIDataInitialize->Release ();
}
if (pUnkDataSource)
{
IDBInitialize* pIDBInitialize = NULL;
hr = pUnkDataSource->QueryInterface
(
IID_IDBInitialize,
(LPVOID*)&pIDBInitialize
);
if (pIDBInitialize)
{
hr = pIDBInitialize->Initialize ();
if ( SUCCEEDED(hr) )
{
IDBCreateSession* pIDBCreateSession = NULL;
hr = pIDBInitialize->QueryInterface
(
IID_IDBCreateSession,
(LPVOID*)&pIDBCreateSession
);
if (pIDBCreateSession)
{
IUnknown* pUnkSession = NULL;
hr = pIDBCreateSession->CreateSession
(
NULL,
IID_IOpenRowset,
(IUnknown**)&pUnkSession
);
if (pUnkSession)
{
hr = UseSession (pUnkSession);
}
pUnkSession->Release ();
}
}
pIDBInitialize->Release ();
}
pUnkDataSource->Release ();
}
return 0;
}
Oracle does not expose a scrollable server cursor. In addition, Oracle does not expose a way to update a row without using a SQL UPDATE statement.
As of this writing, the Microsoft OLE DB Provider for Oracle only exposes forward-only, read-only rowsets. Therefore, the provider itself does not implement the IRowsetFind, IRowsetLocate, IRowsetScroll, and IRowsetChange interfaces, nor does it support fetching or scrolling backwards.
When using IDataInitialize to create your data source object, however, you can get these interfaces by simply setting their corresponding DBPROPSET_ROWSET property value to VARIANT_TRUE. Doing so will force the Client Cursor Engine (CE) service component to automatically perform the work necessary to expose them.
Naturally, there is some amount of overhead incurred to use the client cursor engine, so if you do not need to fetch or scroll backward through the rowset or plan to update data in the rowset, you should request a forward-only, read-only rowset for optimal performance.
Continuing with our previous example of writing directly to OLE DB in C++, you would replace the call to UseSession with a call to the following routine to guarantee a forward-only, read-only rowset:
HRESULT GetForwardOnlyRowset (IUnknown* pUnkSession)
{
HRESULT hr = S_OK;
LPCWSTR pwszTable = L"EMP";
IOpenRowset* pIOpenRowset = NULL;
hr = pUnkSession->QueryInterface (IID_IOpenRowset,
(LPVOID*)&pIOpenRowset);
if (pIOpenRowset)
{
IRowset* pIRowset = NULL;
DBID dbidTemp;
dbidTemp.eKind = DBKIND_NAME;
dbidTemp.uGuid.guid = GUID_NULL;
dbidTemp.uName.pwszName = (LPWSTR)pwszTable;
hr = pIOpenRowset->OpenRowset
(
NULL,
&dbidTemp,
NULL,
IID_IRowset,
0,
NULL,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
hr = FetchRowsetForward (pIRowset);
pIRowset->Release ();
}
pIOpenRowset->Release ();
}
return hr;
}
If you are using ADO to write your application, you request a CursorLocation of adUseServer, a CursorType of adOpenForwardOnly, and a LockType of adLockReadOnly. Here's an example written in Microsoft Visual Basic®:
Sub ForwardOnly()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDAORA;Data Source=goliath;User ID=scott;Password=tiger"
rs.CursorLocation = adUseServer
rs.Open "emp", cn, adOpenForwardOnly, adLockReadOnly
rs.MoveFirst
While rs.EOF <> True
Debug.Print rs(0).Value, rs(1).Value
rs.MoveNext
Wend
rs.Close
cn.Close
End Sub
If you must have a scrollable rowset, the Client Cursor Engine service component can provide a snapshot cursor. The cursor engine will fully materialize the rowset on the client for you. However, unless you specifically request that the client cursor engine should use asynchronous fetches, it will wait until it completes its fetches before returning control to your application. In the case of a large rowset, this could take quite a bit of time.
In many cases, your application may start to fetch forward but will need to fetch backward as well. In that case, you may wish to request that the materialization of the rowset occur asynchronously.
If writing directly to OLE DB in C++, set the DBPROP_ROWSET_ASYNCH property in DBPROPSET_ROWSET to DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION before instantiating the rowset:
HRESULT GetScrollableRowset (IUnknown* pUnkSession)
{
HRESULT hr = S_OK;
LPCWSTR pwszTable = L"EMP";
IOpenRowset* pIOpenRowset = NULL;
hr = pUnkSession->QueryInterface (IID_IOpenRowset,
(LPVOID*)&pIOpenRowset);
if (pIOpenRowset)
{
IRowset* pIRowset = NULL;
DBID dbidTemp;
DBPROPSET rgPropset[1];
DBPROP rgProps[3];
dbidTemp.eKind = DBKIND_NAME;
dbidTemp.uGuid.guid = GUID_NULL;
dbidTemp.uName.pwszName = (LPWSTR)pwszTable;
rgPropset[0].rgProperties = rgProps;
rgPropset[0].cProperties = 3;
rgPropset[0].guidPropertySet = DBPROPSET_ROWSET;
rgProps[0].dwPropertyID = DBPROP_CANFETCHBACKWARDS;
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].dwStatus = 0;
VariantInit (&rgProps[0].vValue);
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].vValue.boolVal = VARIANT_TRUE;
rgProps[1].dwPropertyID = DBPROP_CANSCROLLBACKWARDS;
rgProps[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[1].dwStatus = 0;
VariantInit (&rgProps[1].vValue);
rgProps[1].vValue.vt = VT_BOOL;
rgProps[1].vValue.boolVal = VARIANT_TRUE;
rgProps[2].dwPropertyID = DBPROP_ROWSET_ASYNCH;
rgProps[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[2].dwStatus = 0;
VariantInit (&rgProps[2].vValue);
rgProps[2].vValue.vt = VT_I4;
V_I4(&rgProps[2].vValue) = DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION;
hr = pIOpenRowset->OpenRowset
(
NULL,
&dbidTemp,
NULL,
IID_IRowset,
1,
rgPropset,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
hr = FetchRowsetBackward (pIRowset);
pIRowset->Release ();
}
pIOpenRowset->Release ();
VariantClear (&rgProps[0].vValue);
VariantClear (&rgProps[1].vValue);
VariantClear (&rgProps[2].vValue);
}
return hr;
}
If you were using ADO, you would request the adAsyncFetch option when opening the recordset. Here's a Visual Basic example:
Sub ScrollableAsync()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDAORA;Data Source=goliath;User ID=scott;Password=tiger"
rs.CursorLocation = adUseClient
rs.Open "emp", cn, adOpenStatic, adLockReadOnly, adAsyncFetch
rs.MoveFirst
While rs.EOF <> True
Debug.Print rs(0).Value, rs(1).Value
rs.MoveNext
Wend
rs.Close
cn.Close
End Sub
The provider allows you to fetch LONG and LONG RAW data in a rowset. Because Oracle only exposes forward-only rowsets, and OLE DB requires that you be able to get data from a rowset at any time, deferred fetching (that is, fetching data only when it is requested) is not possible.
At this time, the provider retrieves all data for a LONG or LONG RAW column for each row it fetches. In the case where you only occasionally require the data from the LONG or LONG RAW column, it is much more efficient to simply request LONG and LONG RAW columns in a second query statement.
If writing directly to OLE DB in C++, you would use code as follows:
HRESULT GetDeferredBLOBRowset (IUnknown* pUnkSession)
{
HRESULT hr = S_OK;
LPCWSTR pwszStmt1 = L"select owner, view_name from all_views";
LPCWSTR pwszStmt2 = L"select text from all_views where owner = ? and view_name = ?";
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommand1 = NULL;
ICommandText* pICommand2 = NULL;
hr = pUnkSession->QueryInterface (IID_IDBCreateCommand,
(LPVOID*)&pIDBCreateCommand);
if (pIDBCreateCommand)
{
hr = pIDBCreateCommand->CreateCommand (NULL, IID_ICommandText,
(IUnknown **)&pICommand1);
_ASSERT ( SUCCEEDED(hr) );
if (pICommand1)
{
hr = pICommand1->SetCommandText(DBGUID_SQL, pwszStmt1);
_ASSERT ( SUCCEEDED(hr) );
}
hr = pIDBCreateCommand->CreateCommand (NULL, IID_ICommandText,
(IUnknown **)&pICommand2);
_ASSERT ( SUCCEEDED(hr) );
if (pICommand2)
{
hr = pICommand2->SetCommandText(DBGUID_SQL, pwszStmt2);
_ASSERT ( SUCCEEDED(hr) );
}
}
if (pICommand1 && pICommand2)
{
IRowset* pIRowset = NULL;
hr = pICommand1->Execute
(
NULL,
IID_IRowset,
NULL,
NULL,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
hr = FetchRowsetDeferredBLOB (pIRowset, pICommand2);
pIRowset->Release ();
}
}
if (pICommand1)
{
pICommand1->Release ();
}
if (pICommand2)
{
pICommand2->Release ();
}
return hr;
}
HRESULT FetchRowsetDeferredBLOB (IRowset * pIRowset, ICommandText * pICommand)
{
HRESULT hr = S_OK;
HACCESSOR hAccessor;
HACCESSOR hAccessorBLOB;
HACCESSOR hAccessorParam;
ULONG cbData;
BYTE * pData;
BYTE * pBLOB;
HROW hrow;
HROW * phrow = &hrow;
ULONG cFetched;
ULONG cColumns;
DBBINDING * rgBinding;
hr = CreateFastAccessor
(
pIRowset,
&hAccessor,
&cColumns,
&rgBinding,
&cbData
);
if (pICommand)
{
// Get an accessor for the BLOB rowset.
IAccessor * pIAccessor;
DBBINDING BLOBBinding;
DBBINDING ParamBinding[2];
ULONG i;
hr = pICommand->QueryInterface (IID_IAccessor, (LPVOID*)&pIAccessor);
_ASSERT ( pIAccessor );
memset ((LPVOID)&BLOBBinding, 0, sizeof(BLOBBinding));
BLOBBinding.iOrdinal = 1;
BLOBBinding.obValue = 0;
BLOBBinding.obStatus = 4000;
BLOBBinding.obLength = BLOBBinding.obStatus + sizeof (DBSTATUS);
BLOBBinding.dwPart = DBPART_STATUS | DBPART_LENGTH | DBPART_VALUE;
BLOBBinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
BLOBBinding.eParamIO = DBPARAMIO_NOTPARAM;
BLOBBinding.wType = DBTYPE_STR | DBTYPE_BYREF;
BLOBBinding.cbMaxLen = 4000;
hr = pIAccessor->CreateAccessor
(
DBACCESSOR_ROWDATA,
1,
&BLOBBinding,
0,
&hAccessorBLOB,
NULL
);
_ASSERT ( SUCCEEDED(hr) );
// Get a Parameter Accessor for the two parameters
memset ((LPVOID)&ParamBinding, 0, sizeof(ParamBinding));
for (i = 0; i < 2; i++)
{
ParamBinding[i] = rgBinding[i];
ParamBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
ParamBinding[i].eParamIO = DBPARAMIO_INPUT;
}
hr = pIAccessor->CreateAccessor
(
DBACCESSOR_PARAMETERDATA,
2,
ParamBinding,
0,
&hAccessorParam,
NULL
);
_ASSERT ( SUCCEEDED(hr) );
}
if ( SUCCEEDED (hr) )
{
pData = (BYTE *)malloc (cbData);
pBLOB = (BYTE *)malloc (4000 + sizeof (ULONG) + sizeof(DBSTATUS));
if (pData)
{
while ( hr == S_OK )
{
hr = pIRowset->GetNextRows (NULL, 0, 1, &cFetched, &phrow );
if ( SUCCEEDED(hr) && cFetched )
{
hr = pIRowset->GetData ( hrow, hAccessor, pData );
if ( SUCCEEDED(hr) )
{
CHAR * pszOwner;
CHAR * pszView;
pszOwner = (CHAR *)*( (BYTE **)(pData + rgBinding[0].obValue) );
pszView = (CHAR *)*( (BYTE **)(pData + rgBinding[1].obValue) );
printf ("OWNER=%-30.30s VIEW_NAME=%-30.30s ", pszOwner, pszView);
if ( strcmp (pszOwner, "SYS") == 0)
{
printf ("(BLOB Skipped...)\n");
}
else
{
hr = FetchBLOB (pICommand, hAccessorParam, pData, hAccessorBLOB, pBLOB);
}
}
pIRowset->ReleaseRows (1, &hrow, NULL, NULL, NULL);
}
}
}
free (rgBinding);
free (pData);
free (pBLOB);
}
return hr;
}
HRESULT FetchBLOB (ICommandText * pICommand, HACCESSOR hAccessorParam,
BYTE *pData, HACCESSOR hAccessorBLOB, BYTE *pBLOB)
{
HRESULT hr = S_OK;
HROW hrow;
HROW * phrow = &hrow;
ULONG cFetched;
IRowset* pIRowset = NULL;
DBPARAMS dbParams;
dbParams.pData = pData;
dbParams.cParamSets = 1;
dbParams.hAccessor = hAccessorParam;
hr = pICommand->Execute
(
NULL,
IID_IRowset,
&dbParams,
NULL,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
if ( SUCCEEDED (hr) )
{
hr = pIRowset->GetNextRows (NULL, 0, 1, &cFetched, &phrow );
if ( SUCCEEDED(hr) && cFetched )
{
hr = pIRowset->GetData ( hrow, hAccessorBLOB, pBLOB );
if ( SUCCEEDED(hr) )
{
printf ("VIEW_TEXT=%s\n\n", *(CHAR **)pBLOB);
}
pIRowset->ReleaseRows (1, &hrow, NULL, NULL, NULL);
}
}
pIRowset->Release ();
}
return hr;
}
Here's another example using ADO, written in Visual Basic. Note the use of the parameterized query and how parameters are defined rather than derived:
Sub DeferredBLOBs()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim rs2 As New ADODB.Recordset
cn.Open "Provider=MSDAORA;Data Source=goliath;User ID=scott;Password=tiger"
rs.CursorLocation = adUseServer
rs.Open "select owner, view_name from all_views", cn, adOpenForwardOnly, adLockReadOnly
cmd.ActiveConnection = cn
cmd.CommandText = "select text from all_views where owner = ? and view_name = ?"
cmd.Parameters.Append cmd.CreateParameter("owner", adVarChar, adParamInput, 30)
cmd.Parameters.Append cmd.CreateParameter("viewname", adVarChar, adParamInput, 30)
rs.MoveFirst
While rs.EOF <> True
Debug.Print rs(0).Value, rs(1).Value,
If (rs(0).Value <> "SYS") Then
cmd(0) = rs(0).Value
cmd(1) = rs(1).Value
Set rs2 = cmd.Execute
Debug.Print rs2(0).Value
Else
Debug.Print "Skipped..."
End If
rs.MoveNext
Wend
rs.Close
cn.Close
End Sub
Oracle does not expose a way to determine parameter information for SQL Data Manipulation Language (DML) commands (SELECT, INSERT, UPDATE, and DELETE), so the provider does not derive it. If you call ICommandWithParameters::GetParameterInfo for a Command object with a SELECT, INSERT, UPDATE, or DELETE statement, you will get a return value of DB_E_PARAMUNAVAILABLE.
That being said, Oracle does expose a way to derive parameter information for PL/SQL stored procedures and functions. There is a significant amount of overhead in the Oracle Call Interface function that is used to do this, so it is strongly recommended that you do not ask the provider to derive this information.
If your application were written for ADO you would simply append parameter definitions instead of calling Command.Parameter.Refresh. See the DeferredBLOB example for the best way to do this.
If your application is written directly to OLE DB, do not call ICommandWithParameters::GetParameterInfo without having first called ICommandWithParameters::SetParameterInfo.
Although not a recommendation specific to the OLE DB Provider for Oracle, it is always better to create DBTYPE_BYREF accessor bindings and use DBMEMOWNER_PROVIDEROWNED. This will cause the provider to hand you a pointer to the data in its row buffer, rather than making a copy in your row buffer.
Note The Client Cursor Engine service component does not support binding data with DBMEMOWNER_PROVIDEROWNED at this time. However, as this article points out, that is not the best performance scenario.
The following C++ code illustrates how to create an accessor with BYREF, PROVIDEROWNED bindings using the information returned from the IColumnsInfo interface. The FetchRowsetForward function will fetch all the data for rowsets created by our GetForwardOnlyRowset routine:
HRESULT CreateFastAccessor
(
IRowset* pIRowset,
HACCESSOR * phAccessor,
ULONG * pcColumns,
DBBINDING ** prgBinding,
ULONG * pcbData
)
{
HRESULT hr = S_OK;
IAccessor * pIAccessor;
IColumnsInfo * pIColumnsInfo;
ULONG cColumns;
DBCOLUMNINFO * rgInfo;
OLECHAR * pStringsBuffer;
ULONG i;
DBBINDING * rgBinding;
*pcColumns = 0;
*prgBinding = NULL;
*pcbData = 0;
hr = pIRowset->QueryInterface (IID_IAccessor, (LPVOID*)&pIAccessor);
_ASSERT ( pIAccessor );
hr = pIRowset->QueryInterface (IID_IColumnsInfo, (LPVOID*)&pIColumnsInfo);
_ASSERT ( pIColumnsInfo );
hr = pIColumnsInfo->GetColumnInfo
(
&cColumns,
&rgInfo,
&pStringsBuffer
);
if ( SUCCEEDED(hr) )
{
rgBinding = (DBBINDING *)malloc (cColumns * sizeof(DBBINDING));
if (rgBinding)
{
memset (rgBinding, 0, (cColumns * sizeof(DBBINDING)) );
for (i = 0; i < cColumns; i++)
{
rgBinding[i].iOrdinal = rgInfo[i].iOrdinal;
rgBinding[i].obValue = *pcbData;
(*pcbData) += sizeof (void *);
rgBinding[i].obLength = *pcbData;
(*pcbData) += sizeof (void *);
rgBinding[i].obStatus = *pcbData;
(*pcbData) += sizeof (void *);
rgBinding[i].dwPart = (DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS);
rgBinding[i].dwMemOwner = DBMEMOWNER_PROVIDEROWNED;
rgBinding[i].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[i].wType = rgInfo[i].wType | DBTYPE_BYREF;
}
hr = pIAccessor->CreateAccessor
(
DBACCESSOR_ROWDATA,
cColumns,
rgBinding,
0,
phAccessor,
NULL
);
if ( FAILED(hr) )
{
free (rgBinding);
rgBinding = NULL;
cColumns = 0;
}
}
}
g_pIMalloc->Free (rgInfo);
g_pIMalloc->Free (pStringsBuffer);
pIColumnsInfo->Release ();
pIAccessor->Release ();
*prgBinding = rgBinding;
*pcColumns = cColumns;
return hr;
}
HRESULT FetchRowsetForward (IRowset* pIRowset)
{
HRESULT hr = S_OK;
HACCESSOR hAccessor;
ULONG cbData;
BYTE * pData;
HROW hrow;
HROW * phrow = &hrow;
ULONG cFetched;
ULONG cColumns;
DBBINDING * rgBinding;
hr = CreateFastAccessor
(
pIRowset,
&hAccessor,
&cColumns,
&rgBinding,
&cbData
);
if ( SUCCEEDED (hr) )
{
pData = (BYTE *)malloc (cbData);
if (pData)
{
while ( hr == S_OK )
{
hr = pIRowset->GetNextRows (NULL, 0, 1, &cFetched, &phrow );
if ( SUCCEEDED(hr) && cFetched )
{
hr = pIRowset->GetData ( hrow, hAccessor, pData );
if ( SUCCEEDED(hr) )
{
hr = PrintColumn ("ENAME", pData, &rgBinding[1]);
}
pIRowset->ReleaseRows (1, &hrow, NULL, NULL, NULL);
}
}
}
free (rgBinding);
free (pData);
}
return hr;
}
There is no support for Unicode in the Oracle Version 7 Call Interface. All data is therefore returned as MultiByte. When fetching character data that does not need to be in Unicode, you can improve performance slightly by choosing DBTYPE_STR bindings instead of DBTYPE_WSTR. In every case, the information returned by IColumnsInfo::GetColumnInfo indicates the optimal binding.
Note Because character data in the schema rowsets are specified as DBTYPE_WSTR, they will be converted for you automatically. Binding them as DBTYPE_STR would force the conversion back to MultiByte strings.
Each time a statement is executed, the provider does some work to prepare for its execution, such as determining rowset columns and preparing for parameter binding. Whenever a statement will be executed more than once, consider using ICommandPrepare to force the preparation of the command to occur only once. When you do not use ICommandPrepare, each execution will have to redo the work.
At this time, the provider implements the base level functionality, and allows you to use service components to get additional behavior.
These are OLE DB interfaces implemented by the provider itself:
Data Source Objects:
CoType TDataSource {
interface IDBCreateSession;
interface IDBInfo;
interface IDBInitialize;
interface IDBProperties;
interface IPersist;
interface ISupportErrorInfo;
}
Sessions:
CoType TSession {
interface IDBCreateCommand;
interface IDBSchemaRowset;
interface IGetDataSource;
interface IOpenRowset;
interface ISessionProperties;
interface ISupportErrorInfo;
interface ITransaction;
interface ITransactionJoin;
interface ITransactionLocal;
}
Commands:
CoType TCommand {
interface IAccessor;
interface IColumnsInfo;
interface ICommand;
interface ICommandPrepare;
interface ICommandProperties;
interface ICommandText;
interface ICommandWithParameters;
interface IConvertType;
interface ISupportErrorInfo;
}
Rowsets:
CoType TRowset {
interface IAccessor;
interface IColumnsInfo;
interface IConnectionPointContainer;
interface IConvertType;
interface IRowset;
interface IRowsetIdentity;
interface IRowsetInfo;
interface ISupportErrorInfo;
}
Transaction Options:
CoType TTransactionOptions {
interface ISupportErrorInfo;
interface ITransactionOptions;
};
These are additional OLE DB interfaces implemented by service components:
Rowsets:
CoType TRowset {
interface IRowsetChange;
interface IRowsetFind;
interface IRowsetLocate;
interface IRowsetScroll;
};
};
You must create your instance with IDataInitialize to use any of these additional interfaces. An example of how to do this is included in the example of how use OLE DB session pooling.
The provider does not natively implement fetching backwards, scrolling backwards, or the IRowsetFind, IRowsetLocate, or IRowsetScroll interfaces. Therefore, applications that require scrollable rowsets must use the client cursor engine to construct one. To do this, set both DBPROP_CANFETCHBACKWARDS and DBPROP_CANSCROLLBACKWARDS to VARIANT_TRUE before creating the rowset.
In our C++ example, you replace the call to UseSession with a call to the GetScrollableRowset function instead:
HRESULT GetScrollableRowset (IUnknown* pUnkSession)
{
HRESULT hr = S_OK;
LPCWSTR pwszTable = L"EMP";
IOpenRowset* pIOpenRowset = NULL;
hr = pUnkSession->QueryInterface (IID_IOpenRowset,
(LPVOID*)&pIOpenRowset);
if (pIOpenRowset)
{
IRowset* pIRowset = NULL;
DBID dbidTemp;
DBPROPSET rgPropset[1];
DBPROP rgProps[3];
dbidTemp.eKind = DBKIND_NAME;
dbidTemp.uGuid.guid = GUID_NULL;
dbidTemp.uName.pwszName = (LPWSTR)pwszTable;
rgPropset[0].rgProperties = rgProps;
rgPropset[0].cProperties = 3;
rgPropset[0].guidPropertySet = DBPROPSET_ROWSET;
rgProps[0].dwPropertyID = DBPROP_CANFETCHBACKWARDS;
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].dwStatus = 0;
VariantInit (&rgProps[0].vValue);
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].vValue.boolVal = VARIANT_TRUE;
rgProps[1].dwPropertyID = DBPROP_CANSCROLLBACKWARDS;
rgProps[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[1].dwStatus = 0;
VariantInit (&rgProps[1].vValue);
rgProps[1].vValue.vt = VT_BOOL;
rgProps[1].vValue.boolVal = VARIANT_TRUE;
rgProps[2].dwPropertyID = DBPROP_ROWSET_ASYNCH;
rgProps[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[2].dwStatus = 0;
VariantInit (&rgProps[2].vValue);
rgProps[2].vValue.vt = VT_I4;
V_I4(&rgProps[2].vValue) = DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION;
hr = pIOpenRowset->OpenRowset
(
NULL,
&dbidTemp,
NULL,
IID_IRowset,
1,
rgPropset,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
hr = FetchRowsetBackward (pIRowset);
pIRowset->Release ();
}
pIOpenRowset->Release ();
VariantClear (&rgProps[0].vValue);
VariantClear (&rgProps[1].vValue);
VariantClear (&rgProps[2].vValue);
}
return hr;
}
Notice that the rowset is instantiated with DBPROP_ROWSET_ASYNCH set to DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION. This instructs the client cursor engine to materialize the rowset asynchronously, enabling your application to regain control much more quickly when the rowset is very large.
If you write your application in ADO, you request a CursorLocation of adUseClient and a CursorType of adOpenStatic. Here's an example written in Visual Basic:
Sub Scrollable()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDAORA;Data Source=goliath;User ID=scott;Password=tiger"
rs.CursorLocation = adUseClient
rs.Open "emp", cn, adOpenStatic, adLockReadOnly, adAsyncFetch
rs.MoveLast
While rs.BOF <> True
Debug.Print rs(0).Value, rs(1).Value
rs.MovePrevious
Wend
rs.Close
cn.Close
End Sub
In this example, the use of adAsyncFetch instructs the client cursor engine to materialize the rowset asynchronously, enabling your application to regain control much more quickly when the rowset is very large.
The provider does not natively implement the IRowsetChange interface. Therefore, applications that must perform updates and do not wish to make discreet update statements must ask the client cursor engine to construct an updatable rowset. To do this, set DBPROP_IROWSETCHANGE to VARIANT_TRUE before creating the rowset.
In our C++ example, you replace the call to UseSession with a call to the GetUpdatableRowset function instead:
HRESULT GetUpdatableRowset (IUnknown* pUnkSession)
{
HRESULT hr = S_OK;
LPCWSTR pwszTable = L"EMP";
IOpenRowset* pIOpenRowset = NULL;
hr = pUnkSession->QueryInterface (IID_IOpenRowset,
(LPVOID*)&pIOpenRowset);
if (pIOpenRowset)
{
IRowset * pIRowset = NULL;
DBID dbidTemp;
DBPROPSET rgPropset[1];
DBPROP rgProps[2];
dbidTemp.eKind = DBKIND_NAME;
dbidTemp.uGuid.guid = GUID_NULL;
dbidTemp.uName.pwszName = (LPWSTR)pwszTable;
rgPropset[0].rgProperties = rgProps;
rgPropset[0].cProperties = 2;
rgPropset[0].guidPropertySet = DBPROPSET_ROWSET;
rgProps[0].dwPropertyID = DBPROP_IRowsetChange;
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].dwStatus = 0;
VariantInit (&rgProps[0].vValue);
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].vValue.boolVal = VARIANT_TRUE;
rgProps[1].dwPropertyID = DBPROP_ROWSET_ASYNCH;
rgProps[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[1].dwStatus = 0;
VariantInit (&rgProps[1].vValue);
rgProps[1].vValue.vt = VT_I4;
V_I4(&rgProps[1].vValue) = DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION;
hr = pIOpenRowset->OpenRowset
(
NULL,
&dbidTemp,
NULL,
IID_IRowset,
1,
rgPropset,
(IUnknown **)&pIRowset
);
if (pIRowset)
{
hr = FetchRowsetForward (pIRowset);
pIRowset->Release ();
}
pIOpenRowset->Release ();
VariantClear (&rgProps[0].vValue);
VariantClear (&rgProps[1].vValue);
}
return hr;
}
Notice that the rowset is instantiated with DBPROP_ROWSET_ASYNCH set to DBPROPVAL_ASYNCH_BACKGROUNDPOPULATION. This instructs the client cursor engine to materialize the rowset asynchronously, enabling your application to regain control much more quickly when the rowset is very large.
If you write your application in ADO, you request a CursorLocation of adUseClient, a CursorType of adOpenStatic, and a LockType of adLockOptimistic. Here's an example written in Visual Basic:
Sub Updatable()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDAORA;Data Source=goliath;User ID=scott;Password=tiger"
rs.CursorLocation = adUseClient
rs.Open "emp", cn, adOpenStatic, adLockOptimistic, adAsyncFetch
rs.MoveFirst
While rs.EOF <> True
Debug.Print rs(0).Value, rs(1).Value
rs(0).Value = rs(0).Value
rs.Update
rs.MoveNext
Wend
rs.Close
cn.Close
End Sub
In this example, the use of adAsyncFetch instructs the client cursor engine to materialize the rowset asynchronously, enabling your application to regain control more quickly when the rowset is large.
The provider supports two methods of calling a stored procedure or function: the canonical syntax defined in the ODBC specification; and the Oracle native syntax, PL/SQL.
Canonical syntax:
{call spfoo [(arg…)]}
Oracle native (PL/SQL) syntax:
begin
spfoo [{arg…)]
end;
When using canonical syntax, parameters can be derived. When using Oracle native syntax, however, it is impractical to derive parameter information because it would require parsing of the PL/SQL code in the provider.
Likewise, when using the canonical syntax, default parameter values can be specified by binding them with a status of DBSTATUS_S_DEFAULT. When using the PL/SQL syntax, you must use named notation and not include bindings for those parameters you wish to specify defaults for.
Oracle PL/SQL LONG and LONG RAW data types cannot hold more then 32,760 bytes of data, even though their name implies that they can support up to 2 gigabytes. This limitation is well documented by Oracle, but it is a common mistake to attempt to insert binary large object (BLOB) data into the database through a stored procedure, where it is truncated to 32,760 bytes.
If you need to work with LONG or LONG RAW values that exceed this limitation, you must use SQL SELECT, INSERT, or UPDATE statements instead of a PL/SQL stored procedure.
A few features are not currently exposed in the Microsoft OLE DB Provider for Oracle. Some are Oracle version 8 features that cannot be exposed through the Oracle 7 Call Interface used by the provider.
There is no way to expose an NCHAR or NVARCHAR through the Oracle 7 Call Interface. Therefore, you cannot insert into or update values in fields defined as NCHAR or NVARCHAR. A SELECT operation should work, however, because Oracle will perform the necessary conversion.
In Oracle 8, the maximum size of a VARCHAR column was increased from 2,000 to 4,000 bytes. However, the Oracle 7 client software has no way to bind a parameter value larger 2,000 bytes. If you create a table with a VARCHAR column of larger than 2,000 bytes, you will be unable to perform parameterized inserts, updates, deletes, and queries against it with data that exceeds the 2,000-byte limit of the client software. Because both the ODBC Driver for Oracle and the OLE DB Provider for Oracle support parameterized inserts, updates, deletes, and queries, they will report ORA-01026 errors in this case. Data that is within the limits enforced by the Oracle client software will work, however.
The workaround is to update your client software to Oracle 8 (8.0.4.1.1c or later).
Oracle exposes a method to pass cursor references as input and output parameters to PL/SQL stored procedures. When you create a stored procedure and specify a parameter with data type REF CURSOR, you can bind an OCI cursor to the parameter.
You might expect the OLE DB Provider for Oracle to let you define these as DBTYPE_IUNKOWN, and then bind them with the pObject element in the DBBINDING structure set to IID_IRowset. However, because some issues remain to be resolved, this version of the provider does not expose this feature.
Use the OLE DB Provider for ODBC Drivers and the Microsoft ODBC Driver for Oracle to get this behavior.
At this time, the provider does not support nested transactions, which is how it would expose save points.