Using native SQL for manipulating data as well as structure (schema) of ISAM-emulated tables is a supported scenario, for example, for being able to share data with non-Xbase++ applications. In the following, the potential pitfalls and general requirements are discussed which must be put into consideration.

Structure and Schema Manipulation​

The structure of an ISAM-emulated table can be manipulated via native SQL commands, as long as the following rules are being followed:
  • Do not remove or otherwise manipulate the internal columns prefixed with "__".
  • Do not use data types which are not supported by the ISAM emulation
  • Do not manipulate columns which are referenced by an ISAM index key-value expression.
  • Do not manipulate the tables prefixed with "alaska-software".
If you want to add a new field which should be part of an ISAM index first add the column using SQL and then create the ISAM index via OrdCreate(). The same order applies to tables. First create the ISAM emulating table via DbCreate() and then use SQL ALTER TABLE to maintain columns.

Manipulating Data​

  • Never change any value in the tables with the "alaska-software" prefix. Those are meta data tables required by the PostgreSQL DBE to work properly.
  • All values in an ISAM-emulated table can be manipulated, except for the data of the internal columns prefixed with "__". These must not be touched.
  • Never use a SQL DELETE on a ISAM emulating table. Use the ISAM operations like DELETE or DbDelete() instead.

Using Views​

See the following article on using views with ISAM-emulated tables: https://ilx.alaska-software.com/index.php?ams/isam-meta-columns-in-views.74/

References:​