BTRV Edition User's Guide

Common SQL Limitations with FairCom RTG Data

FairCom DB provides full, industry-standard SQL and you can create a database that is accessed exclusively through SQL. The limitations discussed below arise when accessing data from both SQL and ISAM interfaces on files created through direct record-oriented ISAM interfaces. Due to the high flexibility of ISAM contrasted with the discipline of SQL, some SQL features cannot be expected to work in this environment.

The following issues are important to keep in mind when developing FairCom DB or FairCom RTG applications that use both ISAM and SQL to access the same data files created through the ISAM interface.

	Limitation	Table definitions are done through ISAM; any changes to the table definitions must be done through ISAM. This implies: You cannot execute Alter Table over an ISAM data file. Referential Integrity cannot be defined across ISAM tables.
	Consideration	Not all ISAM indexes are compatible with SQL. In particular, some indexes do not collate as SQL mandates, for example, certain COMP types, and therefore cannot be used by SQL for sorting or to perform searches depending on the collation. This does not limit functionality, however can impact performance. In such a case it is possible to create new indexes directly from SQL that satisfy SQL needs and apply to both SQL and Btrieve access.
	Has Workaround	ISAM files are not portable among different endianess, even if you are using portable data types. You can use the FairCom ctunf1 utility to convert endianess before moving between a big-endian and a little-endian system given that all data is ASCII string format and no native binary fields are included in tables.

Triggers

In addition, keep in mind that SQL triggers are applicable at the SQL level only. NoSQL APIs (e.g., ISAM, c-treeDB, COBOL, c-treeDB Java, etc.) cannot invoke a SQL trigger. This is not a limitation specific to FairCom DB and FairCom RTG: a trigger is a SQL concept that is not supported by ISAM.

If you desire to have an action executed on record changes below the SQL layer, consider the FairCom DB Record Update Callback feature discussed in the FairCom DB Programmer's Reference Guide:

Record Update Callback

Note: Additional considerations apply if you use the FairCom DB Professional Developer's Kit to expose your ISAM data through SQL, giving you full read, write, insert, and delete access to the data. Please contact FairCom should you have questions about specific use cases.