DF_FILE_PRIMARY_INDEX

Specifies the index number of the primary index – the index that uniquely identifies a record.

Level

Table

Supported by

All Drivers

Type

Integer, permanent

Access

Read / Write

Values

0 ~ maximum number of indexes allowed by the database

Remarks

The primary index of a table is the number of the index that uniquely identifies a record in that table. The DataFlex database API demands that every table has at least one such index. This index is used to keep a pointer to a record. The primary index concept was introduced in newer drivers along with related concepts as standard tables and RowIds.

The new drivers have a conformance level of 1. Older drivers have a conformance level of 0 and do not support the DF_FILE_PRIMARY_INDEX attribute. The conformance level of a driver can be queried by using the DF_DRIVER_CONFORMANCE attribute.

Primary indexes apply to both recnum and standard tables. In a recnum table, the primary index will often be 0 (zero). In a standard table, the segments of the primary index will comprise the fields that uniquely identify a record. Most often, these fields will be your primary key.

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.

Function DriverIndex String sDriver Returns Integer
    String sCurrentDriver
    Integer iNumberOfDrivers
    Integer iDriver
    Integer iCount
    Move 0 To iDriver
    Get_Attribute DF_NUMBER_DRIVERS To iNumberOfDrivers
    For iCount From 1 To iNumberOfDrivers
        Get_Attribute DF_DRIVER_NAME Of iCount To sCurrentDriver
        If (Uppercase(sCurrentDriver)) Eq (Uppercase(sDriver)) ;
            Move iCount To iDriver
    Loop
    Function_return iDriver
End_Function // DriverIndex

Procedure ShowPrimaryIndex
    Handle hTable
    String sTable
    String sDriver
    Integer iDriver
    Integer iConformance
    Integer iPrimaryIndex
    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_DRIVER Of hTable To sDriver
            Get DriverIndex sDriver To iDriver
            Get_Attribute DF_DRIVER_CONFORMANCE Of iDriver To iConformance
            If (iConformance >= 1) ;
                Get_Attribute DF_FILE_PRIMARY_INDEX Of hTable To iPrimaryIndex
            Else ;
                Move 0 To iPrimaryIndex
            Showln sTable " -- " iPrimaryIndex
            Close hTable
        End
    Until (hTable = 0)
End_Procedure // ShowPrimaryIndex

The sample procedure above shows the primary index of every table in the file list. If the table is accessed through a conformance 0 driver, the primary index is set to 0 (zero).

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 "Clock.int"
        Set_Attribute DF_FILE_RECNUM_TABLE Of hTable To False
        Set_Attribute DF_FILE_LOGIN Of hTable To "SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind"
        Set_Attribute DF_FILE_TABLE_NAME Of hTable To "Clock"
        Set_Attribute DF_FILE_USE_DUMMY_ZERO_DATE Of hTable To True
        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_FILE_PRIMARY_INDEX Of hTable To iIndex
        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 standard table called Clock with one unique index that is set to be the primary index.