# CHAPTER 3: Data Types

## 3.1 Introduction

In Empress, all attributes are declared to hold data of specific data types. These are:

Table 3-1: Empress Data Types

Type | Declaration
Character | `CHAR` or `CHARACTER` (length, type) `TEXT` (print length, primary storage, overflow storage, # of direct pointers to extents) `NLSCHAR` or `NLSCHARACTER` (length, type) `NLSTEXT` (print length, primary storage, overflow storage, # of direct pointers to extents)
Date | `DATE` (format) `TIME` (format) `MICROTIMESTAMP` (format)
Decimal | `DECIMAL` or `DEC` (maximum digits, number of decimal places) `DOLLAR` (maximum dollar digits, format)
Float | `REAL` `FLOAT` (number of decimal places) `LONGFLOAT` or `DOUBLE PRECISION`
Integer | `INTEGER` or `INT` `LONGINTEGER` `SHORTINTEGER` or `SMALLINT`
User Interpreted (Byte Stream) | `MULTIMEDIA` or `BULK` (# of bytes printed, primary storage, overflow storage, # of direct pointers to extents)

All the data types listed above are subject to explicit or implicit parameters such as maximum length, display format, etc. These data types are sometimes referred to as user data types. If no value is specified for their parameters, default values are used. These defaults are covered in the following sections.

Empress also has the concept of generic data types that are not subject to parameters. For instance, a generic integer is a sequence of digits. Generic values result from evaluating expressions, since the result of an expression is not naturally associated with any attribute. For instance, the expression (`loans.amount * 0.2`) yields a generic decimal value. Width controls are provided for the display of generic values and are described at the end of this chapter.

## 3.2 Data Type Ranges

The following tables list the ranges - minimum and maximum values - for the different Empress data types. The first table lists the ranges for Empress parametric data - values associated with attributes. The second table lists the ranges for Empress generic data - constants or the results of expressions.

### 3.2.1 Parametric Data Types

The parametric data types are as follows:

Table 3-2: Parametric Data Types

Type | Range
`CHAR` | 1 to maximum value of C int, typically 231-1 bytes
`NLSCHAR` | 1 to maximum value of C int, typically 231-1 bytes
`TEXT` | 1 to maximum value of C int, typically 231-1 bytes
`NLSTEXT` | 1 to maximum value of C int, typically 231-1 bytes
`DATE` | Using the format `yyyymmdd`, it is 00000101 to 99991231(January 1, 0000 to December 31, 9999)
`TIME` | January 1, 1970 00:00:00 to December 31, 2035 23:59:59
`MICROTIMESTAMP` | January 1, 0000 00:00:00.000000 to December 31, 9999 23:59:59.999999
`DECIMAL` | 1 to 38 digits
`DOLLAR` | 1 to 13 digits
`REAL` | System dependent - 4-byte single precision float.
`FLOAT` | System dependent - If the number of decimal places is between 1 and 6, it is 4-byte single precision float; if it is between 7 and 12, it is 8-byte double precision float.
`LONGFLOAT` | System dependent - 8-byte double precision float.
`INTEGER` | -32767 to 32767 (215-1)
`LONGINTEGER` | -2147483647 to 2147483647 (231-1)
`SHORTINTEGER` | -127 to 127 (27-1)
`BULK` | 1 to maximum value of C int, typically 231-1 bytes

### 3.2.2 Generic Data Types

The generic data types and their ranges are:

Table 3-3: Generic Data Types

Type | Range
`CHAR` | 1 to maximum value of C int, typically 231-1 bytes
`INTEGER` | -2147483647 to 2147483647 (231-1)
`DECIMAL` | 1 to 83 digits (42 before, 41 after the decimal)
`FLOAT` | System dependent - 8-byte double precision float
`DATE` | Using the format yyyymmdd, it is 00000101 to 99991231 (January 1, 0000 to December 31, 9999)

## 3.3 Character Data

There are four character data types: `CHAR` or `CHARACTER` for ordinary character strings, `NLSCHAR` or `NLSCHARACTER` for 8-bit character strings, `TEXT` and `NLSTEXT` for passages of text of virtually unlimited length.

### 3.3.1 CHARACTER Data Type

The `CHAR` data type stores strings consisting of one or more characters. The number of bytes can range in length from a single byte to the maximum value of a C int (typically 231-1 bytes on a 32-bit machine). Acceptable characters are: blank, underscore, any upper or lowercase letter, any digit or any other character, printable or non-printable.

Non-printable characters may be stored in `CHAR` type 3 only.

Attributes of type `CHAR` are specified as:

       attr CHAR [ (length [, type]) ]

where:

`length` | is the maximum number of bytes (the default is 25).
`type` | is 1, 2, or 3 (the default is 1).

Type 1 strips leading and trailing blanks on input, and accepts only printable characters as data (e.g. new lines may not be included). For example, the names:

       "    John P. Doe   "

and

       "John P. Doe"

are identical if they are entered as `CHAR` type 1.

Type 2 stores the data exactly as it is entered, and accepts only printable characters. The two names given as examples for `CHAR` type 1 above are different if entered as `CHAR` type 2.

Type 3 stores data exactly as entered, and allows any valid ASCII character except \ 0 (`NULL`) as input. A newline (the carriage return-linefeed combination) may be entered as a value for `CHAR` type 3 by typing a backslash ( \ ) immediately before pressing <Return> or <Linefeed> (the " \" character is not stored with the string). The backslash is the default Empress line continuation character, kept in the system variable `MSLINECONT`. If this has been changed, then the character given by `MSLINECONT` should be used.

Data for a `CHAR` attribute used in an SQL interface `INSERT` or `UPDATE` command or in a `WHERE` clause must be enclosed in quotes, so that Empress can identify the beginning and end of the data. Character data in the Interactive Interface does not require enclosing quotes since the Interactive Interface interprets input not beginning with a period (.) as data.

Although `CHAR` type 3 attributes can store any ASCII character (except `NULL`), problems may occur when data containing control characters is loaded from a file or entered from a terminal via the Query Language. This is because the operating system and the terminal handler may interpret certain control characters instead of accepting them as data. For example, <Ctrl+D> is normally the end-of-file character on a UNIX system. In their respective systems, both will stop data entry from a terminal or file.

It is always possible to enter problem characters into a database using a program written in one of the Empress Host Language Interfaces.

#### 3.3.1.1 Entering Very Long Lines

Entering a line of data containing more than the system buffer size (buffer overflow varies from system to system) requires special steps, because the operating system will only store a certain number of bytes in a single line typed from a terminal.

If you need to enter long lines, you can either:

1. Place these attribute values in a file using a system editor, and use an insert or update from file command (see the Empress SQL: User's Guide on inserting/updating from a file).

2. Use the Interactive Interface and invoke the system editor with the`.zz` command to enter the long lines (see The Interactive Interface chapter of this manual).

Terminals with automatic wraparound do not normally insert a new line into the text when the wraparound occurs. Although data entered at a terminal in this manner may appear to consist of several lines, it is treated by the operating system and Empress as a single line unless an explicit new line has been entered by pressing the <Return> or <Linefeed> key.

On the other hand, some text editors with automatic wrap (such as vi) can insert a new line into the typed text at the wrap point. If you are creating data files using an editor, you should check to see whether this is occurring, to prevent surprises when you enter the data into a table.

Note that `CHAR` data types 1 and 2 do not allow new lines as data values. `CHAR` type 3 and the `TEXT` data type do allow new lines but the line continuation character (set by `MSLINECONT` with the default backslash (\)) must be typed before the new line is entered.

### 3.3.2 TEXT Data Type

The `TEXT` data type is designed to store passages of text. `TEXT` data can range in size from a single character to the maximum value of a C int (typically 231-1 bytes on a 32-bit machine).

A `TEXT` attribute is defined as:

       attr TEXT [ (display [, primary [, overflow [, extents ]]])];

where:

`display` | is the number of characters to display in a column when selecting in SQL Interactive Interface. This parameter does not affect how the data is stored in files. The default value for this parameter is 20.
`primary` | is the number of characters to be stored in the main table. The minimum value for this parameter is 0. The default value for this parameter is 20.
`overflow` | is the number of characters in each extent of overflow storage. The minimum value for this parameter is 16. The default value for this parameter is 20.
`extents` | is the number of pointers stored in the main table pointing to extents in the overflow file. The default value for this parameter is 1.

#### 3.3.2.1 Display Length

Display length is the length of the displayed lines whenever Empress retrieves the attribute value via a `SELECT` or interactive `INSERT`/`UPDATE`:

1. For a `SELECT`, the entire attribute value is printed out immediately, using as many lines as required. For a `SELECT` using the `LIST` keyword, the data is printed as it was entered, without the lines restricted to the display length.

2. For an interactive `INSERT`/`UPDATE`, only one display-length line is printed, together with the number of characters remaining. The Empress editor or a system editor can then be used to update the value.

When a text passage is printed out on retrieval, lines are printed up to the limit of the display length, and then a new line is started. Words are broken in the middle, without hyphenation, if the display length is reached.

New lines which are embedded in the text are preserved on output. Note that you must precede a new line by a line continuation string (the default is ( \ ) and is defined by the system variable `MSLINECONT`) to enter it as part of the text. The new line will itself take up storage equivalent to 1 character while the backslash is not stored.

If you have a `TEXT` attribute with a display length of 20 into which you have placed four 40-character lines, each ending with a new line, then from a `SELECT` with tabular output you will see eight 20-character lines printed. From an interactive `INSERT`/`UPDATE` you will see the first 20 characters of the first line, and a message stating that there are 140 more characters in the value.

If the same `TEXT` attribute has a display length of 60, from a `SELECT` with tabular output you will see the four 40-character lines each ending with a new line, followed by 20 blanks. Thus, the text column would appear to have a very wide separator from the next attribute column.

If you simply enter 160 characters into the attribute as data, with no embedded new lines, then for a display length of 60 a `SELECT` with tabular output will produce three lines, the last line padded with 20 blanks at the end. A display length of 40 produces exactly four lines; a display length of 20 produces eight lines, and so on.

#### 3.3.2.2 Primary Storage Length

Only the beginning of the each `TEXT` attribute value is stored in the table. The rest is stored in an overflow area. The number of bytes stored in the main table is given by the primary storage length. Primary storage length is independent of the length of input or displayed lines, and altering it will not affect the length of displayed lines.

The best primary storage length depends on the type of data you expect to store. Generally, the primary storage length will be the same as the display length.

For short pieces of text, like a couple of lines, it is best to set primary storage large enough to hold most of the text passages which will be entered, and to specify short lines for overflow storage. This is economical for storage and gives fast retrieval. If, for example, you want to store an address in a `TEXT` data type and you expect almost all your addresses to be four lines long, make your primary storage large enough to hold all four lines. If your display lines are to be 40 characters, you would specify primary storage as 160 characters, and it would take up slightly more storage in your table than a 160-character `CHAR` attribute.

#### 3.3.2.3 Overflow Storage Length

For each `TEXT` data type attribute, a separate overflow storage file is created to store the data which does not fit into the main table. Overflow length is the number of characters stored in each extent in this overflow area. (An extent is a unit of storage assigned to the overflow file.) Overflow length may be specified as zero but the purpose of the `TEXT` data type is unlimited storage, so the size of the overflow storage is usually greater than zero. As many lines are put in overflow storage as are needed, and the overflow can keep growing indefinitely.

If you never actually use the overflow storage, the overhead for the empty overflow file is minimal. If you really do not want any overflow storage, you should use `CHAR` type 3 instead of `TEXT`. They both accept new lines, and the major difference in terms of retrieval is that you cannot specify a display length for `CHAR` 3, but are given the attribute length as the display length.

Fetching data from overflow storage adds a little overhead to the time taken to retrieve data, but overflow storage is only searched if it is actually used.

If you specify short overflow storage length, little space will be wasted if the last overflow record is only partially filled, or if you are typing in short lines. (Short lines ending in a new line will be padded to the overflow length in the file in which they are actually stored.) However, there will be a greater overhead involved in storage if there are many overflow records. In contrast, a large overflow length require fewer extents and therefore less time is involved in accessing records.

#### 3.3.2.4 Directly Accessible Extents

The data in the overflow file was organized as tree structure. Each extent of the overflow file can be either a data block or a pointer block with the leaves being data blocks, and other nodes being pointer blocks. When variable length data is inserted into a table, the overflow data is stored either in contiguous extents (whenever it's possible) or in non-contiguous extents (when it's not permissible by the system). A pointer block in the overflow file is not required if overflow data is stored in contiguous extents or there is enough space to hold all the pointers to the extents in the main table (parameter `extents`).

If you know approximate number of extents required in the overflow file, to specified this number as `extents` in the attribute parameter will improve performance since all the extents in the overflow file can be directly accessible.

### 3.3.3 NLSCHARACTER Data Type

The `NLSCHARACTER` data type is essentially the same as the `CHARACTER` with the exception of characters being stored in 8-bit.

### 3.3.4 NLSTEXT Data Type

The `NLSTEXT` data type is essentially the same as `TEXT` with the exception of characters being stored in 8-bit.

## 3.4 Date Data

There are three data types for storing dates:

- `DATE`, for ordinary day-month-year dates .
- `TIME`, for time-of-day data.
- `MICROTIMESTAMP`, for time-of-day plus microseconds information.

### 3.4.1 DATE Data Type

The `DATE` data type is designed specifically for storing dates. A `DATE` attribute is specified as:

       attr DATE [ (type) ]

where:

`type` | ranges from 0 to 8. If`type` is not specified, it is 0 by default.

The nine different `DATE` data types cover most common date formats, and are as follows:

Table 3-4: Empress DATE Data Type Format

Type | Format | Year Range | Example
0 | yyyymmdd | 0000-9999 | 19980627
1 | dd aaaaaaaaa yyyy | 0000-9999 | 27 June 1998
2 | aaaaaaaaa dd, yyyy | 0000-9999 | June 27, 1998
3 | mm/dd/yy | nn00-nn99 * | 06/27/98
4 | dd/mm/yy | nn00-nn99 * | 27/06/98
5 | dd aaa yy | nn00-nn99 * | 27 Jun 98
6 | aaa dd, yy | nn00-nn99 * | Jun 27, 98
7 | mm/dd/yyyy | 0000-9999 | 06/27/1998
8 | dd/mm/yyyy | 0000-9999 | 27/06/1998

*
  `nn` is defined by the Empress system variable `MSDATELIMIT`, the value of which is assigned by the user to indicate the preferred century for entering dates.

On input, the date-type only determines the order in which Empress expects to find the values for day, month, and year. On output, the date-type determines the display format.

The months may be entered as two digit numbers, full names, or three-letter abbreviations. Names may be in any combination of upper- and lowercase. For example, `November`, `november`, `Nov`, `NOV`, `nov`, and `11` are all stored and retrieved as `November` for `DATE` types 1 and 2, as `Nov` for `DATE` types 5 and 6, and as `11` for `DATE` types 3, 4, 7, and 8.

For `DATE` types 0, 1, 2, 7, and 8 (which have four-digit years), entering only one or two digits for a year causes the date to be interpreted as whatever the value set by the Empress system variable `MADATELIMIT`. For example, setting `MSDATELIMIT` to `2000`, and entering `56` results in the year `2056` and entering `1` results in the year `2001`. Entering three digits for the year causes a leading zero to be appended to the year.

For `DATE` types 3, 4, 7, and 8 (which have non-blank separators) the slash (/) in the date may also be entered as a space, a hyphen (-), a comma (,) and a period (.). However, all dates are printed with slashes on retrieval. For example, `27/06/1998`, `27-06-1998` and `27,06,1998` may all be legitimately entered as a type 8 `DATE`, but all are printed as `27/06/1998` when they are retrieved.

Note that Empress system variable `MADATELIMIT` determines the century for the `DATE` data types that print only two digits for the year. For detail of how to set this variable, please refer to Database Administrator's Guide.

#### 3.4.1.1 Today's Date

For any `DATE` type, entering either `TODAY` or `NOW`, in any combination of upper- and lowercase, stores the current date in the appropriate format.

#### 3.4.1.2 Universal Date Format

All Empress `DATE` data types will accept as legal input a date written in the form "`yyyymmdd`", such as "`19980409`" for April 9, 1998. This date format may be used for both data entry and in comparisons.

#### 3.4.1.3 Customized Date Entry Format

Empress provides a system variable, `MSDATEPIC`, which may be set to indicate your preferred order of day, month, and year for entering dates. It does not affect the format of displayed output.

The main effect of setting a date picture is to indicate the order of day, month, and year in the date values entered. Once this is known, Empress can turn the date entered into the form appropriate for the particular date type.

`MSDATEPIC` may be set to a date picture using the following symbols: `d` for a day digit, `m` for a month digit, `a` for a month letter, and `y` for a year digit. A picture is composed of a number of letters or digits enclosed in double quotes. The default value of `MSDATEPIC` is:

       dd aaaaaaaaa yyyy

#### 3.4.1.4 Interpreting Dates

When a date value is entered for a date attribute, it is evaluated as follows:

1. Empress first checks to see whether it can be made into a valid date using the universal date format (`yyyymmdd`), and if so, enters it.

2. If the value is not in universal format, Empress checks if it is a valid date using the day, month and year order determined by the format of the `DATE` type for that attribute, and enters the value if it does.

3. Finally, if both these tests have failed, Empress attempts to reconcile the day, month and year order with the order specified in `MSDATEPIC`. If this fails, the value is rejected.

As an example, consider a `DATE` type 7 attribute, whose precise format is `mm/dd/yyyy`, with `MSDATEPIC` set to `dd aaa yy`.

To enter the date for June 27 1998, the following are acceptable according to the universal format: `19980627` and `980627`.

The order of the precise `DATE` type 7 format allows the following. The days represented by `06` may also be entered as `6`.

       06/27/1998
       06/27/98
       06-27-1998
       06-27-98
       June 27 98

Finally, the order specified by `MSDATEPIC` allows the following:

       27 Jun 98
       27 Jun 1998
       27 June 1998
       27 June 98
       27 6 1998
       27 6 98
       27 06 1998
       27 06 98

The last four may also be entered with slashes or dashes instead of spaces.

The following forms are unacceptable, since they fit none of the above categories: `1998 June 27`, `98 jun 27`, and so on.

#### 3.4.1.5 Dates in Expressions

Since a literal date in an expression is not associated with any particular attribute, `DATE` types associated with attributes are not considered in evaluating the date. When using a date in an expression, such as when doing date arithmetic, the date must be in either universal format or satisfy the day, month, year order specified in `MSDATEPIC`. (See also the discussion on date expressions in the Empress SQL: User's Guide).

#### 3.4.1.6 Century Indicator for Two-Digit Year DATE Data Type

Empress provides a system variable `MSDATELIMIT` which may be set to indicate your preferred century for entering dates.

The main effect of setting `MSDATELIMIT` is for `DATE` types 3, 4, 5, and 6 (which have two-digit years). For example, setting `MSDATELIMIT` to `2000`, and entering only two digits for a year causes the date to be interpreted as a 21th century date. As an example entering `00` results in the year `2000`. By setting the ``MSDATELIMIT`` variable will also affect the ranges of the year, for example, from year 2000 to year 2099.

### 3.4.2 TIME Data Type

The `TIME` data type stores date plus time-of-day information. `TIME` attributes are specified as:

       attr TIME [(type)]

The `type` is the same as for `DATE` attributes (i.e., a number from 0 to 8), and specifies the format in which the date part of the value will be printed. If the `type` is omitted, it defaults to 0.

Time is recorded using the 24-hour clock (e.g., 3 PM IS 15:00:00). For `TIME` types 0, 1, 2, 7, and 8, the time may range from 1 January 1970 00:00:00 to 31 December 2035 23:59:59, and for types 3, 4, 5, and 6, from 1 January 1970 00:00:00 to 31 December 2035 23:59:59.

The different styles of output can best be shown by an example. For 2:56:15 PM, August 28, 1998, the nine `TIME` data types produce:

Table 3-5: Empress TIME Data Types Format

Type | Format | Year Range | Example
0 | yyyymmddhhmmss | 1970-2035 | 19980828145615
1 | dd aaaaaaaaa yyyy hh:mm:ss | 1970-2035 | 28 August 1998 14:56:15
2 | aaaaaaaaa dd, yyyy hh:mm:ss | 1970-2035 | August 28, 1998 14:56:15
3 | mm/dd/yy hh:mm:ss | 1970-2035 | 08/28/98 14:56:15
4 | dd/mm/yy hh:mm:ss | 1970-2035 | 28/08/98 14:56:15
5 | dd aaa yy hh:mm:ss | 1970-2035 | 28 Aug 98 14:56:15
6 | aaa dd, yy hh:mm:ss | 1970-2035 | Aug 28, 98 14:56:15
7 | mm/dd/yyyy hh:mm:ss | 1970-2035 | 08/28/1998 14:56:15
8 | dd/mm/yyyy hh:mm:ss | 1970-2035 | 28/08/1998 14:56:15

#### 3.4.2.1 Current Time

For any of the `TIME` data type formats, entering `TODAY` (in any mixture of upper- and lowercase letters) displays the current date and 00:00:00 as the time portion. Entering `NOW` will display today's date and the current time. `TIME` data values are interpreted in the same way as `DATE` data values.

### 3.4.3 MICROTIMESTAMP Data Type

The `MICROTIMESTAMP` data type stores `TIME` plus microseconds information. `MICROTIMESTAMP` attributes are specified as:

       attr MICROTIMESTAMP [(type)]

The type is the same as for `TIME` attributes (i.e. a number from 0 to 8), and specifies the format in which the date part will be printed. If the type is omitted, it defaults to 0.

This data type stores information regarding date and time including number of microseconds which may range from January 1, 0000 00:00:00.000000 to December 31, 9999 23:59:59.999999 where the last 6 digits in the format represent the number of microseconds. This is a large improvement over the range of the `TIME` data type.

The nine different `MICROTIMESTAMP` styles are shown below. The output is for August 28, 1998 2:56:15 p.m. and 234599 microseconds.

Table 3-6: Empress MICROTIMESTAMP Data Types

Type | Format | Year Range | Example
0 | yyyymmddhhmmssffffff | 0000-9999 | 19980828145615234599
1 | dd aaaaaaaaa yyyy hh:mm:ss.ffffff | 0000-9999 | 28 August 1998 14:56:15.234599
2 | aaaaaaaaa dd, yyyy hh:mm:ss.ffffff | 0000-9999 | August 28, 1998 14:56:15.234599
3 | mm/dd/yy hh:mm:ss.ffffff | nn00-nn99 * | 08/28/98 14:56:15.234599
4 | dd/mm/yy hh:mm:ss.ffffff | nn00-nn99 * | 28/08/98 14:56:15
5 | dd aaa yy hh:mm:ss.ffffff | nn00-nn99 * | 28 Aug 98 14:56:15.234599
6 | aaa dd, yy hh:mm:ss.ffffff | nn00-nn99 * | Aug 28, 98 14:56:15.234599
7 | mm/dd/yyyy hh:mm:ss.ffffff | 0000-9999 | 08/28/1998 14:56:15.234599
8 | dd/mm/yyyy hh:mm:ss.ffffff | 0000-9999 | 28/08/1998 14:56:15.234599

*
  `nn` is defined by the Empress system variable `MSDATELIMIT`, the value of which is assigned by the user to indicate the preferred century for entering dates.

If you are using `MICROTIMESTAMP` data type in the client/server application using ODBC Interface, you must upgrade your Empress ODBC Server to version 2.2.

## 3.5 Decimal Data

There are two data types for storing numbers with decimal places: `DECIMAL` or `DEC`, for ordinary decimal numbers and `DOLLAR`, for storing monetary values.

### 3.5.1 DECIMAL Data Type

The `DECIMAL` or `DEC` data type stores decimal data, and allows the specification of both the total number of digits and the number of digits following the decimal point. `DECIMAL` data may range in value from 1 to 38 digits. A `DECIMAL` attribute is defined as:

       attr DECIMAL [(total_digits [, decimal_places])]

where :

`total_digits` | specifies the total number of digits stored, excluding sign and decimal point. The default is 1.
`decimal_places` | is the number of digits after the decimal point. The default is 0.

The following shows how `DECIMAL` data is stored and retrieved by attributes defined using different parameters:

       Input      (5,2)      (4,2)      (6,4)      (6,0)      (2,0)

       -10        -10.00     -10.00     -10.0000   -10        -10
       .4         0.40       0.40       0.4000     0          0
       1.234567   1.23       1.23       1.2346     1          1
       1.5        1.50       1.50       1.5000     2          2
       10         10.00      10.00      10.0000    10         10
       12.34      12.34      12.34      12.3400    12         12
       21.9       21.90      21.90      21.9000    22         22
       123.4      123.40     ***        ***        123        ***

Note how rounding occurs. Extra decimal places are rounded to the nearest number. The entries marked with asterisks (***) indicate a Conversion Error on input. A Conversion Error occurs if you attempt to enter too many digits before the decimal point or if the entered value cannot be interpreted as a decimal number (e.g., `abcd`).

### 3.5.2 DOLLAR Data Type

The `DOLLAR` data type is designed for storing monetary values, which may be either positive or negative. `DOLLAR` values may range from 1 to 13 digits. `DOLLAR` attributes are specified as:

       attr DOLLAR [ (max_digits [ , type])]

where:

`max_digits` | specifies the maximum number of digits before the decimal point (the largest dollar amount the attribute may hold).Empress allows a maximum of 13. The default is 4.
`type` | is 1, 2, or 3. If the `type` is not specified, it will be 1 by default.

A `DOLLAR` attribute of type 1 prints the value with the dollar sign flush against the leading digit. A `DOLLAR` attribute of type 2 prints the value with the dollar sign left-adjusted for the maximum number of digits, with asterisks used to fill in up to the leftmost printing digit. A `DOLLAR` attribute of Type 3 prints the value with no dollar sign, asterisks, or commas.

An attribute specified as `DOLLAR (10,1)` would hold an amount up to $9,999,999,999.99, and smaller amounts entered would be printed with no blanks after the dollar sign:`$1,253.47`. An attribute specified as `DOLLAR (10,2)` would hold and print the same maximum amount, but smaller amounts would have asterisks printed after the dollar sign to the maximum number of digits: `$******1,253.47`. An attribute specified as `DOLLAR (10,3)` would hold and print the same maximum amount, but no dollar sign, commas, or asterisks would be printed: `1253.47`

Dollar data used in a Query Language `INSERT` or `UPDATE` command or in a `WHERE` clause must have the dollar sign typed twice if it is included, so that Empress does not confuse it with an Empress variable. Dollar data entered with dollar signs via the Interactive Interface does not require the double dollar sign.

#### 3.5.2.1 Customized Dollar Formats

The Empress system variable `MSDOLLAR` may be used to control input and output formats for `DOLLAR` data types. It is specified as a character string with a maximum of 50 characters, where the first character is the dollar/cent separator, the second is the thousands separator, the third is the filler character (for `DOLLAR` type 2 attributes), and the rest of the string is the currency sign.

The default value for `MSDOLLAR` is:

        .,*$

giving the standard Empress `DOLLAR` attribute appearance.

As an example of changed output appearance, for German currency `MSDOLLAR` would be set to:

       ,.*DM

so that a `DOLLAR (6,2)` amount would print as:

       DM**1.234,56

For U.S. currency, setting `MSDOLLAR` to:

       ., US$

prints the same amount as:

       US$ 1,234.56

Dollar amounts may be entered from files or via the Query Language in the same style as it is produced via a `SELECT` command.

Note that if the thousands separator is set to a period (.), you need to be careful when entering dollar data via the Interactive Interface. For example, if you enter a whole dollar amount greater than $999 which includes a single thousands separator, so that the value entered contains one period (e.g. `1.234`), Empress will confuse the period with a decimal point.

Thus, if `MSDOLLAR` is set to "`,.*DM`", the following amounts will be entered correctly:

       Value Entered    Value as a Decimal Number

       1.234,56         1234.56
       DM1.234          1234.00
       1.234,           1234.00
       1.234.567        1234567.00

Entering `1.234`, however, produces a decimal value of `1.23`, rather than `DM1.234,00`.

## 3.6 Float Data

Float data can store non-integer numeric values such as numbers with decimal values. Float data differ from the Decimal data in providing for fewer decimal digits but a greater range in values. Float data may be of three types:

- `FLOAT`
- `LONGFLOAT` or `DOUBLE PRECISION`
- `REAL`

A float data attribute is specified as either one of the following:

       attr FLOAT [decimal places]
       attr LONGFLOAT
       attr REAL

`REAL` data type are represented as 7 significant digits (the exact number depends on the machine on which Empress is running), and are represented in scientific notation on retrieval (i.e., 10342 is printed as `1.034200e+04`, and 0.010342 is printed as `1.034200e-02`). Data values stored may be very large, although this varies depending on the machine you are using.

`LONGFLOAT` or `DOUBLE PRECISION` data are stored as 16 significant digits, and are also retrieved in scientific notation. The exact range of values stored in `LONGFLOAT` also depends on the machine.

The number of digits of precision stored by `FLOAT` is set by the user, subject to the limitations of the system. If the number of decimal places is between 1 and 6, it is the same as `REAL` (4-byte single precision float). If the number of decimal places is between 7 and 12, then it is an 8-byte `DOUBLE PRECISION` float.

## 3.7 Integer Data

There are three types of integer data:

- `INTEGER` or `INT`
- `SHORTINTEGER` or `SMALLINT`
- `LONGINTEGER`

Integer data attributes are defined as either one of the following:

       attr SHORTINTEGER
       attr [ INTEGER ]
       attr LONGINTEGER

The integer types to chosen depends on the size of the numbers you plan to store. `SHORTINTEGER` values can range from -127 to 127(+/-27-1); `INTEGER` values can range from -32767 to 32767(+/-215-1); and `LONGINTEGER` values can range from -2147483647 to 2147483647(+/-231-1). Since `LONGINTEGER` covers the ranges of the other two types, it might seem unnecessary to have `INTEGER` and `SHORTINTEGER`. These, however, take up half and one-quarter of the storage space required by `LONGINTEGER`, respectively, and thus a considerable saving in storage space may be obtained if it is known that all data values for a given attribute will never exceed a certain size.

## 3.8 User Interpreted Data

The `MULTIMEDIA` or `BULK` data type stores data of up to the maximum value of a C int (typically 231-1 bytes on a 32-bit machine) in binary representation. This allows storage of arbitrary sequences of data values. BULK attributes are declared as:

|  |  | `attr` | `|BULK |MULTIMEDIA` | `| [(display [, primary [, overflow [, extents]]])]; |`

where:

`display` | is the number of bytes to be printed per line when the data is retrieved by a `SELECT` command. Data will be displayed in hexadecimal.
`primary` | is the number of bytes to be stored in the main table.
`overflow` | is the number of bytes in each extent overflow storage which is separate from the main table.
`extents` | shows the number of extents available to the attribute and their locations, as established by pointers in the main table.

If these parameters are not specified, the default for each is 20 bytes. For a further discussion of these three parameters, see the section on the `TEXT` data type.

Data may be entered manually, or stored directly in a file using the keywords `DIRECT_FROM`. Data is entered in hexadecimal as either:

- a continuous stream in which each number is two digits and no blanks are allowed.
- one- or two-digit numbers separated by blanks.

The two input styles may not be intermixed within one data value. The hexadecimal digits are the normal ASCII characters 0-9, A-F (or a-f).

Data are converted from hexadecimal into binary and stored as binary, in which format they may be accessed directly by application programs. Output from a Query Language `SELECT` is reconverted to hexadecimal and printed as two-digit numbers separated by single blanks (the alphabetic digits are printed in lower case). (See also the discussion on Internal, External and File Attribute Formats in the Empress Host Language: C Interface - mx Routines and Empress Host Language: C Kernel Level Interface - mr Routines manuals.)

An example of accessing `BULK` data values is given in the example program `voice.c` in the Empress Host Language: C Kernel Level Interface - mr Routines manual under the section Manipulating Bulk Data.