Easysoft ODBC-LINC Developer Driver ODBC and SQL Conformance

API Support:

SQL:

Easysoft ODBC-LINC Developer Driver Supported Features:

API Support

The Easysoft ODBC-LINC Developer 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 Easysoft ODBC-LINC Developer 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 Easysoft ODBC-LINC Developer Driver supports the ODBC minimum SQL grammar with the majority of SQL92 extensions. These additional features are not dependent on support from the target drivers.

Easysoft ODBC-LINC Developer Driver Supported Features:

Statement Types

The Easysoft ODBC-LINC Developer Driver supports the following statements:

Unions

The Easysoft ODBC-LINC Developer Driver supports UNION and UNION ALL.

Table Reference

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

Joins

The Easysoft ODBC-LINC Developer 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 SQL92 and ODBC format:

SQL92

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 Easysoft ODBC-LINC Developer 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 Easysoft ODBC-LINC Developer Driver provides all the functions required by ODBC and also functions from SQL92. Functions can be specified in either ODBC or SQL92 format:

SQL92

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

SQL92 Functions

ODBC Functions

Set Functions

The Easysoft ODBC-LINC Developer Driver supports the following Set Functions:

Conversion Functions

The Easysoft ODBC-LINC Developer Driver supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.

Conditional Functions

The Easysoft ODBC-LINC Developer Driver supports CASE statements and the shorthand forms NULLIF and COALESCE.

Literals

All SQL92 and ODBC32 literals are supported and can be specified in either form:

SQL92

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

ODBC

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

Optimizations

The Easysoft ODBC-LINC Developer Driver performs several optimizations to improve performance including the following:

Query optimization

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 Optimization

In cases where indexes are present on tables the Easysoft ODBC-LINC Developer 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 optimization is enabled then the query takes under 2 seconds (on the same machine).

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

Informational Schema

The Easysoft ODBC-LINC Developer 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 Easysoft ODBC-LINC Developer Driver provides FORWARD ONLY, STATIC and KEYSET CURSORS and also provides the following additional ODBC features (reported via the SQLGetIinfo 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 SQL92 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