|
| ||||||||||||||||||
 |  |  |  |
This section documents the ODBC and SQL conformance in the Easysoft SQI-Sage Tetra CS/3 Driver and supported Sage Tetra CS/3 data types and their SQL equivalents.
The Easysoft SQI-Sage Tetra CS/3 Driver provides the following API Entry points:
The following functions are provided by the ODBC Driver Manager:
| SQLDataSources | Core |
| SQLDrivers | Core |
| SQLAllocConnect | Core |
| SQLAllocEnv | Core |
| SQLError | Core |
| SQLGetConnectOption | Core |
| SQLSetParam | Core |
The following functions are provided by the Setup DLL:
| SQLConfigDriver | Core |
| SQLConfigDSN | Core |
The following functions are currently not supported:
| SQLBulkOperations | Level 1 |
The Easysoft SQI-Sage Tetra CS/3 Driver supports the following statements:
The Easysoft SQI-Sage Tetra CS/3 Driver supports UNION and UNION ALL.
The Table reference list in a select can contain all or any of:
The Easysoft SQI-Sage Tetra CS/3 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, sub query or another join. Joins can be nested with no restriction on depth.
Joins can be specified in both SQL92 and ODBC format:
SELECT * from x LEFT OUTER JOIN y ON x.a = y.a
SELECT * from {oj x LEFT OUTER y ON x.a = y.a }
The Easysoft SQI-Sage Tetra CS/3 Driver supports the following predicates:
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 ))
The sub queries in predicates can be correlated or non correlated:
SELECT a FROM b WHERE c = ALL ( SELECT x FROM y
SELECT a FROM b WHERE c = ALL ( SELECT x FROM y
The Easysoft SQI-Sage Tetra CS/3 Driver provides all the functions required by ODBC and also functions from SQL92. Functions can be specified in SQL92 or ODBC format:
SELECT CURRENT_DATE, EXTRACT( YEAR
FROM Employee.data_of_birth ) FROM Employee
SELECT {fn CURRENT_DATE()}, {fn EXTRACT( YEAR
FROM Employee.data_of_birth )} FROM Employee
The following is a complete set of supported functions:
The Easysoft SQI-Sage Tetra CS/3 Driver supports the following aggregate functions:
The Easysoft SQI-Sage Tetra CS/3 Driver supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.
The Easysoft SQI-Sage Tetra CS/3 Driver supports CASE statements and the shorthand forms NULLIF and COALESCE.
All SQL92 and ODBC32 literals are supported and can be specified in either form:
DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH
{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}
The Easysoft SQI-Sage Tetra CS/3 Driver performs several optimizations to improve performance including the following:
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
SELECT * FROM x WHERE b = 20 and c = 30 and a <> 10
In cases where indexes are present on tables the Easysoft SQI-Sage Tetra CS/3 Driver will if necessary rearrange the order that tables are processed to enable the index to be used. This 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.
WHERE a.catalog_number = c.catalog_number
AND b.catalog_number = a.catalog_number
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. If table optimization is enabled then, on the same machine, the query takes under 2 seconds. 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), with optimization the same query will return in under 10 seconds.
The Easysoft SQI-Sage Tetra CS/3 Driver provides an informational schema view of the tables supplied by the target data sources. The following tables are available
The following data types are supported:
The Easysoft SQI-Sage Tetra CS/3 Driver provides FORWARD ONLY, STATIC and KEYSET CURSORS and also provides the following additional ODBC features (reported via the SQLGetIinfo API call).
Asynchronous operation is supported both at the statement and connection level (SQL_AM_STATEMENT)
The data source supports column alias using the optional AS clause
Correlation names are supported and can be any valid user-defined-name
All SQL92 Datetime literals are supported
SQLGetData can be called for any column, bound or unbound
The columns in the GROUP BY clause and the select list are not related (SQL_GB_NO_RELATION)
This section lists the Sage Tetra CS/3 data types and the SQL data types to which data is converted:
| Copyright Notice |
| | | |
