In a recnum table, individual records are uniquely identified by a record identity. A record identity is a single numeric column, with no decimal places, that uniquely identifies a record in a table. The driver is informed about the record identity by specifying the (unique) index made up of the record identity column.
There are two programming styles that can be used to handle tables in the Application framework: RowId and RECNUM style. When using recnum tables, Both RECNUM and RowId style can be used.
A record identity used to be required for historic reasons. A short introduction on record numbers explains that history.
The DataFlex embedded database stores its information in disk files. Every table is stored in several disk files. One of the files contains the actual data, the other files contain the indexes, a DataFlex compiler include file (so the table can be used in programs) and a column name file.
The DataFlex embedded database data is accessed according to a defined sort order called an index. One order always exists and that is the “storage order.” This order can be used from within a DataFlex environment and is referred to as the record number (RECNUM) order. The record number is a consecutive positive number starting at 1 that indicates a record’s relative position in the table. It can be used to search the table for a specific record.
Since record numbers indicate a record’s relative position in the table, it can be used in conjunction with the record size to determine the records position in the disk file. This involves minimal disk access and results in a fast operation.
The record number can be used within the DataFlex environment as if it were a field in the table. In reality, it is a logical column; no disk space is allocated to store record numbers. Record numbers are assigned when creating records. DataFlex programmers cannot control the record number for a new record.
It has been strongly discouraged by Data Access to use record numbers as meaningful information. Older systems programmed in DataFlex sometimes misused the logical column as customer number or order number.
DataFlex record numbers have the following characteristics:
Uniquely identify a row
Automatically available
Automatically maintained
Unchangeable for a given row
Reusable
Offers faster access than other indexes
Sequential consecutive number ranging from 1 to 16 million
DataFlex no longer relies on the existence of record numbers but it is still possible to use legacy application code that does rely on the existence of record numbers by using RECNUM tables.
The API requires every recnum table it accesses to have a record number like column called the record identity. There is only a need to access recnum tables in a non embedded database if the program uses the RECNUM programming style. This column can be a logical column or actually exist in the table. It should have the following characteristics:
Uniquely identify a row
Numeric with no decimal places
Ranges from 1 to 2^31 (2147483647)
Is available as a unique index
The API does not require the record identity to
Be automatically available
Be automatically maintained
Be unchangeable for a given row
Be reusable
Offer faster access than other indexes
Be sequential consecutive numbers ranging from 1 to 16 million
All recnum tables accessed by the Data Access Database API must fulfill the four characteristics. Since the API does not require that the record identity is automatically maintained there may be drivers that require additional programming to maintain the identity. Also be aware that other non-DataFlex applications creating data in tables accessed by DataFlex need to make sure that the record identity meets the minimum requirements of the API.
Other database types do not have a record number column or use some other form of identifying the rows in a table. Some databases use record number like functionality, others use so-called ROWID’s, some use time stamps others do not use any type of logical identifier (at least not accessible by client programs).
Note that we recommend using standard tables (tables without RECNUM) with databases other than the DataFlex embedded database. You will only need to use a RECNUM column if an application contains code that references RECNUM. If an application does so, we recommend updating such code to not use RECNUM.
Microsoft SQL Server does not have a record identity column that is automatically available for every table. It has a type attribute that we can use so the API for recnum tables requirements are met; it is called the IDENTITY type attribute. For more information on this type, see the SQL Server documentation.
When converting a table to a SQL Server recnum table, an extra column will be inserted before the original definition. This column will be called RECNUM. It is an integer column that has the IDENTITY attribute set. Since SQL Server automatically maintains identity columns, every application accessing the table will follow the API’s minimum requirements for recnum tables. The program that used the original table does not need to be adjusted in any way.
IBM’s DB2 Universal Database does not have a record identity column that is automatically available for every table. It has a type attribute that we can use so the API requirements for recnum tables are met; it is called the IDENTITY type attribute. For more information on this type, see the DB2 documentation.
When converting a table to a DB2 recnum table, an extra column will be inserted before the original definition. This column will be called RECNUM. It is an integer column that has the IDENTITY attribute set. Since Db2 automatically maintains identity columns, every application accessing the table will follow the API’s minimum requirements. The program that used the original table does not need to be adjusted in any way.
Since ODBC is an open specification it is unknown if the data source supports record identity columns, RowId's, time stamps or none of the mentioned identifiers.
When converting a table to an ODBC recnum table, an extra column can be pre-pended to the original definition. This column will be called RECNUM. It is a numeric column; the exact type depends on the types supported by the actual back-end used. The program that used the original data may need to be adjusted.
Some databases support an auto-increment feature for certain column types. If this feature is supported it can be used by altering the database configuration file.
See the Database Specific Configuration File for a discussion of pre-defined database configuration files.
See Driver Configuration for a more information on configuration files.