DF_FIELD_LENGTH

The length of a field.

Level

Column

Supported by

DF_DATETIME type: The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 5.0 and higher
All other types: All Drivers

Type

Integer, permanent

Access

Read / Write

Values

1 – maximum number of bytes allowed for the field type.

Remarks

The effective length of a column varies by type: - For ASCII, Text, and Binary columns, this is the maximum number of characters/bytes. - For numeric columns, it represents the maximum number of digits (including digits after the decimal separator). - For date columns, the DF_FIELD_LENGTH attribute is a constant value.

If you wish to create a numeric field with digits after the decimal point, you must set the DF_FIELD_PRECISION attribute for that field after setting the DF_FIELD_LENGTH attribute. If you wish to create a numeric field without digits after the decimal point, it is not necessary to set the DF_FIELD_PRECISION attribute for that field.

Example

The following example code creates a new numeric column with a length of 14.2.

Move 0 To iColumn
Create_Field At iColumn
Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 16
Set_Attribute DF_FIELD_PRECISION Of hTable iColumn To 2

For all drivers, this is a permanent attribute that can only be set inside a Structure_Start...Structure_End operation. For the non-Embedded Database drivers, the permanent value of this attribute can be stored in the intermediate file using the Field_Length keyword.

In the Embedded Database, the DF_FIELD_LENGTH attribute of date columns will always be 6. Dates are stored as Julian integer values in a 3-byte integer. Numeric columns are stored as binary-coded decimal values where each nibble uses four bits. Since the length of a column always spans complete bytes, the length of a numeric column is always an even value. The length of Binary and Text columns includes two bytes that are used to hold the length of the value of the column. Therefore, the maximum number of bytes of actual data that can be placed in these fields is DF_FIELD_LENGTH minus 2.

In the DataFlex SQL Drivers, the DF_FIELD_LENGTH attribute of Date and DateTime columns is a constant. The value of that constant depends on the actual SQL type on the back end.

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 16 KB; for SQL Server, it is 2 GB. When opening a table in such a back end, it usually reports the column length as 2 GB. When using the DataFlex SQL Drivers, the size of such columns will be truncated at 16 KB 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 these 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 AddLastNameColumn Handle hTable
    Integer iColumn
    Open hTable Mode DF_EXCLUSIVE
    Structure_Start hTable
        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "LastName"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_ASCII
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 50
    Structure_End hTable
End_Procedure

This example adds a field named LastName, of type ASCII and length 50 to the end (as the last field) of the table.

Procedure AddAmountColumn Handle hTable
    Integer iColumn
    Open hTable Mode DF_EXCLUSIVE
    Structure_Start hTable
        Move 0 To iColumn
        Create_Field hTable At iColumn
        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "Amount"
        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 8
        Set_Attribute DF_FIELD_PRECISION Of hTable iColumn To 2
    Structure_End hTable
End_Procedure

This example adds a field named Amount, of type BCD (Numeric) and length 8, with 6 digits before and 2 digits following the decimal point to the end (as the last field) of the table.

Procedure CreateTable
    Handle hTable hoWorkspace
    String sPath sOrigFolder
    Integer iColumn 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.