ISAM ODBC driver ODBC and SQL conformance

API support:

SQL:

ISAM ODBC driver supported features:

API support

The ISAM ODBC driver provides the following API entry points:

Function ODBC conformance
SQLAllocHandle Core
SQLBindCol Core
SQLBindParameter Core
SQLBrowseConnect Level 1
SQLCancel core
SQLCloseCursor Core
SQLColAttribute Core
SQLColumnPrivileges Level 2
SQLColumns Core
SQLConnect Core
SQLCopyDesc Core
SQLDescribeCol Core
SQLDescribeParam Level 2
SQLDisconnect Core
SQLDriverConnect Core
SQLEndTran Core
SQLExecDirect Core
SQLExecute 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
SQLPutData Core
SQLRowCount Core
SQLSetConnectAttr Core
SQLSetCursorName Core
SQLSetDescField Core
SQLSetDescRec Core
SQLSetEnvAttr Core
SQLSetPos Level 1
SQLSetStmtAttr Core
SQLSpecialColumns Core
SQLStatistics Core
SQLTablePrivileges Level 2
SQLTables Core

The following functions are provided by the ODBC Driver Manager:

Function ODBC Conformance
SQLDataSources Core
SQLDrivers Core
SQLAllocConnect Core
SQLAllocEnv Core
SQLError Core
SQLGetConnectOption Core
SQLSetParam Core

The following functions are provided by the ISAM ODBC driver Setup library:

Function ODBC conformance
SQLConfigDriver Core
SQLConfigDSN Core

The following functions are currently not supported, but are planned for future release:

Function ODBC conformance
SQLBulkOperations Level 1
SQLProcedureColumns Level 1
SQLProcedures Level 1

SQL

Supported features

The ISAM ODBC driver supports the ODBC minimum SQL grammar with the majority of SQL-92 extensions. These additional features are not dependent on support from the target drivers.

ISAM ODBC driver supported features:

Statement types

The ISAM ODBC driver supports the following statements:

Unions

The ISAM ODBC driver supports UNION and UNION ALL.

Table reference

The Table reference list in a select can contain all or any of:

Joins

The ISAM ODBC driver supports the following types of joins:

The joining condition may be specified with the ON or USING clause. Both the left and right source can be a table name, subquery, or another join. Joins can be nested with no restriction on depth.

Joins can be specified in both SQL-92 and ODBC format:

SQL-92

SELECT * from x LEFT OUTER JOIN y ON x.a = y.a

ODBC

SELECT * from {oj x LEFT OUTER y ON x.a = y.a }

Predicates

The ISAM ODBC driver supports the following predicates:

Subqueries in predicates can be correlated or non-correlated:

Correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = a )

Non-correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = 12 )

Scalar functions

The ISAM ODBC driver provides all the functions required by ODBC and also functions from SQL-92. Functions can be specified in either ODBC or SQL-92 format:

SQL-92

SELECT
  CURRENT_DATE,
  EXTRACT( YEAR FROM Employee.data_of_birth )
FROM
  Employee

ODBC

SELECT
  {fn CURRENT_DATE()},
  {fn EXTRACT( YEAR FROM Employee.data_of_birth )}
FROM
  Employee

The following is a complete set of supported functions:

Pseudo-variable functions

SQL-92 functions

ODBC functions

Set functions

The ISAM ODBC driver supports the following functions:

Conversion functions

The ISAM ODBC driver supports both the SQL-92 CAST function and the ODBC CONVERT function for conversion between compatible data types.

Conditional functions

The ISAM ODBC driver supports CASE statements and the shorthand forms NULLIF and COALESCE.

Literals

All SQL-92 and ODBC literals are supported and can be specified in either form:

SQL-92

DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH

ODBC

{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}

Optimisations

The ISAM ODBC driver does several optimisations to improve performance including the following:

Query optimisation

The WHERE clause of a query will be rewritten into a form that allows more efficient processing of data.

For example, the query:

SELECT * FROM x WHERE ( a = 10 or b = 20 ) and c = 30

will be changed into the equivalent:

SELECT * FROM x WHERE a = 10 and c = 30
UNION
SELECT * FROM x WHERE b = 20 and c = 30

Table optimisation

In cases where indexes are present on tables, the ISAM ODBC driver will (if necessary) rearrange the order that tables are processed to enable the index to be used, which can lead to huge increases in performance.

For example, consider the following query, where tables a, b, and c each have 800 rows and an index on catalog_number:

SELECT * FROM a, b, c
WHERE a.catalog_number = c.catalog_number
AND b.catalog_number = a.catalog_number
AND a."desc" = b."desc"
AND c.retail = a.retail
AND a.catalog_number = b.catalog_number

When run with the table order specified, the query takes about 350 seconds on a given test configuration, but if table optimisation is enabled then the query takes under 2 seconds (on the same machine).

The effect of this optimisation is most noticeable on some of the queries that comprise the TPC-D benchmark set. Without this optimisation some of the queries can be considered to never end (still running after a day), but with optimisation the same query will return in under 10 seconds.

Informational schema

The ISAM ODBC driver provides an informational schema view of the tables supplied by the target data sources.

The following tables are available:

Data types

The following data types are supported:

ODBC features

The ISAM ODBC driver provides FORWARD ONLY, STATIC, and KEYSET CURSORS and also provides the following additional ODBC features (reported by using the SQLGetInfo API call):

SQL_ASYNC_MODE

Asynchronous operation is supported both at the statement and connection level (SQL_AM_STATEMENT).

SQL_COLUMN_ALIAS

The data source supports column alias using the optional AS clause.

SQL_CORRELATION_NAME

Correlation names are supported and can be any valid user-defined-name.

SQL_DATETIME_LITERALS

All SQL-92 datetime literals are supported.

SQL_GETDATA_EXTENSIONS

SQLGetData can be called for any column bound or unbound.

SQL_GROUPBY

The columns in the GROUP BY clause and the select list are not related (SQL_GB_NO_RELATION).

SQL_INDEX_KEYWORDS

All keywords are supported.