Developer Reference > Data Access Methods > ODBC Guide > ODBC Specification Support
ODBC Specification Support
The following topics overview Zen support for the Open Database Connectivity Specification (ODBC) specification:
For instructions on setting up ODBC configuration options in Zen, see DSN Setup and Connection Strings.
ODBC Specification Supported
ODBC (Open Database Connectivity) is a standard API originally developed by Microsoft for accessing database management systems (DBMS). The standard has evolved over the years. The Zen relational interface supports the ODBC v3.51 specifications for Core, Level 1, and Level 2 interface support levels (Level 3 is not supported).
Exceptions to ODBC Interface Support
Core Level
SQL_BEST_ROWID
The relational interface uses unique indexes as the optimal set of columns that identifies a row in the table.
When a new row is inserted into a table that includes an IDENTITY column, the relational interface does not return the value assigned to the IDENTITY column. You may determine the value for an IDENTITY column through the use of the @@IDENTITY variable. See @@IDENTITY and @@BIGIDENTITY in SQL Engine Reference.
Level 2
The following are not supported:
• SQL_ATTR_LOGIN_TIMEOUT
• SQL_BEST_ROWID (see discussion above)
• SQL_ROWVER
ODBC API Support
The following table lists the ODBC API functions supported by the relational interface and the ODBC support level. For detailed information on the ODBC API, see the Microsoft ODBC documentation.
ODBC Function
ODBC Support Level
SQLAllocHandle
Core
SQLBindCol
Core
SQLBindParameter
Core
SQLBrowseConnect
Level 1
SQLBulkOperations
Level 1
SQLCancel
Core
SQLCloseCursor
Core
SQLColAttribute
Core
SQLColumnPrivileges
Level 2
SQLColumns
Core
SQLConnect
Core
SQLCopyDesc
Core
SQLDataSources
Core
SQLDescribeCol
Core
SQLDescribeParam
Level 2
SQLDisconnect
Core
SQLDriverConnect
Core
SQLDrivers
Core
SQLEndTran
Core
SQLExecDirect
Core
SQLExecute
Core
SQLExtendedFetch
Core
SQLFetch
Core
SQLFetchScroll
Core
SQLForeignKeys
Level 2
SQLFreeHandle
Core
SQLFreeStmt
Core
SQLGetConnectAttr
Core
SQLGetCursorName
Core
SQLGetData
Core
SQLGetDescField
Core
SQLGetDescRec
Core
SQLGetDiagField
Core
SQLGetDiagRec
Core
SQLGetEnvAttr
Core
SQLGetFunctions
Core
SQLGetInfo
Core
SQLGetStmtAttr
Core
SQLGetTypeInfo
Core
SQLMoreResults
Level 1
SQLNativeSql
Core
SQLNumParams
Core
SQLNumResultCols
Core
SQLParamData
Core
SQLPrepare
Core
SQLPrimaryKeys
Level 1
SQLProcedureColumns
Level 1
SQLProcedures
Level 1
SQLPutData
Core
SQLRowCount
Core
SQLSetConnectAttr
Core
SQLSetCursorName
Core
SQLSetDescField
Core
SQLSetDescRec
Core
SQLSetEnvAttr
Core
SQLSetPos
Level 1
SQLSetStmtAttr
Core
SQLStatistics
Core
SQLTablePrivileges
Level 2
SQLTables
Core
Exceptions to ODBC API Support
The following section contains details on the exceptions to ODBC API support.
SQLGetData
If your application calls SQLGetData to return data into an SQL_C_NUMERIC structure, the ODBC standard specifies that the SQL_DESC_SCALE field is set to zero and the SQL_DESC_PRECISION field uses the driver-defined precision.
Zen use the values for scale and driver-defined precision as defined in the metadata. Consider the following example, where scale is set to two:
CREATE TABLE testnum (col1 NUMERIC(10,2))
INSERT INTO testnum VALUES (10.34)
SELECT * FROM testnum
The SELECT statement returns 10.34, not 10.00.
SQLGetTypeInfo
SQLGetTypeInfo generates a list of native data type names (type_name) specified by the relational interface. For example, SQL_CHAR is mapped to CHARACTER. Use the names which are returned from this function for the data type names for columns in a CREATE TABLE or ALTER TABLE statement or for parameters for procedures or declared variables in procedures and triggers.
See Supported Data Types for a list of supported ODBC data types.
SQLGetInfo
The relational interface returns identical values for SQL_DRIVER_VER and SQL_DBMS_VER. This version value is returned in the following format:
aa.bb.cccc ddd
This value can be interpreted as four components as explained in the following table:
Part
Value
Description
aa
Major version
The major version of the database engine
bb
Minor version
The minor version of the database engine, which is typically updated in a service pack
cccc
Build number
The build further specifies the release
ddd
Point build
A minor update to the build
The following table summarizes formats of other values typically returned by SQLGetInfo.
Item
Example Value
SQL_DRIVER_NAME
W3ODBCCI.DLL
SQL_DRIVER_VER
10.00.0147 012
SQL_DRIVER_ODBC_VER
03.51
SQL_DBMS_NAME
Zen
SQL_DBMS_VER
10.00.0147 012
SQL_ODBC_VER
03.52.0000
SQL_ODBC_API_CONFORMANCE
SQL_OAC_LEVEL2
SQL_ODBC_INTERFACE_CONFORMANCE
SQL_OIC_LEVEL2
SQLSpecialColumns
The Zen relational interface uses unique indexes as the optimal set of columns that uniquely identifies a row in the table. When a new row is inserted, the relational interface does not return the values for IDENTITY columns. You may determine the value for an IDENTITY column through the use of the @@IDENTITY variable. See @@IDENTITY and @@BIGIDENTITY in SQL Engine Reference.
ODBC Attribute Support
The relational interface provides ODBC v3.51 attribute support, with the exceptions listed here.
Connection Attribute Support
The following table lists the exceptions to ODBC connection attribute support:
fOption
Comments
SQL_ATTR_AUTO_IPD
The default value is SQL_TRUE. The Pervasive ODBC Driver does not allow setting this attribute value to SQL_FALSE.
SQL_ATTR_CONNECTION_TIMEOUT
The default value is 0. No other value is supported.
SQL_ATTR_METADATA_ID
The default value is SQL_FALSE. The Pervasive ODBC Driver does not allow setting this attribute's value to SQL_TRUE
Statement Attribute Support
The following table lists the exceptions to ODBC statement attribute support:
fOption (numerical value)
Comments
SQL_ATTR_ENABLE_AUTO_IPD (15)
The default value is SQL_TRUE. Pervasive ODBC Driver does not allow setting this attribute value to SQL_FALSE.
SQL_ATTR_METADATA_ID (10014)
The default value is SQL_FALSE. Pervasive ODBC Driver does not allow setting this attribute's value to SQL_TRUE.
SQL_ATTR_PARAM_BIND_TYPE (18)
Only SQL_PARAM_BIND_BY_COLUMN is supported.
SQL_ATTR_QUERY_TIMEOUT (0)
Supported through SQLSetStmtAttr and SQLSetConnectAttr. Applies only to SQLExecDirect, SQLExecute, SQLFetch, and SQLExtendedFetch. Does not apply to DDL statements.
ODBC Descriptor Field Support
The Zen relational interface provides ODBC v3.51 descriptor field support except for the options listed in the following table.
Option
Comments
SQL_DESC_BIND_TYPE
For application parameter descriptors (APDs), only SQL_BIND_BY_COLUMN is supported.
SQL_DESC_ROWVER
See Exceptions to ODBC Interface Support.
Descriptor Fields and Bitness Values
Note that some of the descriptor fields that can be set through the various ODBC SQLSet and SQLGet functions have been changed to accommodate 64-bit values while others are still 32-bit values. If you are using the 64-bit ODBC driver, ensure that you use the appropriate sized variable when setting and retrieving these fields. For more information, refer to the Microsoft ODBC documentation.
A point of clarification is that SQL_ROWSET_SIZE is supported by both SQLGetStmtOption and SQLGetStmtAttr. If you are using the 64-bit ODBC driver and you call either SQLGetStmtOption or SQLGetStmtAttr, a 64-bit value is returned in *ValuePtr when that attribute parameter is set to SQL_ROWSET_SIZE.
SQLSetStmtOption Options
The section discusses the Zen support for the following SQLSetStmtOption options:
• SQL_BIND_TYPE
• SQL_CONCURRENCY
• SQL_CURSOR_TYPE
• SQL_RETRIEVE_DATA
• SQL_ROWSET_SIZE
• SQL_USE_BOOKMARKS
The following tables indicate valid set values for each option.
Option
ODBC Cursor Library
Current Zen ODBC Drivers
SQL_BIND_TYPE
SQL_BIND_BY_COLUMN or a length to indicate row-wise binding
SQL_BIND_BY_COLUMN or a length to indicate row-wise binding
SQL_CONCURRENCY
SQL_CONCUR_READ_ONLY or SQL_CONCUR_ VALUES
(for SQL_CONCUR_ROWVER the library substitutes SQL_CONCUR_VALUES, returns SQL_SUCCESS_WITH_INFO and sets SQLSTATE to 01S02)
(for SQL_CONCUR_LOCK the library returns SQL_ERROR returned with SQLSTATE of S1C00)
SQL_CONCUR_READ_ONLY or SQL_CONCUR_ROWVER or SQL_CONCUR_LOCK
(for SQL_CONCUR_VALUES the driver automatically substitutes SQL_CONCUR_ROWVER)
SQL_CURSOR_TYPE
SQL_CURSOR_FORWARD_ONLY or SQL_CURSOR_STATIC
(for SQL_CURSOR_KEYSET_DRIVEN and SQL_CURSOR_DYNAMIC the library substitutes SQL_CURSOR_STATIC, returns SQL_SUCCESS_WITH_INFO and sets SQLSTATE to 01S02)
SQL_CURSOR_FORWARD_ONLY or SQL_CURSOR_STATIC or SQL_CURSOR_DYNAMIC
(for SQL_CURSOR_KEYSET_DRIVEN the driver substitutes SQL_CURSOR_STATIC, returns SQL_SUCCESS_WITH_INFO and sets SQLSTATE to 01S02)
SQL_RETRIEVE_DATA
SQL_RD_ON
(for SQL_RD_OFF the library returns SQL_ERROR returned with SQLSTATE of S1C00)
SQL_RD_ON or SQL_RD_OFF
SQL_ROWSET_SIZE
Any value indicating number of rows in the rowset as long as it does not exceed maximum rowset size.
Any value indicating number of rows in the rowset as long as it does not exceed maximum rowset size.
SQL_USE_BOOKMARKS
SQL_UB_ON or SQL_UB_OFF
SQL_UB_ON or SQL_UB_OFF
 
Last modified date: 11/04/2024