Easysoft ODBC-ODBC Join Engine ODBC and SQL Conformance

Contents

ODBC API support

ODBC-ODBC Join Engine API entry points

The ODBC-ODBC Join Engine 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

ODBC Driver Manager functions

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

ODBC-ODBC Join Engine Setup library functions

The following functions are provided by the ODBC-ODBC Join Engine Setup library:

Function ODBC conformance
SQLConfigDriver Core
SQLConfigDSN Core

Unsupported functions

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 statements

The ODBC-ODBC Join Engine supports the following statements:

  • ALTER TABLE
  • COMMIT
  • CREATE INDEX
  • CREATE TABLE
  • CREATE VIEW
  • DELETE STATEMENT (positioned)
  • DELETE STATEMENT (searched)
  • DROP INDEX
  • DROP TABLE
  • DROP VIEW
  • GRANT
  • INSERT
  • REVOKE
  • ROLLBACK
  • SELECT
  • SELECT FOR UPDATE
  • UPDATE (positioned)
  • UPDATE (searched)

Unions

The ODBC-ODBC Join Engine supports UNION and UNION ALL.

Table reference

The table reference list in a SELECT can contain all or any of:

  • Table name
  • Subquery
  • Join

Joins

The ODBC-ODBC Join Engine supports the following types of joins:

  • INNER
  • LEFT OUTER
  • RIGHT OUTER
  • FULL OUTER
  • NATURAL

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 ODBC-ODBC Join Engine supports the following predicates:

  • Comparison (a = b)
  • BETWEEN (a BETWEEN b AND c)
  • LIKE (a LIKE '%green%')
  • NULL (a IS NOT NULL)
  • IN value_list (a IN (1, 2, 3))
  • IN sub_query (a IN (SELECT x FROM y)
  • Quantified comparison (a = ALL (SELECT x FROM y))
  • EXISTS (EXISTS(SELECT x from y))

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 ODBC-ODBC Join Engine provides ODBC and SQL-92 functions. 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

  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • CURRENT_USER
  • USER

SQL-92 functions

  • BIT_LENGTH
  • CHAR_LENGTH
  • CHARACTER_LENGTH
  • OCTET_LENGTH
  • POSITION
  • SUBSTRING
  • TRIM

ODBC functions

  • ASCII
  • CHAR
  • CONCAT
  • DIFFERENCE
  • INSERT
  • LCASE
  • LEFT
  • LENGTH
  • LOCATE
  • LTRIM
  • REPEAT
  • REPLACE
  • RIGHT
  • RTRIM
  • SOUNDEX
  • SPACE
  • UCASE
  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • CEILING
  • COS
  • COT
  • DEGREES
  • EXP
  • FLOOR
  • LOG
  • LOG10
  • MOD
  • PI
  • POWER
  • RADIANS
  • RAND
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • TAN
  • TRUNCATE
  • CURDATE
  • CURTIME
  • DAYNAME
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • EXTRACT
  • HOUR
  • MINUTE
  • MONTH
  • MONTHNAME
  • NOW
  • QUARTER
  • SECOND
  • TIMESTAMPADD
  • TIMESTAMPDIFF
  • WEEK
  • YEAR
  • DATABASE
  • IFNULL

SET functions

The ODBC-ODBC Join Engine supports the following SET functions:

  • COUNT(* | ALL | DISTINCT)
  • AVG(ALL | DISTINCT)
  • MIN(ALL | DISTINCT)
  • MAX(ALL | DISTINCT)
  • SUM(ALL | DISTINCT)

Conversion functions

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

Conditional functions

The ODBC-ODBC Join Engine 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 '2024-01-02', INTERVAL '10-2' YEAR TO MONTH

ODBC

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

ODBC-ODBC Join Engine features

Optimisations

The ODBC-ODBC Join Engine performs several optimisations to improve performance including the following:

Query optimisation

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

For example, the query:

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

becomes:

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 ODBC-ODBC Join Engine 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 ODBC-ODBC Join Engine provides an informational schema view of the tables supplied by the target data sources.

The following tables are available:

  • INFO_SCHEMA.CHARACTER_SETS
  • INFO_SCHEMA.COLLATIONS
  • INFO_SCHEMA.COLUMN_PRIVILEGES
  • INFO_SCHEMA.COLUMNS
  • INFO_SCHEMA.INDEXES
  • INFO_SCHEMA.SCHEMATA
  • INFO_SCHEMA.SERVER_INFO
  • INFO_SCHEMA.SQL_LANGUAGES
  • INFO_SCHEMA.TABLE_PRIVILEGES
  • INFO_SCHEMA.TABLES
  • INFO_SCHEMA.USAGE_PRIVILEGES
  • INFO_SCHEMA.VIEWS

Data types

The following data types are supported:

  • SQL_CHAR
  • SQL_VARCHAR
  • SQL_LONGVARCHAR
  • SQL_NUMERIC
  • SQL_DECIMAL
  • SQL_SMALLINT
  • SQL_INTEGER
  • SQL_REAL
  • SQL_FLOAT
  • SQL_DOUBLE
  • SQL_BIT
  • SQL_TINYINT
  • SQL_BIGINT
  • SQL_BINARY
  • SQL_VARBINARY
  • SQL_LONGVARBINARY
  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • SQL_INTERVALS (all types)

ODBC features

The ODBC-ODBC Join Engine provides FORWARD ONLY, STATIC, and KEYSET CURSORS and also provides the following additional ODBC features (reported through 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.