Posted By: David Faust
Your successful c‑tree application runs day in and day out quietly and reliably handling your valuable data with the performance that can only be found in FairCom’s record oriented ISAM technology. Modernization and interoperable systems now take your data queries to the next level.
SQL is the obvious choice. SQL’s ad hoc query advantages provide a powerful and flexible interface for advanced reporting needs. In addition, this is a great upsell opportunity for OEM developers looking to easily extend the features of their applications and then earn additional revenue from selling an upgrade to c‑treeACE SQL. Once imported, you can query data via any standard SQL interface. ODBC, JDBC, ADO.NET, dbExpress, Direct SQL, dynamic (interactive) SQL queries… c‑treeACE SQL supports them all.
c‑treeACE SQL Import
How do you add this capability to your existing c‑tree data? First and foremost, remember that all c‑treeACE SQL tables are nothing more than standard c‑tree data and index files. There is nothing inherently special about these files, other than a few additional hidden fields added by default. Even these fields are not always mandatory. As long as the IFIL and DODA definitions are in place, nearly every c‑tree data file can be used as a c‑treeACE SQL table. Even the IFIL and DODA information can be externally defined in XML format for those applications where actual files cannot be modified. c‑treeACE SQL can easily be extended to many existing c‑tree data files. With a careful bit of planning you can easily enjoy ISAM speed and control with flexible SQL reporting.
Easy Import with ISAM Explorer
c‑treeACE introduced the ISAM Explorer tool which allows fast and easy database and table creation and maintenance. It also provides fast and easy import functionality. By default, ISAM Explorer creates c‑treeACE SQL ready databases and tables, so you can quickly add SQL capabilities to existing applications with just a few mouse clicks.
Here’s how. We’ll use the tutorial 1 customer master example to demonstrate a table import. You’ll first need to create this table using the c‑treeDB C or C++ Tutorial 1 example. This creates an ISAM table of customer records with complete IFIL and DODA resources necessary for import. This table will reside in the server’s working directory.
- Connect c‑treeACE ISAM Explorer to a c‑treeACE Server. This can be either locally, or a server accessible on your network.
- Right click on a database and choose “Add Existing Table.”
Caution: An existing data file can be associated (imported) with multiple databases. This is not advised and can lead to unexpected data loss, or conflicting access.
- Enter the name of the existing c‑tree data file and the path to locate the file. By default, ISAM Explorer expects files to exist in the c‑treeACE SQL database directory (for example ctreeSQL.dbs).
Pay particular attention to the path as this is usually the step that causes the most confusion. The path is always relative to c‑treeACE SQL server’s working directory, which defaults to the current executable directory of c‑treeACE SQL. (Hint – You can easily identify this directory by the presence of the CTSTATUS.FCS status log file.) If this location is defined with a LOCAL_DIRECTORY keyword, then all paths are relative to this location. If the files are located in a c‑treeACE SQL database directory (for example, ctreeSQL.dbs) then this path can be given.
Of course, you can always use a full path to ensure the import process will properly locate your file. Remember, there are no requirements for c‑tree file locations and you are free to position your files wherever they best fit your application needs.
- Due to differences in existing table definitions, there are multiple options that can be considered when importing these tables. Click “Show Advanced Options” to configure the import process for your exact import needs. Note that these correspond to many of the options available in the command line ctsqlimp utility.
The most common choices are “Skip Indices” (for partial segments) and “Skip fields not Complying with Conventional Identifier Rules” (for example, “hidden” fields with names containing underscores).
- Upon successful import, ISAM Explorer automatically updates the c‑treeACE SQL system tables (dictionaries) and your data is now ready to query.
- Now you can query your data with other SQL interfaces, such as SQL Explorer.
You now have your data available for access with both your existing application AND c‑treeACE SQL!
Table Definition Requirements and Limitations
To take advantage of the ability to fully co-exist with c‑treeACE SQL, certain requirements must be met to ensure compatibility.
- Tables must contain IFIL and DODA structures. These can be added after the fact for existing files (using the PutIFIL() andPutDODA() API calls) and are inserted automatically for files created by c‑treeDB and c‑treeACE SQL. Contact FairCom for other options (XML based) if you’re unable to directly modify existing files.
- An ISAM application must use corresponding c‑treeACE data types (as defined in the DODA) compatible with the c‑treeACE SQL data type mapping. For example, a CT_CHAR field type is used in c‑treeACE SQL to store a 1-byte integer. (See “Types SDK” below for type conversions when using non-standard types.)
Note: There was a previous incompatibility in the handling of imported CT_ARRAY types in c‑treeACE SQL V8 and prior due to expected field length headers not in place. Beginning in c‑treeACE SQL V9 this is now transparently handled in most situations.
CT_ARRAY fields are imported by default as a c‑treeACE SQL BINARY field. c‑treeACE SQL expects the first four bytes of a binary field to specify the length of the field. When you create a table with c‑treeACE SQL these four bytes are automatically created and maintained for you. When considering an existing CT_ARRAY field, you previously had to explicitly include these four prefix bytes and assign the appropriate value. This should no longer be required with V9 forward. Should you have questions about importing c‑treeACE CT_ARRAY fields, please contact your nearest FairCom office for suggestions and advice. We’re here to help.
- The table must have either TRNLOG or PREIMG in its file mode to use the ROLLBACK WORK and integrity constraint capabilities.
- Superfiles are not supported by c‑treeACE SQL.
- To handle NULL values, the table must contain the $NULFLD$ field, a hidden field generated by c‑treeDB at creation time. Tables created with the c‑treeDB interface (used with c‑treeACE SQL) have a hidden field, $NULFLD$, which is used to determine if each user-created field in the record buffer has a NULL value. c‑treeACE SQL requires this capability to implement constraints. c‑treeDB and c‑treeACE SQL will access tables without the $NULFLD$ field, however, the table’s fields will always return a non-NULL status.
- To properly handle JOINS referencing a ROWID value, the table should contain the $ROWID$ field (a hidden field generated by c‑treeDB at creation time). c‑treeDB and c‑treeACE SQL will work without a $ROWID$ field in the table, and will use the record offset as the ROWID tuple identifier. As a result, SQL statements such as SELECT * FROM TABLE WHERE ROWID > ‘4’ will fail as the ROWID will return 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.
When importing existing tables, existing indexes as defined in the IFIL resource are considered. One special case can cause particular challenges. As the concept of a “field” does not always exist in many existing c‑tree ISAM applications, there may be index segments that only span a portion of field data as defined in the DODA. These are known as partial segments and frequently found in older ISAM applications. While SQL standards do not support this construct, indexes containing partial segments can be successfully imported into c‑treeACE SQL. These indexes will only be used for lookups on existing data. New inserts and record updates via SQL will not update these indexes, as the c‑treeACE SQL definition (requiring full fields) does not match the existing partial segment definition. Your existing ISAM application will continue to update these indexes. As a result, queries may come back with unexpected result sets if you’re not expecting this situation.
The easiest solution, of course, is to drop these indexes after import (or skip them entirely during the import with the option provided) and re-define appropriate indexes via SQL after the import process is complete. Your IFIL structures will be updated, and in many cases, the change may be transparent to existing applications.
Note: If there is more than one logical index in one physical index file, the DROP INDEX and the DROP TABLE commands will also not work properly.
c‑treeACE SQL Types SDK
At it’s core, the import process does a “best guess” to map data types to a compatible c‑treeACE SQL type. The optimal mapping occurs when the application uses c‑treeDB data types, which map exactly to their c‑treeACE SQL counterparts.
Existing application data and index files, that is, data created prior to the publication of the c‑treeDB API, may have implemented certain field types in a way that do not have an exact match and hence, are incompatible with c‑treeACE SQL. c‑treeACE SQL relies upon c‑treeDB functionality. Field types such as CT_DATE, CT_TIME andCT_TIMES are the most common examples of existing data that may be incompatible with c‑treeDB.
In these cases, FairCom can offer a Types SDK which allows converting data on the fly from existing storage to SQL presentation. For example, a date stored as a system time value (four byte integer Unix times are the most common) can be properly returned as a c‑treeACE SQL date with the proper epoch conversion applied.
Contact your nearest FairCom office if you believe you have data types that do not directly map to c‑treeACE SQL. Our experienced engineering team has a rich experience in solving these unique data query challenges, and can help you quickly implement a successful types SDK callback module.