iAnywhere.com - UltraLite database options

UltraLite database options

UltraLite databases support the following set of database options, which must be set in the schema file. One set of options controls the handling of dates and times. A second set controls the default handling of arithmetic operations.

Setting database options

For UltraLite components, the database options must be set in the schema file.

If you are using the Schema Painter, set the options in the database property sheet.
If you are using the ulinit utility, set the options in your Adaptive Server Anywhere reference database.
If you are using the ulxml utility, set the options in the XML document that describes the database schema.

For UltraLite static interfaces, set the options in your Adaptive Server Anywhere reference database.

For information about setting Adaptive Server Anywhere database options, see Setting options.

Date and time options

The following database options control the default handling of dates and times. These settings can be changed within SQL operations by using functions such as the DATEFORMAT function.

DateFormat Sets the default string format in which dates are retrieved from the database.
Allowed values are constructed from the symbols listed in the table that follows this list. The default value is YYYY-MM-DD.

The corresponding Adaptive Server Anywhere database option is DATE_FORMAT. See DATE_FORMAT option [compatibility].
DateOrder Sets the default interpretation of dates when submitted to the database.
Allowed values are MDY, YMD, DMY. The default value is YMD.

The corresponding Adaptive Server Anywhere database option is DATE_ORDER. See DATE_ORDER option [compatibility].
NearestCentury Sets the interpretation of two-integer year values when submitted to the database.
Allowed values are integers from 0 to 100 inclusive. The default value is 50. Two digit years YY less than the value are converted to 20YY, while years greater than or equal to the value are converted to 19YY.

The corresponding Adaptive Server Anywhere database option is NEAREST_CENTURY. See NEAREST_CENTURY option [compatibility].
TimeFormat Sets the default format for times retrieved from the database.
Allowed values are constructed from the symbols listed in the table that follows this list. The default value is HH:NN:SS.SSS.

The corresponding Adaptive Server Anywhere database option is TIME_FORMAT. See TIME_FORMAT option [compatibility].
TimestampFormat Sets the default format for timestamp values retrieved from the database.
Allowed values are constructed from the symbols listed in the table that follows this list. The default value is YYYY-MM-DD HH:NN:ss.SSS.

The corresponding Adaptive Server Anywhere database option is TIMESTAMP_FORMAT. See TIMESTAMP_FORMAT option [compatibility].
TimestampIncrement As timestamps are inserted into the database, UltraLite truncates them to match this increment. This value is useful when a DEFAULT TIMESTAMP column is being used as a primary key or row identifier. In particular, during synchronization it can be important that timestamps match, and different supported consolidated databases maintain timestamps to different resolution. Setting the TimestampIncrement to match that of the consolidated database can help to avoid spurious inequalities.
Allowed values are integers greater than zero. The default value is 1.

The corresponding Adaptive Server Anywhere database option is TRUNCATE_TIMESTAMP_VALUES. See TRUNCATE_TIMESTAMP_VALUES option [database].

The symbols used in DateFormat, TimeFormat, and TimestampFormat values are taken from the following table:

Symbol	Description
yy	Two digit year.
yyyy	Four digit year.
mm	Two digit month, or two digit minutes if following a colon (as in hh:mm).
mmm[m...]	Character short form for months—as many characters as there are "m"s. An upper case M causes the output to be made upper case.
d	Single digit day of week, (0 = Sunday, 6 = Saturday).
dd	Two digit day of month. A leading zero is not required.
ddd[d...]	Character short form for day of the week. An upper case D causes the output to be made upper case.
hh	Two digit hours. A leading zero is not required.
nn	Two digit minutes. A leading zero is not required.
ss[.ss..]	Seconds and parts of a second.
aa	Indicate AM or PM (12 hour clock).
pp	Indicate times after noon by PM (12 hour clock).
jjj	Day of the year, from 1 to 366.

Arithmetical operations

The Precision and Scale options control the handling of arithmetical operations.

Precision Sets the maximum number of digits in the result of any decimal arithmetic.
Allowed values are integers between 0 and 127 inclusive. The default value is 30.

Precision is the total number of digits to the left and right of the decimal point. The Scale option specifies the minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum Precision.
Scale Sets the minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum Precision.
Allowed values are integers between 0 and 127 inclusive. The default value is 6.

For example, when a DECIMAL(8,2) value is multiplied by a DECIMAL(9,2) value, the result could require a DECIMAL(17,4). If Precision is 15, only 15 digits are kept in the result. If Scale is 4, the result is a DECIMAL(15,4). If Scale is 2, the result is a DECIMAL(15,2). In both cases, there is a possibility of overflow.