c-treeACE SQL Standards Conformance
c-treeACE SQL provides a high performance SQL interface into the proven core of the c-treeACE Server. Tailored for high volume production environments, the c-treeACE SQL Server includes optimizations such as sophisticated query rewrite techniques to improve nested query performance and join-order optimization to improve performance of queries joining many tables. c-treeACE SQL extensively caches and buffers information for maximum transaction and query throughput.
Because c-treeACE SQL is built on the same core technology of the c-treeACE Server, you get all of the performance and features that distinguish c-treeACE with the additional benefits of a functionally-complete SQL interface compatible with SQL-92, ODBC 3.0 and JDBC 2.0 standards.
c-treeACE SQL provides embedded SQL and interactive SQL utilities, JDBC support, and an ODBC driver. c-treeACE SQL also supports stored procedures, triggers and user defined functions utilizing the powerful and extensible Java language. As such, c-treeACE SQL requires the JDK from Sun Microsystems.
FairCom's c-treeACE SQL supports most of the major platforms including Windows, Linux, Mac OS X, HP-UX, Solaris, and AIX. Additional ports to other operating systems are constantly ongoing, so please contact FairCom for the latest status of your operating system of choice.
Highlights
c-treeACE SQL supports entry level SQL-92 with the following additional SQL-92 Intermediate features:
- Dynamic SQL
- GET DIAGNOSTICS support
- CASE expressions
- INTERSECT, MINUS set operators
- ALTER INDEX
- Extensive character-string functions
- SET SCHEMA
- Date/time/interval types
- CAST functionality
- Variable-length character strings
- Nested queries
- Transaction isolation levels
- Recursive views
- Schema manipulation statements
Data Types
c-treeACE SQL supports all SQL-92 data types, including unstructured LONG types
Performance
 c-treeACE SQL Optimizations
