www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Data Access Interfaces

Virtuoso .NET Data Provider
Interactive SQL Utility
Virtuoso Driver for ODBC
Virtuoso Driver for JDBC
OLE DB Provider for Virtuoso
Using the OLE DB Provider for Virtuoso Known Limitations Data Types Metadata Supported Interfaces Data Source Objects Sessions Rowsets
Virtuoso In-Process Client
Unix Domain Socket Connections

8.5. OLE DB Provider for Virtuoso

OLE DB is an open data access technology developed and promoted by Microsoft. It allows for uniform data access across diverse data sources including but not limited to conventional RDBMSes. Based on the COM architecture it provides very flexible and exhaustive set of interfaces any database application developer might ever need. At the same time, OLE DB is somewhat overcomplicated and therefore is rarely used directly. In the majority of cases people use ADO(+) instead, ADO is another, somewhat simpler, of Microsoft's data access technologies. However, ADO itself is based upon OLE DB, thus those who use ADO make indirect use of OLE DB as well.

With the advent of ADO.NET, OLE DB is no longer as an much essential part of the overall Microsoft data access architecture as it used to be. However it still remains important and useful working together with the Microsoft OLE DB .NET Data Provider.

The OLE DB Provider for Virtuoso (VIRTOLEDB) gives OLE DB and ADO applications access to the Virtuoso databases. In principle, the same thing is possible through the conjunction of the Virtuoso ODBC driver and Microsoft OLE DB Provider for ODBC. However, VIRTOLEDB provides native OLE DB access which is more complete and more efficient. Therefore it is preferable for this purpose.

8.5.1. Using the OLE DB Provider for Virtuoso

Being a COM in-process server VIRTOLEDB has to be installed on the client computer. When VIRTOLEDB is properly installed it can be used by a host of existing applications thanks to the support of standard OLE DB interfaces.

8.5.1.1. System Requirements

VIRTOLEDB requires the following software:


8.5.1.2. Installing the Provider

VIRTOLEDB is installed as a part of Virtuoso installation on a Windows platform. The following files pertain to the OLE DB Provider for Virtuoso.

Table: 8.5.1.2.1. OLE DB Provider for Virtuoso Files
File Description
virtoledb.dll DLL that implements the VIRTOLEDB provider.
virtoledb.h Header file for development of VIRTOLEDB consumers.

The installation procedure, in addition to placing VIRTOLEDB files on a client computer, also registers VIRTOLEDB in the system to make it known as a COM server. This also makes it available through the OLE DB root enumerator object and the Data Links component.


8.5.1.3. Invoking the Provider

Applications that utilize OLE DB Data Links component can use VIRTOLEDB without any specific measures. Applications that need to directly invoke VIRTOLEDB should follow examples provided in this section.

8.5.1.3.1. Using Data Link User Interface

Data Links is a user interface component for connecting to OLE DB data sources and constructing ADO-style connection strings. It belongs to OLE DB core components and is a part of MDAC. It is used my many applications including development environments like MS Visual Studio.

VIRTOLEDB uses the extension mechanism of the Data Link API and provides a customized version of the Data Link connection page.

Data Link Provider Page
Figure: 8.5.1.3.1.1. Data Link Provider Page
Data Link Connection Page
Figure: 8.5.1.3.1.2. Data Link Connection Page

8.5.1.3.2. Using COM and OLE DB Interfaces Directly

Applications that utilize OLE DB Data Links component can use VIRTOLEDB without any specific measures. Applications that need to directly invoke VIRTOLEDB should follow examples provided in this section.

#define INITGUID
#include "virtoledb.h"

..

  IDBInitialize* pIDBInitialize = NULL;
  HRESULT hr = CoCreateInstance(CLSID_VIRTOLEDB, NULL, CLSCTX_INPROC_SERVER,
                                IID_IDBInitialize, (void**) &pIDBInitialize);
  if (FAILED(hr))
    goto EXIT;

8.5.1.3.3. ADO Applications
Dim strConn As String
Dim objConn As ADODB.Connection

