Easysoft ODBC-LINC Developer Driver ODBC and SQL Conformance
API Support:
- Easysoft ODBC-LINC Developer Driver API entry points
- Functions provided by the ODBC Driver Manager
- Functions provided by the Easysoft ODBC-LINC Developer Driver Setup library
- Functions currently not supported, but planned for future release
SQL:
Easysoft ODBC-LINC Developer Driver Supported Features:
- Statement Types
- Unions
- Table Reference
- Joins
- Predicates
- Scalar Functions
- Set Functions
- Conversion Functions
- Conditional Functions
- Literals
- Optimizations
- Informational Schema
- Data Types
- ODBC Features
API Support
The Easysoft ODBC-LINC Developer Driver provides the following API entry points:
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:
- 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 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:
- Table name
- Sub Query
- Join
Joins
The Easysoft ODBC-LINC Developer Driver 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 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:
- 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 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
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURRENT_USER
- USER
SQL92 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 Easysoft ODBC-LINC Developer Driver supports the following Set Functions:
- COUNT( * | ALL | DISTINCT )
- AVG( ALL | DISTINCT )
- MIN( ALL | DISTINCT )
- MAX( ALL | DISTINCT )
- SUM( ALL | DISTINCT )
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:
- 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 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