Transaction Processing Optimizations
- SMP (symmetric multi-processor) support provides transaction scalability.
- Compiled/cached SQL for repeated queries.
- Metadata and security information caching.
Data Warehousing Optimizations
- Star-join schema optimization.
- Join order/method selection for complex decision support queries.
- Query rewrite techniques improve nested query performance.
- Join hints for influencing query execution plans.
- Index usage for scalar functions.
Security
GRANT and REVOKE support: column, table, and view level
c-treeACE SQL Conformance Levels
Deviations from Conformance Levels
c-treeACE SQL conforms to Entry SQL of SQL 1992 standard with the following deviations:
Section
|
Feature
|
4.16, 4.17
|
Modules/Procedures as specified by SQL 1992 are not supported. Instead, Java Stored procedures are supported.
|
4.26
|
The privilege USAGE is not supported.
|
5.3
|
The syntax for specifying a date/time/timestamp literal with the keywords DATE/TIME /TIMESTAMP is not supported. In addition to accepting the string literal for the same, ODBC syntax for specifying literal of these types is supported.
|
Binding Styles
The binding styles supported are the following:
- Embedded SQL C
- Direct invocation and processing of SQL language.
Supported Features from Higher Conformance Levels
Following the are the SQL 1992 standard features required by the higher conformance levels that are partially/fully supported in c-treeACE SQL:
Section
|
Feature
|
5.2
|
Delimited identifiers, lowercase identifiers
|
5.3
|
NCHAR strings with internationalization option, datetime literals in string/ODBC format
|
5.4
|
Connection name in Esqlc
|
6.1
|
VARCHAR, DATE, TIME, TIMESTAMP data types supported, NCHAR supported with internationalization option
|
6.2
|
Dynamic parameter specification supported.
|
6.3
|
Derived table, joined table and optional keyword AS supported.
|
6.9
|
Both simple case expressions and searched case expressions supported. In addition, both case abbreviations, NULLIF and COALESCE are supported
|
6.10
|
CAST specification supported
|
6.13
|
Concatenation operator || supported in string value expressions.
|
6.14
|
Datetime value expressions supported without time zone specifier.
|
7.3
|
WHERE, GROUP BY and HAVING clauses allowed for a grouped view.
|
7.4
|
Specifying more than one table reference allowed in FROM clause when there is a reference to a grouped view.
|
7.5
|
Left and right outer joins are supported.
|
7.9
|
A query specification is updatable even when it contains a subquery.
|
7.10
|
Set operators INTERSECT and MINUS (alias to EXCEPT) are supported.
|
10.6
|
A constraint name can be specified.
|
11.10
|
ALTER TABLE statement is supported.
|
11.18
|
DROP TABLE statement is supported.
|
11.20
|
DROP VIEW is supported.
|
11.37
|
REVOKE statement is supported.
|
13.1
|
Updatability clause partially supported in DECLARE CURSOR statement.
|
14.1
|
SET TRANSACTION ISOLATION LEVEL ... is supported.
|
15.1
|
CONNECT statement supported.
|
15.3
|
DISCONNECT statement supported.
|
16.2
|
SET SCHEMA statement supported.
|
17
|
Dynamic SQL partially supported in ESQLC.
|
18.1
|
GET DIAGNOSTICS statement supported in ESQLC.
|
Extensions to Standard Features
Following are the feature extensions to c-treeACE SQL most of which have been adopted from other leading DBMS products or from later SQL standards.
Sl No.
|
Feature
|
1.
|
More than 100 scalar functions are supported.
|
2.
|
Identifier case for non-delimited identifiers could be upper/lower/mixed.
|
3.
|
Privileges ALTER and INDEX are supported.
|
4.
|
Default clause could specify SYSDATE, SYSTIME, SYSTIMESTAMP and UID.
|
5.
|
Java Stored procedures are supported.
|
6.
|
User defined scalar functions in Java are supported.
|
7.
|
CREATE/DROP INDEX statements are supported.
|
8.
|
Private and public synonyms are supported.
|
9.
|
Triggers are supported.
|
10.
|
LOCK TABLE statement is supported.
|
11.
|
RENAME statement is supported.
|
12.
|
UPDATE STATISTICS statement supported.
|
13.
|
Multi-fetch and multi-insert features are supported.
|
14.
|
Support for BIGINT, LONG VARCHAR, LONG VARBINARY, LOB and LOC data types.
|
15.
|
CONTAINS operator support for columns with LONG VARCHAR type.
|
16.
|
TOP <n> support in SELECT clause.
|
17.
|
Support for preserved cursors.
|
18.
|
Support for TRUNCATE TABLE statement
|
c-treeACE SQL ODBC
c-treeACE SQL ODBC Driver Details
The c-treeACE SQL ODBC Driver brings the ease of ODBC data access to your c-treeACE SQL applications.
This ODBC driver, compatible with version 3.0 of the ODBC standard, provides wide compatibility with hundreds of query, reporting, Web and development tools. This driver is available for all platforms supported by c-treeACE SQL, including Windows, Linux, Mac OS X, HP-UX, Solaris, and AIX.
The c-treeACE SQL ODBC Driver supports "Extended SQL Grammar", the broadest level of the ODBC standard. This includes nested queries, recursive views, case expressions, outer joins, positioned updates, union, intersect and minus set operations, ODBC-compatible scalar functions, extended data types, and procedure calls. Extended SQL provides high-end decision-support functionality with the power to answer complex business questions.
The c-treeACE SQL ODBC Driver is tailored for high performance production environments, including online transaction processing and decision support applications. Specialized stored procedures and client-side caching give you efficient metadata access. Optimized client/server interactions provide efficient online transaction processing. Extensive client-side buffering reduces network traffic resulting in fast ad-hoc queries. Performance tests show that this driver gives equal or superior performance compared to those of other leading database vendors.
Flexible Configuration Support
This sophisticated driver supports both traditional client/server and three-tier configurations for Internet and intranet environments. This flexibility ensures that all of your present and future needs will be met.
No Additional Deployment Costs
The c-treeACE SQL ODBC Driver is included with the license for c-treeACE SQL. All clients communicating with the server can use this driver.
Driver Compatibility Information
Data Type Support
The c-treeACE SQL ODBC Driver supports all ODBC data types, including LONG types of arbitrary length and structure.
ODBC 3.0 Support
The c-treeACE SQL ODBC driver supports all Core, Level 1, and Level 2 API functions, plus extensive scalar functions.
Scalar functions
String Scalar Functions
ASCII
CHAR
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LTRIM
|
REPEAT
REPLACE
RIGHT
RTRIM
SPACE
SUBSTRING
UCASE
|
Time and Data Functions
CURDATE
CURTIME
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
|
MONTH
MONTHNAME
NOW
QUARTER
SECOND
WEEK
YEAR
|
Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
DEGREES
EXP
FLOOR
|
LOG10
MOD
PI
POWER
RADIANS
RAND
SIGN
SIN
SQRT
TAN
|
c-treeACE SQL JDBC
c-treeACE SQL JDBC Driver Details
The c-treeACE SQL JDBC driver provides an industry standard means of connecting to c-treeACE SQL. Java Database Connectivity (JDBC) has emerged as a preferred method for Web browser access to databases. With more developers using the Java programming language, the need to access databases from Java will continue to grow.
JDBC specifies a single Application Programming Interface for Java programs to access any data source, thereby masking the differences between different data sources and providing a standard interface to any Java application.
100% Pure Java™ JDBC Driver
The c-treeACE SQL JDBC Driver is a 100% Pure Java JDBC driver supporting JDBC 2.0. Unlike other types of JDBC drivers, the c-treeACE SQL JDBC Driver is a "Type 4" driver written completely in Java and does not rely on third-party network middleware. This type of driver requires no software on client browsers and offers superior performance over other types of JDBC Drivers.
Instead, the JDBC driver converts JDBC calls directly into the Java network protocol. This type of driver has the following advantages:
- Because there is no client-resident software, the installation and administration hassles of ODBC drivers are a thing of the past.
- Because there is no third-party network middleware, it provides superior performance to multi-tier JDBC drivers.
No Additional Deployment Costs
The c-treeACE SQL JDBC Driver is included with the license for c-treeACE SQL. If you purchase a production server license, you have the ability for all clients communicating with the server to use this driver.
Support for Multiple Configurations
The c-treeACE SQL JDBC driver supports both two-tier and three-tier configurations. In a two-tier configuration, the JDBC driver runs on the Web browser and communicates with the c-treeACE SQL across the Internet or Intranet. In a three-tier configuration, the JDBC Driver runs on a middle tier (typically an application server) and communicates with c-treeACE SQL running on the third tier.
Because the c-treeACE SQL JDBC driver is a pure-Java JDBC driver, it can support either configuration with no modifications.
Driver Compatibility Information
JDBC Supported Functions
The c-treeACE SQL JDBC driver supports an extensive set of scalar functions, including the following:
Supported Numeric Functions:
SQL_FN_NUM_ABS
SQL_FN_NUM_ACOS
SQL_FN_NUM_ASIN
SQL_FN_NUM_ATAN
SQL_FN_NUM_ATAN2
SQL_FN_NUM_CEILING
SQL_FN_NUM_COS
SQL_FN_NUM_DEGREES
SQL_FN_NUM_EXP
SQL_FN_NUM_COS
SQL_FN_NUM_DEGREES
SQL_FN_NUM_EXP
|
SQL_FN_NUM_FLOOR
SQL_FN_NUM_LOG10
SQL_FN_NUM_MOD
SQL_FN_NUM_PI
SQL_FN_NUM_POWER
SQL_FN_NUM_RADIANS
SQL_FN_NUM_RAND
SQL_FN_NUM_SIGN
SQL_FN_NUM_SIN
SQL_FN_NUM_SQRT
SQL_FN_NUM_TAN
|
Supported String Functions:
SQL_FN_STR_ASCII
SQL_FN_STR_LTRIM
SQL_FN_STR_RTRIM
|
SQL_FN_STR_CONCAT
SQL_FN_STR_LENGTH
SQL_FN_STR_LOCATE
|
Supported System Functions
SQL_FN_SYS_USERNAME
SQL_FN_SYS_IFNULL
|
SQL_FN_SYS_DBNAME
|
Supported Data Types
The c-treeACE SQL JDBC Driver supports all JDBC data types, including LONG types of arbitrary length and structure:
JDBC type
|
Java type
|
CHAR
VARCHAR
LONGVARCHAR
NUMERIC
DECIMAL
BIT
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
BINARY
VARBINARY
LONGVARBINARY
DATE
TIME
TIMESTAMP
|
String
String
String
java.math.BigDecimal
java.math.BigDecimal
Boolean
byte
short
int
long
float
double
double
byte[ ]
byte[ ]
byte[ ]
java.sql.Date
java.sql.Time
java.sql.Timestamp
|
Utilities
c-treeACE SQL Utilities
The following utilities are used to provide interactive and embedded SQL support to c-treeACE SQL applications.
Interactive SQL
Our interactive SQL utility (iSQL) supports application development and administration for your c-treeACE SQL database. This tool provides the flexibility to create scripts for administration and testing, or for generating formatted reports.
The following example shows dynamic SQL statements being used to create, populate, and query a table.
C:\FairCom\v8.14\ctreeSDK\ctreeISQL> isql -u ADMIN -a ADMIN myDatabase
FairCom/isql Version v8.14.872(Build-0401010)
FairCom Corporation (C) 1992-2004.
ISQL> CREATE TABLE TIGERS (TEST CHAR(30));
ISQL> INSERT INTO TIGERS VALUES('FairCom');
1 record inserted.
ISQL> SELECT * FROM TIGERS;
TEST
----
FairCom
1 record selected
ISQL>
Embedded SQL
The embedded SQL precompiler lets you process C language program source files with embedded SQL statements. It converts the SQL statements to C language calls and invokes the C compiler to generate object or executable files.
You can insert the SQL statements among C constructs, as shown in the program excerpt below.
EXEC SQL
SELECT 1
INTO :result
FROM systpe.syscalctable
WHERE :ship_date > SYSDATE
AND :ship_date < ADD_MONTHS (:order_date, 1) ;
if (sqlca.sqlcode == 0)
printf ("ship_date valid ") ;
else
if (sqlca.sqlcode == SQL_NOT_FOUND)
printf ("ship_date invalid ") ;
else
printf ("Error ") ;
|
