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 the 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 iTempRecnum 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 identify, 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 you your table you need to change the above code to support different Primary Key different field(s) 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 identify 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 roTempId1 to riTempId2

Set priLastId  to riTempId2

A special data-type 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:

FindByRowId

Move (FindByRowId(iFile,riRowId)) to bFound

GetRowId

Move (GetRowId(iFile)) to riRowId

NullRowId

Move (NullRowId()) to riRowId

IsNullRowId

Move (IsNullRowId(riRowId)) to bIsNull

IsSameRowId

Move (IsSameRowId(riRowId1,riRowId2)) to bIsSame

SerializeRowId

Move (SerializeRowId(riRowId)) to sSerializedRowId

DeSerializeRowId

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

Send FindByRowId of hoDDO iFile riRowId

Send ReadByRowId

Send ReadByRowId of hoDDO iFile riRowId

Get CurrentRowId

Get CurrentRowId of hoDDO to riRowId

Get HasRecord

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

See Also

Basic Data Dictionary and Table Concepts