Skip to content

DF_FIELD_DEFAULT_VALUE

The default value of a column.

Level

Column

Supported by

All Drivers, The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 6 or higher for [newid()] or [newsequentialid()].

Type

String, temporary

Access

Read / Write

Values

A string containing the default value of the column or [newid()] or [newsequentialid()].

Syntax

Use cli.pkg
Get_Attribute DF_FIELD_DEFAULT_VALUE of {tableNumber} {columnNumber} to {StringVariable}
Set_Attribute DF_FIELD_DEFAULT_VALUE of {tableNumber} {columnNumber} to {StringVariable}

Remarks

This attribute sets up the database default value for a column. You can set up SQL Server defaults on a column in three different formats: literal, ODBC escape sequence, or back-end string.

  • A literal default is set by using a string in single quotes. It should be a valid literal value for the SQL type of the column.
  • An ODBC escape sequence is enclosed in curly brackets {}.
  • A back-end string is enclosed in square brackets [].

Typical examples of setting default values are:

Set_Attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyCharField To "'Unknown'"
Set_Attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyDateField To "{fn current_date()}"
Set_Attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyCharField To "[convert(char(30), CURRENT_USER)]"

The default will be used when creating records. For example, if we have a table MyTable with columns A, B, and C, using the code below will result in a new row in MyTable having default values (if any) for columns B and C.

Clear MyTable
Begin_Transaction
Move "Not a default value" To MyTable.A
Saverecord MyTable
End_Transaction

See NULL Values and Defaults for more information.

GUID Columns

GUIDs are only supported with the DataFlex SQL Driver revision 6 or higher.

A GUID (Globally Unique Identifier) or UUID (Universally Unique Identifier) is a randomly assigned value that is (almost) guaranteed to be universally unique. Typically, it looks something like this:

60FCEA2E-CFFA-4788-AB32-6B81F37D3FFA

In SQL, this type is called a uniqueidentifier. Normally, this value is assigned automatically, either by the client (i.e., your code) or on the server. You can create as many GUID columns in a table as you wish. When used, this is often used as your primary key (PK) column value.

To create a GUID PK whose value is client-assigned, do the following:

  1. Create a GUID column (uniqueidentifier). This column will probably be your PK column as well.
  2. Add the following code to your DD Class to assign the GUID during creation:
Use WinUUID.pkg // add to top of DD Class
:
// augment Creating as follows
Procedure Creating
    Forward Send Creating
    Move (RandomHexUUID()) to myTable.myUUID // whatever your table column name is
End_Procedure

You can also have the server do this assignment for you. You do this by setting the field's DF_FIELD_DEFAULT_VALUE to [newid()] or [newsequentialid()] as explained below:

  1. Create a GUID column (uniqueidentifier). This column will probably be your PK column as well.
  2. Set the DF_FIELD_DEFAULT_VALUE to [newid()] or [newsequentialid()].

Note that these expressions are input with surrounding brackets []. You must do this. It tells the driver that the value enclosed is not a string literal. After entering the brackets, they will not appear in the Properties Panel, but the server will be properly updated.

After a new record is saved, the new GUID value will be set in your file buffer.

The use of newid() vs. newsequentialid() in MS SQL is technical. When GUIDs are used as the primary clustered index, the random assignment of GUIDs with newid() does not result in a well-balanced tree. Using newsequentialid() creates GUIDs that are better-balanced.