Dataset ID storage

As already announced in the article Version 5.8 Part 1 RecID unlimited, this article presents solutions for the adaptation of dataet IDs when these are saved as a reference to a target dataset.

Background

M64-bit dataset IDs were introduced with version 5.8. As a result, the IDs of datasets differ between versions 5.7 and 5.8. This article describes what needs to be considered when switching to version 5.8 if an application persistently stores dataset IDs in order to reference data.
Example

With version 5.7, a table is created and then a dataset is inserted. The dataset is then read and the dataset ID (decimal and hexadecimal) is output in the debugger:

@A+
@C+

main
{
  RecRead(100,1,0);
  DbgTrace('Version: ' + CnvAI(DbaInfo(_DbaClnRelMaj)) + '.' +
                         CnvAI(DbaInfo(_DbaClnRelMin)) +
           ' - Datensatz-ID: ' + CnvAI(RecInfo(100,_RecID)) +
           ' / 0x' + CnvAI(RecInfo(100,_RecID),_FmtNumHex | _FmtNumLeadZero,0,8));
}

The following line presents the result:

Version: 5.7 - Datensatz-ID: 16.777.216 / 0x01000000

When executed with version 5.8, the following result is obtained:

Version: 5.8 - Datensatz-ID: 1 / 0x00000001

The determined dataset ID therefore differs between the versions. As the dataset ID is only used temporarily in most cases, this is not a problem. However, the situation is different if the dataset ID is saved as a field in the dataset, for example, in order to reference a specific dataset in another table (Fig. 1).

Fig.1: Referencing a dataset via its dataset ID

In the example, the dataset ID is contained as a field in the source table. An A via the saved dataset ID in the source table (e.g. RecID1) to determine the dataset in the target table would come to nothing with version 5.8, as its dataset ID is now a different one (red dashed arrow).

Solution approach

The approach is to calculate a dataset ID (version 5.8) from the dataset ID (version 5.7). This would turn the value 0x01000000 back into 0x00000001, 0x02000000 back into 0x00000002 and so on. The reference would be restored in this way. The following code shows a function for calculating the dataset ID from an int value:

// Converts a RecID that is stored in an int value
sub ConvertSingleRecIDInt
(
  aRecID                : int;   // Dataset ID (Version 5.7)
  aTarTblIsSequential   : logic; // Target file (to which the RecIDs refer)
                                 // has "sequential insertion" activated?
)
: int;                           // Dataset ID (version 5.8)
{
  if (aTarTblIsSequential)
  {
    if (aRecID & 0x80 != 0)
      ErrSet(_ErrValueOverflow);
    else
      return (
          (aRecID & 0xFF000000) >> 24 & 0xFF |
          (aRecID & 0x00FF0000) >> 8         |
          (aRecID & 0x0000FF00) << 8         |
          (aRecID & 0x000000FF) << 24
      );
  }
  else
  {
    if (aRecID > 0)
      ErrSet(_ErrValueOverflow);
    else
      return -aRecID;
  }
}

The function converts a dataset ID version 5.7 (1st argument) into a dataset ID version 5.8 (return value). Depending on whether the “Sequential insert” option is set for the target table or not, the database server assigns the dataset IDs in a different order. The calculation then also differs. If “Sequential insert” is set for the table, the function is passed true in the second argument, otherwise false. The function sets the global error code _ErrValueOverflow if the resulting dataset ID is >= 0x80000000, as these cannot be mapped in the value range of int. The function should therefore only be used if the prime counter of the target file is < 0x80000000. If it is greater, the following function should be used:

// Converts a RecID that is stored in a bigint value
sub ConvertSingleRecIDBigInt
(
  aRecID                : bigint; // Dataset ID (Version 5.7)
  aTarTblIsSequential   : logic;  // Target file (to which the RecIDs refer)
                                  // has "sequential insertion" activated?
)
: bigint;                         // Dataset ID (Version 5.8)
{
  if (aTarTblIsSequential)
  {
    return (
        (aRecID & 0x00000000FF000000\b) >> 24\b |
        (aRecID & 0x0000000000FF0000\b) >> 8\b  |
        (aRecID & 0x000000000000FF00\b) << 8\b  |
        (aRecID & 0x00000000000000FF\b) << 24\b)
    ;
  }
  else
  {
    if (aRecID < 0\b)
      return -aRecID;

    return (0x100000000\b - aRecID);
  }
}

As the return value of the function is bigint, all dataset IDs to be converted can be displayed. However, this also means that a bigint field must now be provided for storing the dataset ID.

The ConvertRecID58 function

If the dataset ID is stored in a database field, calculating the dataset ID alone is not sufficient. The original dataset ID must also be replaced by the new value:

• On-demand
  The dataset ID is replaced the first time it is required. This has the advantage that not all saved dataset IDs need to be replaced, but only those that are actually referenced. The disadvantage is that all places in the source code where the original dataset ID is accessed must be adapted. Furthermore, an additional flag would be required in the dataset to indicate whether the dataset ID has already been converted or not.
• All datasets
  The dataset IDs of all datasets are calculated and changed in the dataset. The advantage here is that no changes need to be made to the source code if the same database field is used for the new dataset ID. However, a separate run must be made that reads all datasets once, enters the new dataset ID in the database field and then updates the dataset in the database.

The ConvertRecID58 function can be used for the practical implementation of the second method:

sub ConvertRecID58
(
  aTblNo                : int;          // Table number in which the RecIDs are located
  aSbrNoSrc             : int;          // Partial dataset number in aTblNo (conversion source)
  aFldNoSrc             : int;          // Field number in aSbrNo (conversion source)
  aTarTblIsSequential   : logic;        // Target file (to which the RecIDs refer) has "sequential insertion" activated?
  opt aSbrNoTar         : int;          // Partial dataset number in aTblNo (conversion target). If not specified, aSbrNoSrc is used.
  opt aFldNoTar         : int;          // Field number in aTblNo (conversion target). If not specified, aSbrNoSrc is used.
)

The function reads all datasets in the file aTblNo one after the other and updates the datasets with the newly calculated dataset ID. The field that stores the dataset ID (version 5.7) is specified via the arguments aTblNo, aSbrNoSrc (partial dataset no.) and aFldNoSrc (field no.). It must have the data type int or bigint. If nothing else is specified, the dataset ID is saved in the same field. If the original dataset ID is to be retained, aSbrNoTar and aFldNoTar can be used to specify a different database field in the same dataset.

Example database

You can use the sample database at the end of the blog article to test the function described. The database contains famous movie pairs. Instructions on how to use the example can be found in the ReadMe.rtf document, which is also included in the archive.