ODBC Specification Support
An Overview of PSQL Support for the Open Database Connectivity Specification
This chapter discusses PSQL support for the ODBC specification:
For instructions on setting up ODBC configuration options in PSQL, go directly to
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 PSQL 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, refer to the Microsoft ODBC documentation.
Table 1 PSQL Support for ODBC API Functions
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.
PSQL use the values for scale and driver-defined precision as defined in the metadata. Consider the following example, in which 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 PSQL database engine. |
bb | Minor version | The minor version of the PSQL database engine, which is typically updated in a service pack. |
cccc | Build number | The build further specifies the release by marking the time at which Actian Corporation generated the release. |
ddd | Point build | A minor update to the build. The build number and point build uniquely identify a product release created at a specific time. |
The following table summarizes the expected format of other values typically returned by SQLGetInfo. The values are examples only.
Item | Example Value |
SQL_DRIVER_NAME | W3ODBCCI.DLL |
SQL_DRIVER_VER | 10.00.0147 012 |
SQL_DRIVER_ODBC_VER | 03.51 |
SQL_DBMS_NAME | Pervasive.SQL |
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 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:
Table 2 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:
Table 3 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 Relational Interface provides ODBC v3.51 descriptor field support except for the options listed in the following table.
Table 4 Exceptions to ODBC Descriptor Support
fOption | Comments |
SQL_DESC_BIND_TYPE | For application parameter descriptors (APDs), only SQL_BIND_BY_COLUMN is supported. |
SQL_DESC_ROWVER | |
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 PSQL 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.
Table 5 Binding, Concurrency, and Cursor Types Support by Access Method
| ODBC Cursor Library | Current PSQL 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) |
Table 6 Rowset Size and Bookmarks Support by Access Method
| ODBC Cursor Library | Current PSQL ODBC Drivers |
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 |