strConn = "Provider=VIRTOLEDB;Data Source=localhost:1111;User Id=dba;Password=dba;Initial Catalog=Demo;Prompt=NoPrompt;"

Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseServer
objConn.Open strConn



8.5.2. Known Limitations


8.5.3. Data Types

8.5.3.1. Data Type Mappings in Rowsets and Parameters

The methods that provide information about rowset columns and command parameters (IColumnsInfo::GetColumnInfo, ICommandWithParameters::GetParameterInfo) use the mapping of Virtuoso data types into OLE DB data types shown in the following table.

Table: 8.5.3.1.1. Data Type Mappings
Virtuoso Type OLE DB Type
CHAR DBTYPE_STR
VARCHAR DBTYPE_STR
LONG VARCHAR DBTYPE_STR
NCHAR DBTYPE_WSTR
NVARCHAR DBTYPE_WSTR
LONG NVARCHAR DBTYPE_WSTR
NUMERIC DBTYPE_NUMERIC
DECIMAL DBTYPE_NUMERIC
SMALLINT DBTYPE_I2
INTEGER DBTYPE_I4
FLOAT DBTYPE_R8
DOUBLE DBTYPE_R8
REAL DBTYPE_R4
VARBINARY DBTYPE_BYTES
LONG VARBINARY DBTYPE_BYTES
DATE DBTYPE_DBDATE
TIME DBTYPE_DBTIME
DATETIME DBTYPE_DBTIMESTAMP
TIMESTAMP BINARY


8.5.3.2. Data Type Conversions

Applications can bind column and parameter values using data types different from those described in . In such cases VIRTOLEDB uses OLE DB Data Conversion Library. See MDAC documentation for the list of supported data type conversions.


8.5.3.3. Long Data Types

Long data types include LONG VARCHAR, LONG NVARCHAR, and LONG VARBINARY. A long value can only be bound to a buffer that have one of these OLE DB types:

Other type conversions are not supported.

If a long data is bound to a DBTYPE_IUNKNOWN type, this implies the use of the ISequentialStream interface. VIRTOLEDB supports the ISequentialStream::Read method both when getting and setting data. The ISequentialStream::Write method is never supported.



8.5.4. Metadata

8.5.4.1. Schema Rowsets

VIRTOLEDB supports schema rowsets listed in the following table.

