Skip to content

DF_FIELD_TYPE

See Also: Get_Attribute, Set_Attribute, DF_FIELD_NATIVE_TYPE, Fundamental Data Types

The data type of the column.

Level

Column

Supported by

  • DF_DATETIME type: The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 5.0 and higher.

See the full list of supported types and type mappings for Microsoft SQL Server here.

See the full list of supported types and type mappings for IBM DB2 here.

  • All other types: All Drivers

Type

Enumeration list, permanent

Access

Read / Write

Values

  • DF_ASCII
  • DF_BCD
  • DF_DATE
  • DF_DATETIME
  • DF_OVERLAP*
  • DF_TEXT
  • DF_BINARY

Remarks

The data type of the column. The list of supported types is limited to the original Embedded Database types. Most non-embedded databases support a larger list of types. The DataFlex Database API and runtime directly support the types listed here. Drivers will project the back-end type onto one of the supported types. An integer column, for example, will be projected onto a DF_BCD column. The DataFlex SQL Drivers allow fine-tuning the back-end type via the column attributes DF_FIELD_NATIVE_TYPE and DF_FIELD_NATIVE_TYPE_NAME.

Do not set both DF_FIELD_NATIVE_TYPE and DF_FIELD_TYPE attribute for the same column. If you set DF_FIELD_TYPE, the driver will determine the best fitting DF_FIELD_NATIVE_TYPE. If you set DF_FIELD_NATIVE_TYPE, the driver will determine the best fitting DF_FIELD_TYPE.

This attribute can only be set inside of a Structure_Start ... Structure_End operation.

The meaning of the different values is:

Enum value Short Description
DF_ASCII ASCII ASCII columns may contain any characters and are used when numeric formatting will not be required.
DF_BCD Numeric Numeric columns may contain only the numbers 0 through 9, a period, and a plus or minus sign.
DF_DATE Date Dates are input and output as they appear (month/day/year, etc.), but in most database systems, such as the embedded database, they are stored in a different way. The Embedded Database stores dates as Julian integer values representing the number of days since the first day of Year 1.
DF_DATETIME DateTime The DateTime type corresponds to an SQL TIMESTAMP_STRUCT as defined in ODBC. DateTime types are used for declaring variables for storing date and time values, with an accuracy down to milliseconds.
DF_OVERLAP Overlap Obsolete: Overlap columns were replaced by multi-segment relationships in DataFlex 11.0 and are obsolete. Logical columns defined in terms of other physical columns, which must be contiguous. They do not take up space in the table, as they are "virtual" columns. For Overlap columns, you must specify the start position of the column by specifying the offset (DF_FIELD_OFFSET) and the length of the column is defined by specifying the length (DF_FIELD_LENGTH) of the column. The support of multi-segment relationships in VDF11 makes the use of overlap columns superfluous.
DF_TEXT Text Text columns (often called "memo" columns) are for storing large amounts of text in ASCII format.
DF_BINARY Binary Binary columns are used to store any characters, but in a binary format. Binary columns are used primarily for storing graphics as bit images.

DF_TEXT and DF_BINARY in the Embedded Database

Note that for the Embedded Database driver, the usable size of a DF_TEXT or DF_BINARY column will be two bytes less than the declared length. Thus, if you declare a text column with a size of 256 bytes, you will be able to store up to 254 characters in this column. The amount of space occupied on the disk is the actual length of the contents, if any, plus 2 bytes, unless compression is not turned on. In that event, the amount of space occupied will be the declared length of the column, regardless of whether the column has any content.

DF_TEXT and DF_BINARY column length in DataFlex SQL Drivers

The maximum size of text and binary columns varies greatly between different back ends. For the Embedded Database, it is 16KB; for SQL Server, it is 2GB. When opening a table in such a back end, it usually reports the column length as 2GB. When connecting to a table through an SQL Database Driver, the size of such columns will be truncated at 16KB by default. You can increase this size by editing the Field_Length intermediate file keyword or setting the DF_FIELD_LENGTH attribute to the desired length when creating the column. Note that binary columns are part of a table’s buffer and the size you specify for it will be allocated in memory when the table is opened.

Procedure ShowTypes Handle hTable
    Integer iNumColumns
    Integer iColumn
    Integer iType
    String sTable
    String sColumn

    Get_Attribute DF_FILE_LOGICAL_NAME Of hTable To sTable
    Showln "Types for table " sTable ":"
    Get_Attribute DF_FILE_NUMBER_FIELDS Of hTable To iNumColumns

    For iColumn From 1 To iNumColumns
        Get_Attribute DF_FIELD_NAME Of hTable iColumn To sColumn
        Get_Attribute DF_FIELD_TYPE Of hTable iColumn To iType
        Show "    " sColumn
        Case Begin
            Case (iType = DF_ASCII)
                Showln " - Ascii"
            Case Break
            Case (iType = DF_BCD)
                Showln " - Numeric"
            Case Break
            Case (iType = DF_DATE)
                Showln " - Date"
            Case Break
            Case (iType = DF_OVERLAP)
                Showln " - Overlap"
            Case Break
            Case (iType = DF_TEXT)
                Showln " - Text"
            Case Break
            Case (iType = DF_BINARY)
                Showln " - Binary"
            Case Break
        Case End
    Loop
End_Procedure

The sample procedure above shows the type for all columns in the passed table.

Procedure AddTurnover
    Handle hTable
    Integer iColumn

    Open Customer Mode DF_EXCLUSIVE
    Move Customer.File_number To hTable
    Structure_Start hTable
        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "TurnOver"
        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_PRECISION Of hTable iColumn To 2
    Structure_End hTable
End_Procedure

The sample procedure above adds a numeric (8.2) column called TurnOver to the Customer table.

Procedure CreateTable
    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 "RIMSample.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 "RIMSample"
        Set_Attribute DF_FILE_GENERATE_RECORD_ID_METHOD Of hTable To RIM_IDENTITY_COLUMN
        Set_Attribute DF_FILE_PRIMARY_INDEX Of hTable To 0

        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ID"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 4

        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "Name"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_ASCII
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 50

        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "Comment"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_TEXT
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To (5 * (2^20))

        Move 0 To iIndex
        Create_Index hTable At iIndex
        Set_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To 1
        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 1 To 1
        Set_Attribute DF_INDEX_NAME Of hTable iIndex To "RimSample001"
    Structure_End hTable

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

    //*** Add to filelist 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:RIMSample"
        Set_Attribute DF_FILE_DISPLAY_NAME Of hTable To "RIM sample table"
        Set_Attribute DF_FILE_LOGICAL_NAME Of hTable To "RIMSamp"
        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 - "RIMSamp.fd") To sPath
        Output_Aux_File DF_AUX_FILE_FD For hTable To sPath
        Close hTable
    End
End_Procedure

This example creates a SQL Server table with a text column of 5 megabytes.