CHAPTER 3: Data Types

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

Table 3-1: Empress Data Types

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.

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.

The parametric data types are as follows:

Table 3-2: Parametric Data Types

The generic data types and their ranges are:

Table 3-3: Generic Data Types

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.

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 2³¹-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:

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.

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.

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 2³¹-1 bytes on a 32-bit machine).

A TEXT attribute is defined as:

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

where:

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.

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.

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.

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.

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

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.

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

   attr DATE [ (type) ]

where:

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

Table 3-4: Empress DATE Data Type Format

*

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.

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.

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

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.

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).

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

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.

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

*

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.

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

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 :

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).

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:

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.

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:

    .,*$

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.

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.

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(+/-2⁷-1); INTEGER values can range from -32767 to 32767(+/-2¹⁵-1); and LONGINTEGER values can range from -2147483647 to 2147483647(+/-2³¹-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.

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

where:

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.