Known Limitations
The following are known limitations with the ODBC Provider:
General
-
The ODBC Provider does not return precision correctly for any numeric data types except DBTYPE_NUMERIC and DBTYPE_DECIMAL. For example, the COLUMNS schema rowset, IColumnsInfo::GetColumnInfo, and IColumnsRowset::GetColumnsRowset return a precision of zero for integer data types.
-
The ODBC Desktop Database Driver cannot start a transaction when it has a rowset open. The ODBC Provider returns E_FAIL if ITransactionLocal::StartTransaction is called when any rowset is held open by this driver. This limitation does not necessarily affect other ODBC drivers.
-
The ODBC Provider does not quote table names passed through DBIDs; for instance, in IOpenRowset. Therefore, when trying to open a table against the ODBC Provider that requires quoting the name (such as when the table name contains extended characters), the consumer must add the quotes to the table name manually or simply execute "Select * from <quoted table name>".
-
Query-based updates cannot be used to update BLOB data or other long data.
-
The query-based update scheme implemented in the ODBC Provider may not allow for correct repositioning in the rowset after performing an update involving the key columns which were used to generate a keyset cursor-based rowset.
-
The ODBC Provider does not support output or input/output parameters as storage objects.
-
Using the ODBC Provider, if the underlying ODBC driver does not support SQLDescribeParam, and the OLE DB consumer does not specify the type of the parameter, the ODBC Provider will convert Boolean parameter values to "-1" and "0" when the data type of the parameter is string. This is in contrast to the OLE DB Programmer's Reference, which calls for Booleans converted to string data types to appear as the strings "True" or "False". In order to ensure proper conversion against ODBC drivers that do not support describing parameters, the OLE DB consumer should always call SetParameterInfo to specify the types of parameters.
-
If DBMEMOWNER_PROVIDEROWNED was set in dwMemOwner in a binding structure and the binding structure binds to a long data column, the ODBC Provider returns DB_E_BADBINDINFO.
-
If DBPROP_REMOVEDELETED is set to VARIANT_TRUE on the rowset and a dynamic ODBC cursor is requested and the ODBC Provider cannot provide this type of cursor, any deleted rows scheduled to be removed from the rowset are removed at fetch time. In this situation, it is possible for row-fetching methods (such as GetNextRows or GetRowsAt) to return both S_OK and fewer rows than requested. This irregular behavior is caused by the ODBC Provider requirement to maintain a one-to-one correspondence between OLE DB and ODBC rowsets. Note that this behavior does not signify the DB_S_ENDOFROWSET condition and that the number of rows returned by the fetching method will never be zero if there are any remaining rows (although the number of rows fetched may be smaller than the number requested).
-
With non-ODBC 3.x drivers, the ODBC Provider cannot return bookmarks on newly inserted rows. The ODBC Provider marks the status of the bookmark column of the newly inserted row as DBSTATUS_E_UNAVAILABLE. Since ODBC 3.x drivers retrieve bookmarks, updates can be performed on newly inserted rows.
-
The ODBC Provider does not allow BLOB data to be set via ISequentialStream on newly inserted rows.
-
In a deferred update mode, the ODBC Provider does not support retrieving data set into blob columns via ISequentialStream, unless that data has been updated on the provider using IRowsetUpdate::Update.
-
When performing deferred updates with the DBCOLUMNFLAGS_ISLONG flag, the following two limitations exist:
-
If you call SetData for a BLOB column bound as DBTYPE_IUNKNOWN, you cannot retrieve the interface pointer in subsequent calls to GetData. After calling IRowsetUpdate::Update, you can retrieve the value as usual.
-
If you call IRowsetUpdate::SetData for a BLOB column not bound in the accessor as DBTYPE_IUNKNOWN, you may retrieve that value as any legal binding other than DBTYPE_IUNKNOWN. After calling IRowsetUpdate::Update, you may retrieve the value in the usual manner.
Microsoft ODBC Driver for Oracle
There are currently no known limitations to using this driver with version 3.0 of the ODBC Provider.
Microsoft SQL Server ODBC Driver
-
When using a Microsoft SQL Server database as the data source, if the consumer specifies a long column in the WHERE clause of a SELECT statement, the long column cannot contain more than 255 characters.
-
Using a Microsoft SQL Server database as the data source, if the consumer uses SetData to update a BLOB column through an ISequentialStream interface, the length part of the binding must contain the total length of the BLOB data. This differs from other data sources, which require the length part of the binding to contain only the length of the pointer to the ISequentialStream object.
-
Using a Microsoft SQL Server database as the data source, if the consumer opens a forward-only cursor using a query-based update, any method of the IRowsetChange interface may cause a deadlock situation.
The following schema and schema restrictions are supported through the Microsoft SQL Server ODBC driver.
| Supported schema |
Supported restrictions |
| CATALOGS |
None |
| COLUMNS |
All |
| TABLES |
All |
| PROVIDER_TYPES |
None |
| INDEXES |
1, 2, and 5 |
| PROCEDURES |
All |
| PROCEDURE_PARAMETERS |
All |
| COLUMN_PRIVILEGES |
1, 2, 3, and 4 |
| FOREIGN_KEYS |
All |
| PRIMARY_KEYS |
All |
| SCHEMATA |
None |
Microsoft Access ODBC Driver
-
When a consumer specifies a value to be inserted or updated in a command that causes a data overflow, but has not called ICommandWithParameters::SetParameterInfo, and the provider cannot determine the column type used by the data source, the ODBC Provider returns DB_E_DATAOVERFLOW (rather than DB_E_ERRORSOCCURRED with the offending parameter marked with the DBSTATUS_E_DATAOVERFLOW flag). This occurs because the ODBC Provider cannot detect which parameter caused the overflow error.
-
In delayed-update mode, consumers who attempt to delete or update rows in a read-only rowset receive the status DBROWSTATUS_DELETED. This differs from the expected status, which is DBROWSTATUS_INTEGRITYVIOLATION.
-
The rowset property DBPROP_MAXROWS does not affect the number of rows returned by the Microsoft Access ODBC driver.
-
When run against the Access driver, the ODBC Provider reports output parameter availability as DBPROPVAL_OA_ATROWRELEASE, rather than the expected value of DBPROPVAL_OA_NOTSUPPORTED.
The following schema and schema restrictions are supported through the Microsoft Access ODBC driver.
| Supported schema |
Supported restrictions |
| CATALOGS |
None |
| COLUMNS |
All |
| TABLES |
All |
| PROVIDER_TYPES |
None |
| INDEXES |
1, 2, and 5 |
| PROCEDURE_PARAMETERS |
All |
| PROCEDURE_COLUMNS |
All |
Notifications
-
On the first change to a row from SetData, the ODBC Provider does not interleave the SynchAfter and DidEvent notifications. The ordering of OKTODO and ABOUTTODO is correct.
Rowset Properties
-
The rowset properties DBPROP_OTHERINSERT and DBPROP_CANHOLDROWS cannot be used together. If you attempt to create a rowset with this combination of properties, the ODBC Provider returns DB_E_ERRORSOCCURRED and returns _NOTSUPPORTED for both properties.
Setting Read-Only Properties to Their Default Values
-
The ODBC Provider does not support the OLE DB version 1.1 behavior of allowing consumers to set the value of a read-only property to its default. Although the OLE DB version 1.1 specification clearly indicates that a status of DBPROPSTATUS_OK is returned in "the case where the value of a read-only property was set to its current value," the ODBC Provider returns DBPROPSTATUS_NOTSETTABLE.
Threading
-
The ODBC Provider supports the Free Threaded multithreading model; there is no support for Apartment model or mixed threading.
ICommandWithParameters
-
The ODBC Provider returns zero for precision and scale when these values do not apply. These values are incorrect and should be ~0 (bitwise NOT 0).
-
If the consumer doesn't allocate enough space, a stored procedure that returns a variable length parameter will not detect the truncation that might have occurred. Instead of returning S_OK and setting the status to DBSTATUS_S_TRUNCATED, the ODBC Provider will return DB_S_ERRORSOCCURRED, and set the status to DBSTATUS_S_OK.
IConvertType
-
IConvertType::CanConvert returns E_INVALIDARG when a bad dwConvert flag is passed in. This is incorrect; the ODBC Provider should return DB_E_BADCONVERTFLAG.
IDBSchemaRowset
-
The ODBC Provider may not support restrictions on the CATALOGS and SCHEMATA schema rowsets.
-
IDBSchemaRowset::GetSchema may not support the schema restriction on any schema on any ODBC driver. The ODBC Provider will incorrectly report this as a supported restriction because ODBC drivers cannot export information about their support of restrictions to the ODBC Provider.
-
IDBSchemaRowset::GetRowset features limited support for the following schema rowsets with most data sources:
FOREIGN_KEYS
PRIMARY_KEYS
STATISTICS
Consumers must restrict their calls by table name or the ODBC Provider will return an empty result set. This limitation does not occur on Microsoft SQL Server 4.21.
-
On Microsoft SQL Server data sources, IDBSchemaRowset::GetRowset( DBSCHEMA_PROCEDURE_COLUMNS) is unsupported. Consumers who need rowset information about stored procedures should prepare a query that includes the stored procedure and call IColumnsInfo::GetColumnInfo.
-
A restriction on table name is required when calling IDBSchemaRowset::GetRowset(DBSCHEMA_INDEXES). This restriction must be used because ODBC function SQLStatistics returns information about a single table and the ODBC Provider uses this function to return information from the data source. If the restriction is not applied, the ODBC Provider returns an empty result set.
-
The PROVIDER_TYPES schema rowset is not sorted by the ODBC Provider.
-
SQL Server will return names similar but not matching when restrictions are placed on the PROCEDURE_NAMES column. When GetRowset is called for PROCEDURES and PROCEDURE_PARAMETERS schema rowsets, and a name restriction is specified, then the PROCEDURE NAMES returned in the result set may not exactly match the restriction value, but may have ";N" appended, where N is a number. Also, if the ";N" is used in the PROCEDURE NAME restriction, then the provider will return an empty result set even if a procedure name is appended with that number.
IRowset
-
The ODBC Provider does not support execution of RestartPosition when the command text contains parameters; the underlying ODBC driver must, however, support SQLExtendedFetch.
-
With IRowset::RestartPosition, the ODBC Provider does not return DB_S_COLUMNSCHANGED when it is supposed to. Instead, it returns E_FAIL.
-
If the row on which the rowset is currently positioned is deleted, the consumer must first execute IRowset::RestartPosition, before retrieving additional row handles with IRowset::GetNextRows.
-
If the columns in the underlying data source change and the consumer requests additional rows with IRowset::GetNextRows, the ODBC Provider will fail with the return code E_FAIL.
-
If the columns in the underlying data source change, the consumer calls IRowset::RestartPosition, and the reposition operation causes the command to be re-executed, the ODBC Provider will fail with the return code E_FAIL. The rowset is now invalid and any method other than ReleaseRows or ReleaseAccessor will fail. If this occurs, the consumer must release all accessors, row handles, and the rowset and must execute the command that created the rowset again.
-
When using IRowset::GetData, you can bind to only one long data column based on ISequentialStream; the ODBC Provider can bind to any number of long data columns not based on ISequentialStream.
IRowsetLocate
-
When the consumer fetches a row with IRowsetLocate::GetRowsAt, saves the bookmark value corresponding to the row, releases the row, and attempts to fetch the row again using IRowsetLocate::GetRowsAt with the saved bookmark, the second fetch operation may fail. The ODBC Provider returns DB_E_BADBOOKMARK if the underlying ODBC call to SQLExtendedFetch returns S1111 (invalid bookmark value); otherwise, the ODBC Provider returns E_FAIL.
IRowsetResynch
-
To enable the ODBC Provider to expose the IRowsetResynch interface, at least one of the following conditions must be met:
-
The IRowsetLocate interface is implemented.
-
The transaction isolation level is set to SQL_TXN_REPEATABLE_READ.
-
The transaction isolation level is set to SQL_TXN_SERIALIZABLE.
-
The transaction isolation level is set to SQL_TXN_VERSIONING.
-
DBPROP_CANHOLDROWS is set to VARIANT_FALSE, SQLExtendedFetch and SQLSetPos with REFRESH are available from the underlying ODBC driver, and the cursor is not a forward-only cursor.
IRowsetScroll
IRowsetScroll is supported on ODBC 3.x drivers. If a driver cannot determine the current row position, a value of 1 is returned for the row number in GetApproximatePosition.