DF_FILE_RECORD_IDENTITY

Level

Table

Supported by

All Drivers

Type

Numeric, permanent

Access

Read / Write

Values

0 ~ maximum number of fields allowed in a file of the database system

Remarks

This attribute identifies the field that is to function as the equivalent of RECNUM in each file of any database system that does not support or use record numbers the way DataFlex does. This can be an existing field. It must, at all times, have a unique value for each record. Once a field has been designated by this attribute, its index (whatever its actual number) may be addressed as Index 0.

Note that there must be an index defined on the record identity column. That index should have one segment, the record identity column. Record identity columns must be numeric with 0 (zero) precision (no decimals).

This attribute can only be set inside a Structure_Start ... Structure_End operation. The DataFlex SQL Drivers store the value of the attribute in the intermediate file under the Primary_Index keyword. The DataFlex Pervasive.SQL Driver stores the type of record identity column in the intermediate file under the Recnum_Support keyword.

For the Embedded Database, this attribute will always have a value of (field) zero. If you attempt to set it to any value other than zero for such a table, you will receive an error. Both the record identity column and its index are logical objects in the table.

For the DataFlex Pervasive.SQL Driver, this attribute will always have a value of (field) zero. The Pervasive.SQL Driver supports two types of recnum columns: one logical column and one physical column. The logical column is a handle to the record. The physical column is a hidden column, called recnum, that is placed at the beginning of the record. This column can be created by the conversion process to preserve the original record numbers. You only need to convert in this way if the recnum column of the original table contains meaningful information.

For the DataFlex SQL Drivers, this attribute can have a non-zero value. The attribute is only meaningful when the table is a recnum table. For standard tables, DF_FILE_PRIMARY_INDEX should be used to determine the identifying columns. A record identity with value 0 (zero) means that the recnum column will be hidden; although there is a physical column, it will not be reported by the driver. This will be the case with most recnum tables accessed through a SQL Database Driver.

Note that when the record identity is an existing visible column, there are two ways to address that column. If, for example, a table has a column called CustomerNumber that is also used as a record identifier, you can address the column in the following ways:

Move Table.CustomerNumber To iVar
Move Table.Recnum To iVar
Move iVar To Table.CustomerNumber
Move iVar To Table.Recnum

Of the four commands above, the last one is special. Moving a value to Recnum will inactivate the record buffer (the DF_FILE_STATUS attribute is set to DF_FILE_INACTIVE). If a buffer is inactive, a save operation on that buffer will create a new record.

Procedure ShowRecordIdentifiers
    Handle hTable
    String sTable
    Boolean bRecnumTable
    Integer iIndex
    String sKey
    Integer iNumSegments
    Integer iSegment
    Integer iColumn
    String sColumn

    Move 0 To hTable
    Repeat
        Get_Attribute DF_FILE_NEXT_USED Of hTable To hTable
        If (hTable > 0) Begin
            Open hTable
            Get_Attribute DF_FILE_LOGICAL_NAME Of hTable To sTable
            Get_Attribute DF_FILE_RECNUM_TABLE Of hTable To bRecnumTable
            If (bRecnumTable) Begin
                Get_Attribute DF_FILE_RECORD_IDENTITY Of hTable To iColumn
                Get_Attribute DF_FIELD_NAME of hTable iColumn To sColumn
                Move ("(" - sColumn - ")") To sKey
            End
            Else Begin
                Get_Attribute DF_FILE_PRIMARY_INDEX Of hTable To iIndex
                Get_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To iNumSegments
                Move "(" To sKey
                For iSegment From 1 To iNumSegments
                    Get_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex iSegment To iColumn
                    Get_Attribute DF_FIELD_NAME of hTable iColumn To sColumn
                    If (iSegment > 1) ;
                        Move (sKey - ", ") To sKey
                    Move (sKey + sColumn) To sKey
                Loop
            End
            Showln sTable " -- " sKey
            Close hTable
        End
    Until (hTable = 0)
End_Procedure // ShowRecordIdentifiers

The sample procedure above shows all tables in the file list with their identifying columns.

Procedure CreateClockTable
    Handle hTable
    Handle hoWorkspace
    String sPath
    String sOrigFolder
    Integer iColumn
    Integer iIndex

    //*** Make sure int file comes in first folder of datapath by
    //*** making that folder current
    Get phoWorkspace Of ghoApplication To hoWorkspace
    Get psDataPath Of hoWorkspace To sPath
    Get PathAtIndex Of hoWorkspace sPath 1 To sPath
    Get_Current_Directory To sOrigFolder
    Set_Directory sPath

    //*** Create a SQL Server table to store clock in/out times
    //*** of employees
    Move 0 To hTable
    Structure_Start hTable "MSSQLDRV"
        Set_Attribute DF_FILE_PHYSICAL_NAME Of hTable To "RNClock.int"
        Set_Attribute DF_FILE_RECNUM_TABLE Of hTable To True
        Set_Attribute DF_FILE_LOGIN Of hTable To ;
            "SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind"
        Set_Attribute DF_FILE_TABLE_NAME Of hTable To "RNClock"
        Set_Attribute DF_FILE_USE_DUMMY_ZERO_DATE Of hTable To True
        Set_Attribute DF_FILE_RECORD_IDENTITY Of hTable To 0
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "EmployeeID"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 10
        Set_Attribute DF_FIELD_NATIVE_TYPE Of hTable iColumn To SQL_INTEGER
        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ClockInTime"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_DATE
        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ClockOutTime"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_DATE
        Move 0 To iIndex
        Create_Index hTable At iIndex
        Set_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To 3
        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 1 To 1
        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 2 To 2
        Set_Attribute DF_INDEX_SEGMENT_DIRECTION Of hTable iIndex 2 To DF_DESCENDING
        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 3 To 3
        Set_Attribute DF_INDEX_NAME Of hTable iIndex To "ClockPK"
    Structure_End hTable

    //*** Reset current working folder to original value
    Set_Directory sOrigFolder

    //*** Add to file list and generate fd
    Move 0 To hTable
    Get_Attribute DF_FILE_NEXT_EMPTY Of hTable To hTable
    If (hTable > 0) Begin
        Set_Attribute DF_FILE_ROOT_NAME Of hTable To "MSSQLDRV:Clock"
        Set_Attribute DF_FILE_DISPLAY_NAME Of hTable To "Clock sample table"
        Set_Attribute DF_FILE_LOGICAL_NAME Of hTable To "Clock"
        Open hTable
        Get psDDSRCPath Of hoWorkspace To sPath
        Get PathAtIndex Of hoWorkspace sPath 1 To sPath
        If (Right(sPath, 1) <> Sysconf(Sysconf_Dir_Separator)) ;
            Move (sPath - Sysconf(Sysconf_Dir_Separator)) To sPath
        Move (sPath - "Clock.fd") To sPath
        Output_Aux_File DF_AUX_FILE_FD For hTable To sPath
        Close hTable
    End
End_Procedure // CreateClockTable

The sample procedure above creates a SQL Server recnum table in the Northwind database. This table has a hidden recnum column.