Skip to content

Default Values

SQL servers allow column definitions to include a default value. This default is used when records are created and no value for the column has been defined. SQL defaults are different from DataFlex data dictionary default values. The DataFlex default values, as implemented in the data dictionary class, will be used at data entry time. While the user is entering data, the default is displayed on screen. SQL defaults are used at save time. Data entry has been completed; the user will not see the default value until the record in question is reselected. Both approaches have their merits. The data entry time approach has the advantage of direct feedback, while the save time approach has the advantage of data integrity guarantees.

You can set up SQL defaults on a column in three different formats: literal, ODBC escape sequence, or back-end string.

Since defaults are used when creating records and no value for the column has been specified, they are usually set to a value that indicates the data is unknown or to a function that generates the desired information. Defaults are also used to ensure that columns that do not allow null values always have a valid value.

Default Value Formats

Literal Default

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. Literal values must be enclosed in single quotes. You can use any literal value that is legal in SQL Server for the type and length of the column.

ODBC Escape Sequence

ODBC escape sequences must be enclosed in curly brackets. The main reason to use ODBC escape sequences rather than back-end strings is that ODBC escape sequences are supported by other drivers. ODBC defines escape sequences for a number of language elements. Two escape sequence types can be used when setting up default values:

  • Date, time, timestamp, and datetime interval literals
  • Scalar functions

For a detailed discussion of ODBC escape sequences, see ODBC Escape Sequences.

Back-end String

Back-end strings are used for any value that is not a literal in quotes and not an ODBC escape sequence. Usually, they are used to set up a default scalar function.

Default Value Configuration

The default value of a column depends on the table definition. This definition can be adjusted by using the DF_FIELD_DEFAULT_VALUE attribute. When converting, a configuration file is used that sets up defaults per type to convert. These defaults can be adjusted in the driver configuration file.

Typical Examples

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. So if we were to have a table MyTable with columns A, B, and C, and use the code below, we would end up with a new row in MyTable having default values (if any) for columns B and C.

Clear MyTable
Lock
Move "Not a default value" To MyTable.A
SaveRecord MyTable
Unlock

See Also