Table: 8.5.4.1.1. Supported Schema Rowsets
Schema Rowset Supported Restrictions
DBSCHEMA_CATALOGS All (CATALOG_NAME)
DBSCHEMA_COLUMN_PRIVILEGES All (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, GRANTOR, GRANTEE)
DBSCHEMA_COLUMNS All (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
DBSCHEMA_FOREIGN_KEYS All (PK_TABLE_CATALOG, PK_TABLE_SCHEMA, PK_TABLE_NAME, FK_TABLE_CATALOG, FK_TABLE_SCHEMA, FK_TABLE_NAME)
DBSCHEMA_INDEXES 1, 2, 3, and 5 (TABLE_CATALOG, TABLE_SCHEMA, INDEX_NAME, TABLE_NAME)
DBSCHEMA_PRIMARY_KEYS All (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
DBSCHEMA_PROCEDURE_PARAMETERS All (PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME, PARAMETER_NAME)
DBSCHEMA_PROCEDURES All (PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME, PROCEDURE_TYPE)
DBSCHEMA_PROVIDER_TYPES All (DATA_TYPE, BEST_MATCH)
DBSCHEMA_SCHEMATA All (CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER)
DBSCHEMA_TABLE_PRIVILEGES All (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, GRANTOR, GRANTEE)
DBSCHEMA_TABLES All (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)


8.5.4.2. Columns Rowset

VIRTOLEDB supports the following columns in the columns rowset.



8.5.5. Supported Interfaces

VIRTOLEDB supports the interfaces listed in the following table.

Table: 8.5.5.1. Supported Interfaces
Object Interface Notes
Data Source IDBCreateSession
IDBInitialize
IDBProperties
IPersist
IDBInfo
IPersistFile
ISupportErrorInfo
Session IGetDataSource
IOpenRowset
ISessionProperties
IDBCreateCommand
IDBSchemaRowset
ISupportErrorInfo
ITransaction
ITransactionJoin
ITransactionLocal
Command IAccessor
IColumnsInfo
ICommand
ICommandProperties
ICommandText
IConvertType
IColumnsRowset
ICommandPrepare
ICommandWithParameters
ISupportErrorInfo
Multiple Results IMultipleResults
ISupportErrorInfo
Rowset IAccessor
IColumnsInfo
IConvertType
IRowset
IRowsetInfo
IColumnsRowset
IConnectionPointContainer For IRowsetNotify interface.
IRowsetChange
IRowsetIdentity
IRowsetLocate
IRowsetRefresh
IRowsetResynch
IRowsetScroll
IRowsetUpdate
ISupportErrorInfo
Error Lookup IErrorLookup


8.5.6. Data Source Objects

8.5.6.1. Initialization and Authorization Properties

VIRTOLEDB supports the following properties in the initialization property group.

Table: 8.5.6.1.1. Supported Initialization and Authorization Properties
Property R/W Default Notes
DBPROP_AUTH_PASSWORD Read/Write General Info: Password
DBPROP_AUTH_USERID Read/Write User ID
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO Read/Write VARIANT_FALSE Persist Security Info
DBPROP_INIT_DATASOURCE Read/Write Data Source
DBPROP_INIT_CATALOG Read/Write Initial Catalog
DBPROP_INIT_PROVIDERSTRING Read/Write Extended Properties
DBPROP_INIT_HWND Read/Write Window Handle
DBPROP_INIT_PROMPT Read/Write DBPROMPT_COMPLETE Prompt
DBPROP_INIT_TIMEOUT Read/Write 0 Connect Timeout

In addition, VIRTOLEDB implements a provider-specific property set DBPROPSET_VIRTUOSODBINIT with the following properties

Table: 8.5.6.1.2. Provider-Specific Initialization and Authorization Properties
Property R/W Default Notes
VIRTPROP_INIT_ENCRYPT Read/Write VARIANT_FALSE

Type: VT_BOOL

Description: Encrypt Connection

If this property is set to VARIANT_TRUE the provider uses SSL encrypted connections.

VIRTPROP_AUTH_PKCS12FILE Read/Write

Type: VT_BSTR

Description: PKCS #12 File

If this property is set to a non-empty string then it is used as a name of a PKCS #12 file that authenticates the client. This also implies that the VIRTPROP_INIT_ENCRYPT property is set to VARIANT_TRUE



8.5.6.2. Data Source Properties

VIRTOLEDB supports the following properties in the data source property group.

Table: 8.5.6.2.1. Supported Data Source Properties
Property R/W Default Notes
DBPROP_CURRENTCATALOG Read/Write Current Catalog
DBPROP_MULTIPLECONNECTIONS Read/Write Multiple Connections


8.5.6.3. Data Source Information Properties

VIRTOLEDB supports the following properties in the data source information property group.

Table: 8.5.6.3.1. Supported Data Source Information Properties
Property R/W Default Notes
DBPROP_ACTIVESESSIONS Read-Only The number of connections the Virtuoso server is licensed to.
DBPROP_ASYNCTXNABORT Read-Only VARIANT_FALSE VIRTOLEDB cannot abort transactions asynchronously.
DBPROP_ASYNCTXNCOMMIT Read-Only VARIANT_FALSE VIRTOLEDB cannot commit transactions asynchronously.
DBPROP_BYREFACCESSORS Read-Only VARIANT_FALSE VIRTOLEDB does not support reference accessors.
DBPROP_CATALOGLOCATION Read-Only DBPROPVAL_CL_START
DBPROP_CATALOGTERM Read-Only "qualifier"
DBPROP_CATALOGUSAGE Read-Only DBPROPVAL_CU_DML_STATEMENTS | DBPROPVAL_CU_TABLE_DEFINITION | DBPROPVAL_CU_INDEX_DEFINITION | DBPROPVAL_CU_PRIVILEGE_DEFINITION
DBPROP_COLUMNDEFINITION Read-Only DBPROPVAL_CD_NOTNULL
DBPROP_COMSERVICES Read-Only 0
DBPROP_CONCATNULLBEHAVIOR Read-Only DBPROPVAL_CB_NULL
DBPROP_CONNECTIONSTATUS Read-Only DBPROPVAL_CS_INITIALIZED
DBPROP_DATASOURCENAME Read-Only N/A
DBPROP_DATASOURCEREADONLY Read-Only VARIANT_FALSE
DBPROP_DATASOURCE_TYPE Read-Only DBPROPVAL_DST_TDP
DBPROP_DBMSNAME Read-Only "Virtuoso"
DBPROP_DBMSVER Read-Only
DBPROP_DSOTHREADMODEL Read-Only DBPROPVAL_RT_FREETHREAD
DBPROP_GROUPBY Read-Only DBPROPVAL_GB_NO_RELATION
DBPROP_HETEROGENEOUSTABLES Read-Only DBPROPVAL_HT_DIFFERENT_CATALOGS
DBPROP_IDENTIFIERCASE Read-Only DBPROPVAL_IC_SENSITIVE
DBPROP_MAXINDEXSIZE Read-Only 2000
DBPROP_MAXOPENCHAPTERS Read-Only 0
DBPROP_MAXROWSIZE Read-Only 2000
DBPROP_MAXROWSIZEINCLUDESBLOB Read-Only VARIANT_FALSE
DBPROP_MAXTABLESINSELECT Read-Only 0
DBPROP_MULTIPLEPARAMSETS Read-Only VARIANT_TRUE
DBPROP_MULTIPLERESULTS Read-Only DBPROPVAL_MR_SUPPORTED
DBPROP_MULTIPLESTORAGEOBJECTS Read-Only VARIANT_FALSE
DBPROP_MULTITABLEUPDATE Read-Only VARIANT_TRUE
DBPROP_NULLCOLLATION Read-Only DBPROPVAL_NC_HIGH
DBPROP_OLEOBJECTS Read-Only DBPROPVAL_OO_BLOB
DBPROP_OPENROWSETSUPPORT Read-Only DBPROPVAL_ORS_TABLE
DBPROP_ORDERBYCOLUMNSINSELECT Read-Only VARIANT_FALSE
DBPROP_OUTPUTPARAMETERAVAILABILITY Read-Only DBPROPVAL_OA_ATROWRELEASE
DBPROP_PERSISTENTIDTYPE Read-Only DBPROPVAL_PT_NAME
DBPROP_PREPAREABORTBEHAVIOR Read-Only DBPROPVAL_CB_PRESERVE
DBPROP_PREPARECOMMITBEHAVIOR Read-Only DBPROPVAL_CB_PRESERVE
DBPROP_PROCEDURETERM Read-Only "procedure"
DBPROP_PROVIDERFRIENDLYNAME Read-Only "OpenLink OLE DB Provider for Virtuoso"
DBPROP_PROVIDERMEMORY Read-Only VARIANT_TRUE
DBPROP_PROVIDERFILENAME Read-Only "virtoledb.dll"
DBPROP_PROVIDEROLEDBVER Read-Only "02.60"
DBPROP_PROVIDERVER Read-Only
DBPROP_QUOTEDIDENTIFIERCASE Read-Only DBPROPVAL_IC_SENSITIVE
DBPROP_ROWSETCONVERSIONSONCOMMAND Read-Only VARIANT_TRUE
DBPROP_SCHEMATERM Read-Only "owner"
DBPROP_SCHEMAUSAGE Read-Only DBPROPVAL_SU_DML_STATEMENTS | DBPROPVAL_SU_TABLE_DEFINITION | DBPROPVAL_SU_INDEX_DEFINITION | DBPROPVAL_SU_PRIVILEGE_DEFINITION
DBPROP_SERVERNAME Read-Only
DBPROP_SQLSUPPORT Read-Only DBPROPVAL_SQL_ODBC_MINIMUM | DBPROPVAL_SQL_ODBC_CORE | DBPROPVAL_SQL_ANSI89_IEF | DBPROPVAL_SQL_ESCAPECLAUSES | DBPROPVAL_SQL_ANSI92_ENTRY
DBPROP_STRUCTUREDSTORAGE Read-Only DBPROPVAL_SS_ISEQUENTIALSTREAM
DBPROP_SUBQUERIES Read-Only DBPROPVAL_SQ_CORRELATEDSUBQUERIES | DBPROPVAL_SQ_COMPARISON | DBPROPVAL_SQ_EXISTS | DBPROPVAL_SQ_IN | DBPROPVAL_SQ_QUANTIFIED | DBPROPVAL_SQ_TABLE
DBPROP_SUPPORTEDTXNDDL Read-Only DBPROPVAL_TC_DML
DBPROP_SUPPORTEDTXNISOLEVELS Read-Only DBPROPVAL_TI_READUNCOMMITTED | DBPROPVAL_TI_READCOMMITTED | DBPROPVAL_TI_REPEATABLEREAD | DBPROPVAL_TI_SERIALIZABLE
DBPROP_SUPPORTEDTXNISORETAIN Read-Only DBPROPVAL_TR_DONTCARE
DBPROP_TABLESTATISTICS Read-Only 0
DBPROP_TABLETERM Read-Only "table"
DBPROP_USERNAME Read-Only N/A



8.5.7. Sessions

8.5.7.1. Session Properties

VIRTOLEDB supports the following properties in the session property group.

Table: 8.5.7.1.1. Supported Session Properties
Property R/W Default Notes
DBPROP_SESS_AUTOCOMMITISOLEVELS Read/Write DBPROPVAL_TI_REPEATABLEREAD Isolation level in auto-commit mode.



8.5.8. Rowsets

8.5.8.1. Properties

Table: 8.5.8.1.1. Supported Rowset Properties
Property R/W Default Notes
DBPROP_ABORTPRESERVE Read/Write
DBPROP_ACCESSORDER Read-Only DBPROPVAL_AO_RANDOM
DBPROP_BLOCKINGSTORAGEOBJECTS Read-Only VARIANT_TRUE
DBPROP_BOOKMARKINFO Read-Only DBPROPVAL_BI_CROSSROWSET
DBPROP_BOOKMARKS Read/Write VARIANT_FALSE
DBPROP_BOOKMARKSKIPPED Read-Only VARIANT_FALSE
DBPROP_BOOKMARKTYPE Read-Only DBPROPVAL_BMK_NUMERIC
DBPROP_CACHEDEFERRED Read-Only VARIANT_FALSE
DBPROP_CANFETCHBACKWARDS Read/Write VARIANT_FALSE
DBPROP_CANHOLDROWS Read/Write VARIANT_FALSE
DBPROP_CANSCROLLBACKWARDS Read/Write VARIANT_FALSE
DBPROP_CHANGEINSERTEDROWS Read-Only VARIANT_FALSE
DBPROP_COLUMNRESTRICT Read-Only VARIANT_FALSE
DBPROP_COMMANDTIMEOUT Read/Write
DBPROP_COMMITPRESERVE Read/Write VARIANT_FALSE
DBPROP_DEFERRED Read-Only VARIANT_FALSE or VARIANT_TRUE
DBPROP_DELAYSTORAGEOBJECTS Read/Write VARIANT_FALSE
DBPROP_FINDCOMPAREOPS Read-Only 0
DBPROP_HIDDENCOLUMNS Read-Only
DBPROP_IAccessor Read-Only VARIANT_TRUE
DBPROP_IColumnsInfo Read-Only VARIANT_TRUE
DBPROP_IColumnsRowset Read/Write VARIANT_FALSE
DBPROP_IConnectionPointContainer Read/Write VARIANT_FALSE
DBPROP_IConvertType Read-Only VARIANT_TRUE
DBPROP_IMMOBILEROWS Read/Write VARIANT_FALSE
DBPROP_IMultipleResults Read/Write VARIANT_FALSE
DBPROP_IRowset Read-Only VARIANT_TRUE
DBPROP_IRowsetChange Read/Write VARIANT_FALSE
DBPROP_IRowsetIdentity Read/Write VARIANT_FALSE
DBPROP_IRowsetInfo Read-Only VARIANT_TRUE
DBPROP_IRowsetLocate Read/Write VARIANT_FALSE
DBPROP_IRowsetRefresh Read/Write VARIANT_FALSE
DBPROP_IRowsetResynch Read/Write VARIANT_FALSE
DBPROP_IRowsetScroll Read/Write VARIANT_FALSE
DBPROP_IRowsetUpdate Read/Write VARIANT_FALSE
DBPROP_ISequentialStream Read-Only VARIANT_TRUE
DBPROP_ISupportErrorInfo Read/Write VARIANT_FALSE
DBPROP_LITERALBOOKMARKS Read-Only VARIANT_FALSE
DBPROP_LITERALIDENTITY Read-Only VARIANT_TRUE
DBPROP_LOCKMODE Read/Write DBPROPVAL_LM_NONE
DBPROP_MAXOPENROWS Read-Only 0
DBPROP_MAXPENDINGROWS Read-Only 0
DBPROP_MAXROWS Read/Write 0
DBPROP_NOTIFICATIONGRANULARITY Read-Only DBPROPVAL_NT_SINGLEROW
DBPROP_NOTIFICATIONPHASES Read-Only DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO | DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT
DBPROP_NOTIFYCOLUMNSET DBPROP_NOTIFYROWDELETE DBPROP_NOTIFYROWFIRSTCHANGE DBPROP_NOTIFYROWINSERT DBPROP_NOTIFYROWRESYNCH DBPROP_NOTIFYROWSETCHANGED DBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE DBPROP_NOTIFYROWSETRELEASE DBPROP_NOTIFYROWUNDOCHANGE DBPROP_NOTIFYROWUNDODELETE DBPROP_NOTIFYROWUNDOINSERT DBPROP_NOTIFYROWUPDATE Read-Only DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO | DBPROPVAL_NP_SYNCHAFTER
DBPROP_ORDEREDBOOKMARKS Read/Write VARIANT_FALSE
DBPROP_OTHERINSERT Read/Write VARIANT_FALSE
DBPROP_OTHERUPDATEDELETE Read/Write VARIANT_FALSE
DBPROP_OWNINSERT Read/Write VARIANT_TRUE
DBPROP_OWNUPDATEDELETE Read/Write VARIANT_TRUE
DBPROP_QUICKRESTART Read/Write VARIANT_FALSE
DBPROP_REENTRANTEVENTS Read-Only VARIANT_FALSE
DBPROP_REMOVEDELETED Read/Write VARIANT_FALSE
DBPROP_REPORTMULTIPLECHANGES Read-Onle VARIANT_FALSE
DBPROP_RETURNPENDINGINSERTS Read-Only VARIANT_FALSE
DBPROP_ROWRESTRICT Read-Only VARIANT_FALSE
DBPROP_ROWSET_ASYNCH Read-Only VARIANT_FALSE
DBPROP_ROWTHREADMODEL Read-Only DBPROPVAL_RT_FREETHREAD
DBPROP_SERVERCURSOR Read-Only VARIANT_TRUE
DBPROP_SERVERDATAONINSERT Read-Only VARIANT_FALSE
DBPROP_SKIPROWCOUNTRESULTS Read-Only VARIANT_TRUE
DBPROP_STRONGIDENTITY Read-Only VARIANT_FALSE
DBPROP_TRANSACTEDOBJECT Reaq-Only VARIANT_FALSE
DBPROP_UNIQUEROWS Read/Write VARIANT_FALSE
DBPROP_UPDATABILITY Read/Write 0


8.5.8.2. Interfaces

VIRTOLEDB supports the following rowset interfaces.

Table: 8.5.8.2.1. Supported Rowset Interfaces
Interface Description
IAccessor
IColumnsInfo
IConvertType
IRowset
IRowsetChange
IRowsetIdentity
IRowsetInfo
IRowsetLocate
IRowsetScroll
ISupportErrorInfo