Skip to content

Record Identity and RowId

Every table needs an identifier that uniquely identifies a record. This identifier is used to find and re-find records. This is done frequently, and therefore the identifier must provide the fastest way possible to find a record.

Some databases have a built-in record identifier in every table. The DataFlex embedded database and Pervasive.SQL both auto-assign unique numeric identifiers to each record. These identifiers, referred to as recnum, provide the fastest way to find a single record.

Most SQL databases, such as Microsoft’s SQL Server and IBM’s DB2, do not provide a built-in record identity. Instead, tables in these databases almost always contain primary keys and an index that provides fast access to any record via that primary key. The primary key can be defined as a single column or as a combination of columns. The data type of the primary key column(s) may be of any type.

If your database supports recnum, you can use recnum-based commands and Data Dictionary messages to identify and find records.

// Low level commands using recnum
Integer iTempRec
Move Customer.Recnum to iTempRec
Clear Customer
:
Move iTempRec to Customer.Recnum
Find EQ Customer by recnum

// Data Dictionary methods using recnum
Integer iTempRec
Integer iFile
Get Current_Record of hoDDO to iTempRec
Send Clear to hoDDO
:
Get Main_File of hoDDO to iFile
Send Find_by_Recnum of hoDDO iFile iTempRec

If your database uses a primary key as its unique identifier, you can use other commands and Data Dictionary messages to identify and find records.

// Low level commands using primary key
String sTempId
Move Customer.Customer_Id to sTempId
Clear Customer
:
Move sTempId to Customer.Customer_Id
Find EQ Customer by 1  // find EQ by index 1

// Data Dictionary methods using primary key
String sTempId
Get Field_Current_Value of hoDDO Field Customer.Customer_Id to sTempId
Send Clear to hoDDO
:
Move sTempId to Customer.Customer_Id
Send Find of hoDDO EQ 1 // find EQ mode and index 1

The above example assumes that the Customer Table’s primary key is Customer.Customer_Id and its index is index 1. Each table will have a different definition for its primary key and primary key index. Depending on your table, you need to change the above code to support different primary key field names, different primary key indexes, and different primary key data types.

There is a major disadvantage with the above methods. A different syntax is required for different databases and, in some cases, different syntax for each table. This means it is very difficult to write abstracted code that can be applied to any table in any database. This kind of abstraction is a goal and requirement of Data Dictionary programming.

This is solved by introducing RowIds.

The record identity of every table is mapped to a special data type called RowId. You define the record-identity to RowId mapping when you define your table in the Studio's Table Editor. Once mapped, a set of RowId commands, functions, and Data Dictionary methods are used to identify and find records. This allows the same syntax to be used for any table.

// Low level commands using RowId
RowId riTempId
Boolean bFound
Move (GetRowId(Customer.File_Number)) to riTempId
Clear Customer
:
Move (FindByRowId(Customer.File_Number, riTempId)) to bFound

// Data Dictionary methods using RowId
RowId riTempId
Get CurrentRowId of hoDDO to riTempId
Send Clear to hoDDO
:
Send FindByRowId of hoDDO riTempId

By using this RowId syntax, you now have a single syntax that can be used with any table from any database. Once defined, you just program using RowId.

The RowId Data Type

RowId riTempId1 riTempId2
:
Property RowId priLastId
:
Get priLastId to riTempId1
Move riTempId1 to riTempId2
Set priLastId to riTempId2

A special data type named RowId is used to store RowId values. This data type has a restricted set of behaviors. Because the underlying data type can be any type or any combination of types, it cannot be cast to any other data type or directly used to perform any kind of evaluation. Instead, a set of RowId global functions and Data Dictionary methods are provided that allow you to perform all required RowId manipulations.

The RowId Global Functions

The following functions provide low-level RowId support:

Move (FindByRowId(iFile, riRowId)) to bFound

Move (GetRowId(iFile)) to riRowId

Move (NullRowId()) to riRowId

Move (IsNullRowId(riRowId)) to bIsNull

Move (IsSameRowId(riRowId1, riRowId2)) to bIsSame

Move (SerializeRowId(riRowId)) to sSerializedRowId

Move (DeSerializeRowId(sSerializedRowId)) to riRowId

These functions allow you to perform any needed RowId evaluation at a low level:

Function RunOrderDtlReport RowId riHdrId Returns RowId
RowId riEnd
Boolean bFound
Move (FindByRowId(OrderHea.File_Number, riHdrId)) to bFound
If bFound Begin
    Set priStartRowId to (NullRowId())
    Get DoRunReport to iStat
    Get priEndRowId to riEnd
End
Else Begin
    Move (NullRowId()) to riEnd
End
Function_Return riEnd
End_Function

The RowId Data Dictionary Interface

Data Dictionaries also provide a complete interface for working with RowIds.

Send FindByRowId of hoDDO iFile riRowId

Send ReadByRowId of hoDDO iFile riRowId

Get CurrentRowId of hoDDO to riRowId

Get HasRecord of hoDDO to riRowId

These methods, along with the global RowId functions, can be used to handle any type of RowId programming using Data Dictionaries.

Function RunOrderDtlReport RowId riHdrId Returns RowId
RowId riEnd
Boolean bFound
Integer iMain
Get Main_File of oOrderHea_DD to iMain
Send FindByRowId of oOrderHea_DD iMain riHdrId
Get HasRecord of oOrderHea_DD to bFound
If bFound Begin
    Set priStartRowId to (NullRowId())
    Get DoRunReport to iStat
    Get CurrentRowId of oOrderDtl_DD to riEnd
End
Else Begin
    Move (NullRowId()) to riEnd
End
Function_Return riEnd
End_Function

Special Notes

  • Do not confuse RowId with Primary Key. If your database supports recnum fields, your tables will still probably have a Primary Key (i.e., they will contain a field or set of fields that are uniquely indexed) which will be identified in your Data Dictionary class by setting the Key_Field_State property. That is the field that you will use in your relationships. While those fields could, in theory, be used to identify RowId, they won’t be because the Database’s internal definition of Recnum provides the fastest way to re-find a record. You always want to use the fastest method for finding records by record identity. Whatever method your database and your database driver supports that is fastest should be used.

  • RowId was added to DataFlex version 11.0. Prior to version 11.0, all tables needed to support a recnum (i.e., all tables needed to support a unique valued, numeric, single field). The introduction of RowId lifts this restriction. Recnum style programming is still supported. If a developer knows that all of their tables will contain a recnum, they can continue to program using the recnum commands and methods.

See Also