|
|
 |
 |
 |
|
| |
c-tree Plus® and c-tree® ODBC Drivers
FairCom offers two ODBC drivers to provide access
to non-SQL applications:
- c-tree Plus ODBC Driver - for use with c-tree Plus standalone
operational models and standard (non-SQL) c-tree Servers
- c-tree ODBC Driver - for use with older c-tree V4 data
files created with c-tree V4.1f through c-tree V4.3c
These single-tier drivers interface directly
to the c-tree Plus and c-tree application programming interface
(API). The term single-tier indicates all of the program logic
necessary to handle requests from a front-end application
is contained within the driver itself, including an SQL interpreter.
All of the features discussed below apply to
both the c-tree Plus ODBC Driver and the c-tree ODBC Driver
unless otherwise noted.
Information on the c-treeSQL
ODBC Driver can be found here.
|
|
| |
ODBC
Driver General Info | Requirements | Driver Constraints | Supported Data Types |
Grammar | ODBC Functions |
| |
c-tree Plus ODBC Driver General Information
ODBC Conversion
The ODBC Driver handles the application
conversion process for the ODBC compliant applications to
access c-tree Plus files. These conversion processes are:
- Connecting the front end application
to a c-tree Plus database (data and index file(s)).
- Defining data - includes retrieving
data file specific information (such as file mode), creating
and deleting data and index files.
- Manipulating data - includes adding,
deleting, retrieving and updating database files.
- Disconnecting from a c-tree Plus database.
- Performing general utility functions
- includes retrieving extended error messages, comparing
data for equality, etc.
- Processing transactions - includes
logging, commit, and rollback.
- Process optimization - includes processing
joins and filters.
ODBC Compliancy
The ODBC standard has three levels of compliance
(core, level 1 and level 2). The c-tree Plus ODBC Driver fully
supports core and level 1, with some level 2 support.
The level of functionality supported by FairCom is consistent
with other ODBC drivers in the marketplace and meets or exceeds
the needs of most popular ODBC compliant applications.
SQL Conformance
The c-tree Plus ODBC Driver fully supports
the minimum SQL grammar and a portion of the core and extended
SQL grammar requirements of the ODBC standard.
Driver Licensing
The c-tree Plus ODBC Driver supports Standalone
(non-server) and Standard (non-SQL) c-tree Server configurations.
Both 16 and 32 bit drivers are available. The c-tree Plus
and c-tree ODBC Drivers are licensed on a per node (client)
basis with quantity discounts available. |
|
|
|
| |
Requirements
The c-tree Plus ODBC Drivers are very flexible
and have minimal requirements, as follows:
Operating System: All versions of Windows
are supported, including Windows 95/98/2000/NT and XP. Further,
FairCom offers legacy 16 bit drivers for Windows V3.1 on an
as needed basis -- please call for details.
Hardware: The Drivers will operate
on any Windows compatible PC with at least 4 MB of available
memory and 3MB of hard drive space (2MB for 16 bit Drivers).
c-tree Plus operational models: The Drivers
support FairCom's predominate operational models including
the standard (non-SQL) client/server model (with TCP/IP, SPX
and Netbios communication protocols) and Multi-user Non-server
model.
File Definitions: Your application
vendor may have a Driver dictionary already available. If
so, please skip the remainder of this. If your not sure, please
consult your application provider as using an existing dictionary
simplifies the initial Driver setup.
If you know a Driver dictionary is not available
for your application, you may easily build one if your vendor
has supplied the necessary file definitions (Incremental File
Structures or parameter file) and the field layout. To help
with determining if you have the necessary information available
for you, please download the ctinfo
utility and execute it on one of your data files. If the
utility reports your files are c-tree Plus Driver ready, please
check all of the data files you desire to access via the c-tree
Plus ODBC Driver or Crystal Reports™ Driver.
Resource Records
A resource record is a special record
added to a data file that can be thought of as a file header
extension. For example, when a data file is created with c-tree
Plus Incremental File Structures (see below), the file definitions
are stored by default in a resource record within the file.
IFIL's/DODA'S
The two methods for defining file
definitions within c-tree Plus are Incremental File Structures
(IFIL, IIDX, ISEG arrays) and parameter files. Incremental
File Structures are recommended for the ODBC Driver so file
definitions are available within the file's resource record
(defined above). A DODA (data object definition array) contains
the field definitions that make up a particular file. The
DODA is added to a c-tree Plus data file (into the same resource
record as the file definitions) using the function PUTDODA(). |
|
|
|
| |
Driver Constraints
The following limits apply to the c-tree Plus
ODBC Driver. Additional limits may be imposed by third-party
ODBC applications. |
|
Description
|
Value
|
Concurrently open tables:
16-bit multi-user non-server driver
|
305 |
|
All others |
405 |
| Number of rows: |
2 billion |
| Number of columns in a query:
|
256 |
| Size of a column: |
2 gigabytes |
| Number of connections: |
limited by memory |
| Table, index and column
name length: |
64 characters |
| Table qualifier: |
68 characters |
| Maximum characters in a
literal: |
1000 |
| Maximum columns in a CREATE
TABLE statement: |
256 |
Maximum ANDed predicates:
This example uses three ANDed predicates:
SELECT * FROM abc
WHERE c1 AND c2 AND c3 AND c4 |
300 |
| Number
of joined tables: |
limited by memory |
|
|
|
| |
Supported Data Types
The c-tree Plus ODBC Driver supports the following
data types. Support for additional types can be added using
the FairCom Driver SDK.
Note: If multiple c-tree Plus types
relate to one SQL type, the default c-tree Plus type for that
SQL type is shown in bold type. For example, the c-tree Plus
types CT_INT2 and CT_INT2U both map to SQL_TINYINT. Going
from SQL to c-tree Plus, SQL_TINYINT maps to CT_INT2.
|
| c-tree
Plus Data Type |
SQL
Data Type
|
| Name |
Description |
Description |
Description |
| CT_ARRAY |
Variable-length
binary data with a 4-byte length. |
SQL_LONGVARBINARY
|
Binary data
< 2 gigabyte. |
| CT_BOOL |
One byte Boolean. |
SQL_BIT |
1 byte. 1 is true.
0 is false. |
| CT_CHAR |
Signed one-byte
integer. |
SQL_TINYINT
|
1 byte unsigned
number. |
| CT_CHARU |
Unsigned one-byte
integer. |
| CT_INT2 |
Signed two-byte integer.
|
SQL_SMALLINT
|
2 byte integer.
|
| CT_INT2U |
Unsigned two-byte integer. |
| CT_INT4 |
Signed four-byte
integer. |
SQL_INTEGER |
4 byte integer. |
| CT_INT4U |
Unsigned four-byte
integer. |
| CT_MONEY |
Signed four-byte integer interpreted
as number of pennies. |
SQL_DOUBLE |
IEEE 8 byte float.
|
| CT_DATE |
Unsigned four-byte
integer interpreted as the number of days since 02/28/1700.
|
SQL_DATE |
IEEE 8 byte
double. |
| CT_TIME |
Unsigned four-byte integer
interpreted as the number of seconds since midnight. |
SQL_TIME |
IEEE 8 byte double. |
| CT_TIMES |
Eight-byte
floating point interpreted as the number of days since 12/30/1899
plus the number of seconds since midnight divided by 86400.
|
SQL_TIMESTAMP
|
IEEE 8 byte
double. |
| CT_SFLOAT |
Four-byte floating point.
|
SQL_REAL |
IEEE 4 byte float. |
| CT_DFLOAT |
Eight byte
floating point. |
SQL_DOUBLE |
IEEE 8 byte
float. |
| CT_SQLBCD |
Undefined in c-tree Plus. Use
the FairCom Driver SDK to perform conversion for your application.
|
SQL_DECIMAL |
Exact numeric quantity with
up to 72 decimal digits of precision. |
| CT_FSTRING |
Fixed length
field delimited data. |
SQL_CHAR |
Character
data < 256 bytes. |
| CT_F2STRING |
Fixed length data with 2-byte
length count. |
SQL_LONGVARCHAR
NOTE: SQL_LONGVARCHAR maps to CT_STRING |
Character data 3 256 bytes
and < 2 gigabytes. |
| CT_F4STRING |
Fixed length data with 4-byte
length count. |
| CT_STRING |
Varying length field delimited
data. |
SQL_VARCHAR |
Character data < 256 bytes. |
| CT_2STRING |
Varying length data with 2-byte
length count. |
SQL_LONGVARCHAR
|
Character data 3 256 bytes
and < 2 gigabytes. |
| CT_4STRING |
Varying length data with 4-byte
length count. |
| CT_PSTRING |
Varying length data with 1-byte
length count. |
| CT_FPSTRING |
Fixed length data with 1-byte
length count. |
SQL_CHAR
NOTE: SQL_CHAR maps to CT_FSTRING
|
Character
data < 256 bytes. |
|
|
|
| |
Grammar
Minimum SQL Grammar
The minimum Grammar requirements (fully met
by the c-tree Plus ODBC Driver) are as follows:
- Create Table
- Delete (searched)
- Drop Table
- Insert
- Select
- Update (searched)
The following tables provide some examples of
the minimum SQL grammar supported by the c-tree Plus ODBC
Driver: |
| Grammar
|
Examples
|
Comments |
| CREATE TABLE
|
CREATE TABLE sal (emp_id
integer, name char(50), salary
float, hire_date date)
CREATE TABLE emp (emp_id integer NOT NULL,
PRIMARY KEY (emp_id) |
Column constraint definitions
supported:
NOT NULL.
Table constraint definitions supported:
UNIQUE and PRIMARY KEY DEFAULT. Default-value is not supported.
|
| DELETE |
DELETE FROM sal WHERE name
= 'John Smith' |
|
| DROP TABLE |
DROP TABLE sal |
[CASCADE | RESTRICT] is not
supported. |
| INSERT |
INSERT INTO sal VALUES (34086,
'Fred Black', 45000.00, '1992-05-25') |
|
| SELECT |
SELECT * FROM
sal
SELECT emp.emp_id, sal.salary FROM emp,
sal WHERE emp.emp_id = sal.emp_id |
|
| UPDATE |
UPDATE sal SET salary = 35000.00
WHERE emp_id = 25089 |
|
Core SQL Grammar
The Core Grammar supported by the c-tree Plus
ODBC Driver is as follows:
- Create Index
- Drop Index
- Select
-Approximate numeric literal
-Between predicate
-Correlation name
-Exact numeric literal
-IN predicate
-Set function
- Subqueries
The following tables provide some examples of
the core SQL grammar supported by the c-tree Plus ODBC Driver: |
| Grammar
|
Examples
|
Comments |
| CREATE INDEX |
CREATE INDEX empidx ON emp
(emp_id, emp_name) |
To designate a key as a primary
key, FairCom supports the UNIQUE option. |
| CREATE VIEW |
CREATE VIEW
vw_sal (v_col1, v_col2) AS SELECT emp_id, name FROM sal |
The column
list is optional. |
| DROP INDEX |
DROP INDEX emp.empuniq |
|
| DROP VIEW
|
DROP VIEW
vw_sal |
[CASCADE |
RESTRICT] is not supported. |
| SELECT |
SELECT COUNT(emp_id), dept
FROM mgrs GROUP BY dept HAVING
dept > 15 |
In addition to supporting
an order by on a column-list, as specified in the ODBC Programmer's
Reference, FairCom has extended the syntax to support an order
by on an expression-list or on any expression in a group by
expression-list.
For example:
SELECT * FROM emp ORDER BY a+b,c+d,e
This causes the result table to be ordered
by three expressions: a+b, c+d, and e.
If the expression is a positive integer
literal, then that literal will be interpreted as the number
of the column in the result set and ordering will be done
on that column. No ordering is allowed on set functions or
an expression that contains a set function.
|
| subqueries |
|
The following
types of subqueries are supported:
comparison, exists, quantified, in, and correlated. Order by
clauses are not allowed in a subquery clause. |
| approximate-numeric-literal
|
SELECT * FROM results WHERE
quotient = -4.5E-2 |
|
| between- predicate |
SELECT c1
FROM emp WHERE emp_id BETWEEN 10000 AND 20000 |
The syntax
expr1 BETWEEN
expr2 AND expr3
returns TRUE if expr1 >= expr2 and expr1 <= expr3.
expr2 and expr3 may be dynamic parameters
(e.g., SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?).
|
| correlation-name |
SELECT * FROM emp t1, addr
t2
WHERE t1.emp_id = t2.emp_id |
FairCom supports both table
and column correlation names. |
| exact-numeric-literal |
INSERT
INTO cars (car_no, price) VALUES (49042, 49999.99)
SELECT * FROM numtbl
WHERE c1 = -208.6543189 |
|
| in-predicate |
SELECT * from colors WHERE
color IN ('red', 'blue', 'green') |
|
| set-function
|
SELECT
COUNT(a+b) FROM q
SELECT MIN(salary) FROM emp |
MIN(expr),
MAX(expr), AVG(expr),SUM(expr), COUNT(*), and COUNT(expr)
are supported.
COUNT(expr) counts all non-NULL values
for an expression across a predicate. For example,
SELECT COUNT
(a+b) FROM q
counts all the rows in q where a+b does
not equal NULL. |
Extended SQL Grammar
The Extended Grammar supported by the c-tree
Plus ODBC Driver is as follows:
-
Left Outer Join (two or three-table outer join)
-
Select
-date arithmetic
-date literal
-time literal
-timestamp literal
-
Unions
-
Use
The following tables provide some examples of
the extended SQL grammar supported by the c-tree Plus ODBC
Driver:
|
| Grammar
|
Examples
|
Comments |
| LEFT OUTER JOIN
|
Two-table outer join:
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptID = dept.deptID
Three-table outer join:
SELECT * FROM (emp LEFT OUTER JOIN dept ON emp.deptID = dept.deptID)
LEFT OUTER JOIN addr ON emp.empID = addr.empID
Embedded in vendor strings:
SELECT t1.deptno, ename FROM {oj emp t2 LEFT OUTER JOIN dept
t1 ON t2.deptno = t1.deptno}
|
FairCom supports two-table
outer joins as specified in Appendix C of the ODBC Programmer's
Reference.
In addition to simple two-table outer
joins, FairCom supports n- way nested outer joins.
The outer join may or may not be embedded
in a vendor string. If a vendor string is used, the ODBC driver
will strip it off and parse the actual outer join text.
|
| UNION |
SELECT name,
status FROM tech_staff UNION
SELECT name, status FROM adm_staff |
union eliminates
duplicate rows. Union all preserves duplicate rows. |
| UNION ALL |
SELECT name,
status FROM tech_staff UNION ALL
SELECT name, status FROM adm_staff |
| date-literal |
SELECT * FROM emp WHERE
hire_date < '1992-02-02'
SELECT * FROM emp WHERE hire_date <
{d '1992-02-02'} |
FairCom supports the following
date literal format: 'yyyy-mm-dd'.
Dates may be in the range of year 0 to
9999.
Date constants may be expressed in SQL
statements as a character string or embedded in a vendor string.
FairCom treats the character string representation as a string
of type SQL_CHAR and the vendor string representation as a
value of type SQL_DATE. This becomes important when conversions
are attempted. For example, CONVERT({d '1992-02-02'}, SQL_TIMESTAMP)
is valid, whereas CONVERT('1992-02-02', SQL_TIMESTAMP) returns
an invalid SQL_TIMESTAMP value. |
| time-literal |
SELECT
* FROM bday WHERE btime = '10:04:29'
SELECT * FROM bday WHERE btime = {t '10:04:29'}
|
FairCom
supports the following time literal form: 'hh:mm:ss'.
Time constants may be expressed in SQL
statements as a character string or embedded in a vendor string.
FairCom treats the character string representation as a string
of type
SQL_CHAR and the vendor string representation as a value of
type SQL_TIME. |
| timestamp-literal |
SELECT * FROM bday WHERE
btime = '1965-08-25 05:25:00'
SELECT * FROM bday WHERE btime={ts '1965-08-25
05:25:00'} |
FairCom supports the following
timestamp literal format: 'yyyy-mm-dd hh:mm:ss'.
Timestamp constants may be expressed in
SQL statements as a character string or embedded in a vendor
string. FairCom treats the character string representation
as a string of type SQL_CHAR and the vendor string representation
as a value of type SQL_TIMESTAMP. |
| date arithmetic |
SELECT
* FROM inv WHERE inv_date > '1993-01-01' AND inv_date <
{d '1993-01-01'} + 30
SELECT * FROM pay WHERE pay_date - inv_date
> 30 |
FairCom
supports adding or subtracting an integer from a date where
the integer is the number of days to add or subtract, and
the date is embedded in a vendor string. (This is equivalent
to executing a CONVERT on the date.)
FairCom also supports subtracting one
date from another to yield a number of days. |
| extended predicates |
{pred contains, col1, 'text'} |
Uses extended vendor string
syntax. |
|
|
|
| |
ODBC Functions
The c-tree Plus ODBC Driver supports the following
ODBC API functions:
|
|
Core Functions
|
Level 1 Functions
|
Level 2 Functions
|
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLCancel
SQLColAttributes
SQLConnect
SQLDescribeCol
SQLDisconnect
SQLError
SQLExecDirect
SQLExecute
SQLFetch
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt
SQLGetCursorName
SQLNumResultCols
SQLPrepare
SQLRowCount
SQLSetCursorName
SQLTransact |
SQLBindParameter
SQLColumns
SQLDriverConnect
SQLGetConnectOption
SQLGetData
SQLGetFunctions
SQLGetInfo
SQLGetStmtOption
SQLGetTypeInfo
SQLParamData
SQLPutData
SQLSetConnectOption
SQLSetStmtOption
SQLSpecialColumns
SQLStatistics
SQLTables |
SQLBrowseConnect
SQLDataSources
SQLDescribeParam
SQLDrivers
SQLMoreResults
SQLNativeSql
SQLNumParams
SQLPrimaryKey |
Scalar Functions
he FairCom ODBC Driver supports ODBC scalar
functions which may be included in an SQL statement as a primary
expression. For a description of the SQL syntax for
these functions, see the ODBC Programmer's Reference.
String Functions
CONCAT
LCASE
LEFT
LENGTH
LOCATE
LTRIM
RIGHT
RTRIM
SUBSTRING
UCASE
Example
SELECT emp_id FROM emp WHERE LCASE(emp_name) = 'karen jung'
Numeric
Functions
MOD
Example
SELECT * FROM tbl1 WHERE MOD(col1, 100) = 0
Date Functions
CURDATE
CURTIME
DAYOFMONTH
DAYOFWEEK
MONTH
YEAR
Example
SELECT * FROM emp WHERE YEAR(hire_date) <1990
System Functions
DATABASE
IF
NULL
USER
Example
SELECT * FROM emp WHERE ename = USER()
The system scalar functions IF and NULL are
SQL extensions.
IF allows you to enter different values depending
on whether the condition is true or false. The syntax
is:
IF(predicate, expression, expression)
For example, if you want to display a column
with logical values as "True" or "False"
instead of a binary representation, you would use the following
SQL statement:
SELECT IF(logicalcol=1, "True", "False")
The system scalar function NULL allows you
to set a column as null values. The syntax is:
NULL()
For example, the following SQL statement retrieves
null values:
SELECT NULL() FROM emp
Conversion Function
CONVERT
Example
SELECT empno FROM emp WHERE hire_date > CONVERT('1993-01-02', SQL_DATE) + 30
|
|
|
|
|
|
Home | About
FairCom | Products | Support
| Sales | Contact
Info | Site Map | Download | Newsletter |
FairCom support has been the best that I have experienced. Here at FedEx we say thanks a lot with what we call a BZ (Bravo Zulu)!