SQL Operations Guide

Using Existing ISAM Data with FairCom DB SQL

In addition to creating new files under the FairCom DB SQL, it is possible to use existing c-tree Plus files under the FairCom DB SQL.

Table Definition Requirements

• Tables must contain IFIL and DODA structures. These can be added after the fact for existing files and are inserted automatically for files created by c-treeDB API and FairCom DB SQL.
• The ISAM application must use the c-tree Plus data types (as defined in the DODA) as in the c-tree - SQL data type mapping. For example, a CT_CHAR is used in the SQL to store a 1-byte integer.

  Note: There is an incompatibility between the use of CT_ARRAY in the current c-tree Plus ODBC Driver and the use of CT_ARRAY in c-treeDB API and c-FairCom DB SQL, including the FairCom DB SQL ODBC Driver.

• The table must have either TRNLOG or PREIMG in its file mode to use the ROLLBACK WORK and integrity constraint capabilities.
• To properly handle NULL, the table must contain the $NULFLD$ field, a hidden field generated by c-treeDB API at creation time. Tables created with the c-treeDB API Interface (used under FairCom DB SQL) have a hidden field, $NULFLD$, which is used to determine if each user-created field in the record buffer has a NULL value. FairCom DB SQL requires this capability to implement the "not null" constraint. c-treeDB API and FairCom DB SQL will access tables without the $NULFLD$ field, but the table’s fields will always return a non-NULL status.
• Avoid referencing a ROWID in a JOIN. To properly handle JOINS referencing ROWID, the table would need to contain the $ROWID$ field (a hidden field generated by the c-treeDB API at creation time). c-treeDB API and FairCom DB SQL should work with tables without the $ROWID$ field by using the record offset as the ROWID tuple identifier. SQL statements like “select * from table where ROWID > '4'” will fail because using record offset as ROWID will give us record offsets instead of sequential numbers.

  Note: When c-tree updates a variable-length record, the record offset for the record may change if the updated record size is larger than the original record. In this particular case, the ROWID for this ROW will not be unique, as required by the SQL standard.

Index Definition Requirements

• If an index contains a segment made by a “partial field” (i.e., does not use Schema segment modes or the segment starting offset and the segment length are different from the field starting offset and the field length) FairCom DB SQL cannot access this index, even though the index is still properly updated by c-tree.

  There is one noticeable exception to this rule for segments on string fields that are exactly 1 byte less than the string size. In this scenario, when importing the index, the -z option identifies the situation and makes SQL aware and capable of using the index.

• If there is more than one logical index in one physical index file, the DROP INDEX and the DROP TABLE commands may not work properly.
• Alter table may not work correctly if tables contain index segments that do not start at field boundaries and/or span over several fields.

  Example: If a field is deleted from the table, and this field is part of an index segment that spans over several fields, c-treeDB API may not know how to adjust the index segment length after the field is deleted from the table. The resulting index definition may not be correct. Tables with unusual characteristics may also not work correctly and the altered table may inherit c-treeDB API characteristics that will prevent them from working in the original application.

• NULL key support disqualifies an index from being used for SQL.
• Conditional index is not used by SQL.
• Temporary indexes are not eligible for SQL use.
• An alternate collating sequence disqualifies an index from being used for SQL.

Adding DODAs to Existing Files

To use FairCom DB SQL with existing ISAM files that do not already have DODAs, add a DODA to each file. This is most easily done with a developer-created utility that opens each file and calls PutDODA() to insert the required resource into that file. The utility should:

1. Include a data object definition array (DODA), simply an array of DATOBJ structures, defined below.
2. Open each data file in ctEXCLUSIVE mode.
3. Call PutDODA() for each file to insert the DODA resource.
4. Close the files.

A DODA is a data object definition array. Each element of the array is comprised of a structure of type DATOBJ. Only three of the first four fields of the DATOBJ are required for c-tree Plus.

DATOBJ is defined as follows:

typedef struct {

pTEXT fsymb; /* ptr to symbol name */

pTEXT fadr; /* adr of field in record buffer */

UCOUNT ftype; /* type indicator */

UCOUNT flen; /* field length */

...

} DATOBJ;

• fsymb points to a unique symbolic name for the field and should not be NULL.
• fadr is not used by c-tree Plus (its value is ignored).
• ftype is one of the field types specified in the “Field Types” table.
• flen is set to the field’s length for fixed length fields, or the known maximum for varying length fields with a known maximum length, or zero for varying length fields without a known maximum length. If the field type has an intrinsic length, which is true for types CT_CHAR through CT_DFLOAT, a zero length is automatically replaced by the intrinsic length.

Given a data record with the structure:

struct {

TEXT zipcode[10]; /* Zip code */

LONG ssn; /* social security # */

TEXT name[50]; /* name */

} DATA_FORMAT;

The corresponding DODA would be defined as:

DATOBJ doda[] = {

{"ZipCode",NULL,CT_FSTRING,10},

{"SocialSecurity",NULL,CT_INT4},

{"Name",NULL,CT_STRING,50}

};

Note: The two string fields show the difference between fixed-length and variable-length strings. zipcode, CT_FSTRING, takes up a fixed space in the record (10 bytes) and does not require a NULL to terminate the string. name, CT_STRING, takes up a variable amount of space up to a maximum of 50 bytes and is NULL terminated.

Available field types are described in “Record Schema - Field Types” in the c-tree Plus Programmer’s Reference Guide.

PutDODA() assigns the contents of a data object definition array (DODA) to data file datno, which must be opened in ctEXCLUSIVE mode. DODA points to the beginning of the DODA as described above. The numfld parameter indicates how many data fields are in the DODA, three in the example above. PutDoda() has the following syntax:

COUNT PUTDODA(COUNT datno,pDATOBJ doda,UCOUNT numfld)

See the PutDODA() function description in the c-tree Plus Function Reference Guide and “Record Schemas” in the c-tree Plus Programmer’s Reference Guide for additional details. Contact your nearest FairCom office for assistance if needed.