|
|
|
Set an existing ROWID column as visible for a given table.
fc_set_rowid_visible('owner', ‘table’, ‘rowid’, ‘rowid_idx’);
Description
Use this function when you have existing tables that include the hidden $ROWID$ field and do not have a primary key. When you create new tables, use the JSON DB API because it ensures the table works well at all API layers, including the JSON DB, SQL, c-treeDB, and ISAM APIs, The JSON DB API follows best practices and does not create the hidden $ROWID$ field; instead, it automatically creates an id field that is the primary key of the table.
You can optionally create a hidden ROWID column on tables to help bridge between FairCom DB’s SQL and c-treeDB layers. This column has a special internal name of $ROWID$ and has an index. It uses the serial segment auto-numbering index mode, and, thus, creates a unique permanent id value for each row.
The stored procedure, fc_set_rowid_visible(), makes an existing c-treeDB ROWID visible as a column in the table. It also makes its index visible. You must assign a name to the column and index. When the column is visible, you can use it in queries. You cannot update or reposition the ROWID column.
A check is made that the table exists, and has a ROWID field. SQL_ERR_NOTBL or SQL_ERR_NOCOL is returned if not.
A check is made to ensure that the rowid isn’t already exposed with that id name - if so, it fails with a duplicate column exception.
Parameters
owner - Should match existing table owner, or the internal get table ID call doesn’t work.
table - table name with the ROWID to expose.
rowid - symbolic name of the exposed ROWID field
rowid_idx - assigned name for the ROWID index
Example
ISQL> call fc_set_rowid_visible('admin', 'test1', 'id', 'id_idx');
0 records returned
ISQL> select col from syscolumns where tbl='test1';
COL
---
name
age
rating
id
4 records selected
ISQL> table test1
COLNAME NULL ? TYPE LENGTH CHARSET NAME COLLATION
------- ------ ---- ------ ------------ ---------
id NOT NULL BIGINT 8
name CHAR 10
age INT 4
rating TINYINT 1
Limitations
This should only be called once for any given table. Unpredictable results can potentially happen with repeated executions.
