BTRV Edition User's Guide

The c-treeRTG SQL Challenge

Many COBOL ecosystems support a separate relational database for SQL requiring batch copy data into the relational database, resulting in out-of-sync data - a D+1 or D+7 constraint. The nature of data and record definitions (which may have redefines) used in these languages does not fit into a traditional relational (SQL-based) model of table and record definitions, which poses a unique challenge.

The FairCom approach is different. Our engineering team implemented the required interfaces through our ISAM technology. These interfaces parallel the Btrieve approach to indexed files without the complications of SQL table remapping. From the application's point of view, c-treeRTG provides a more advanced “native” file system with no limitations for the application programmer. In addition, the interface between the FairCom DB SQL engine and the native data allows concurrent read/write access through standard SQL.

c-treeRTG provides concurrent access to data using SQL in addition to Btrieve direct record file I/O routines. This provides a truly flexible environment allowing you to precisely choose data access interfaces balanced for performance and information demands. While some data conversion is required for SQL access, the FairCom SQL approach is far simpler and more efficient than the remapping required by other implementations. The advantage is your original application maintains direct access to the data with no added complexity.

Supported SQL Features

The c-treeRTG products allow your Btrieve data to be exposed through SQL, giving you full access to read, write, insert, and delete records. This support is targeted at the most important SQL functionality and includes:

• Reading and writing and updating records, concurrent with direct RTG access
• Advanced joins across tables
• Views
• Standard table, column and view access permissions.
• User-defined scalar functions
• Stored procedures
• Triggers

Notable Limitations

There are multiple considerations when combining SQL.with RTG.

• A table created through SQL cannot be accessed using c-treeRTG ISAM drivers for Btrieve.
• Referential integrity is applicable at the SQL level only; no check is performed to ensure referential integrity.
• SQL triggers are applicable at the SQL level only; your <FC_PRODUCT_READY> program cannot invoke a SQL trigger.
• If transactions are disabled on Btrieve files, this creates additional considerations when accessing these files via SQL. For example, rollbacks do not affect these files; transaction isolation is not provided; etc.

Developing Stored Procedures, Triggers and User Defined Functions

Stored Procedures, triggers and user defined functions are supported with Java across all platforms giving you nearly complete access to any Java class framework you can include. FairCom recommends developing within our NetBeans plug-in (found in the sql.stored.procs folder), which eases Java stored procedure development, debugging, and deployment.

For Microsoft Windows environments, the .NET framework can be used with our Microsoft Visual Studio plugin.