Informix Guide to GLS Functionality SQL Features

This section explains how a locale affects the way that a database server handles the MONEY data type, extended data types, and smart large objects (CLOB and BLOB data types).

For the syntax of these data types, see the Informix Guide to SQL: Syntax. For descriptions of these data types, see the Informix Guide to SQL: Reference.

The MONEY data type stores currency amounts. This data type stores fixed-point decimal numbers up to a maximum of 32 significant digits. You can specify MONEY columns in data definition statements such as CREATE TABLE and ALTER TABLE.

The choice of locale affects monetary data in the following ways:

• The value of the default scale parameter in the definition of MONEY columns
• The currency notation that the client application uses

The locale defines the default scale and currency notation in the MONETARY category of the locale file. For information on the MONETARY category of the locale file, see The MONETARY Category.

Define a MONEY column with the following syntax.

Internally, the database server stores MONEY values as DECIMAL values. The precision parameter defines the total number of significant digits, and the scale parameter defines the total number of digits to the right of the decimal separator. For example, if you define a column as MONEY(8,3), the column can contain a maximum of eight digits, and three of these digits are to the right of the decimal separator. A sample data value in the column might be 12345.678.

If you omit the scale parameter from the declaration of a MONEY column, the database server provides a scale that the locale defines. For the default locale (U.S. English), the database server uses a default scale of 2. It stores the data type MONEY(precision) in the same internal format as the data type DECIMAL(precision,2). For example, if you define a column as MONEY(10), the database server creates a column with the same format as the data type DECIMAL(10,2). A sample data value in the column might be 12345678.90.

For nondefault locales, if you omit the scale when you declare a MONEY column, the database server declares a column with the same internal format as DECIMAL data types with a locale-specific default scale. For example, if you define a column as MONEY(10), and the locale defines the default scale as 4, the database server stores the data type of the column in the same format as DECIMAL(10,4). A sample data value in the column might be 123456.7890.

For the complete syntax of the MONEY data type, see the Informix Guide to SQL: Syntax. For a complete description of the MONEY data type, see the Informix Guide to SQL: Reference.

Client applications format values in MONEY columns with the currency notation that the locale defines. This notation specifies the currency symbol, thousands separator, and decimal separator. For more information about currency notation, see Numeric and Monetary Formats.

For the default locale, the currency symbol is a dollar sign ($), the thousands separator is a comma (,), and the decimal separator is a period (.). For nondefault locales, the locale defines the appropriate culture-specific currency notation for monetary values. You can also use the DBMONEY environment variable to customize the currency symbol and decimal separator for monetary values. For more information, see Customizing Monetary Values.

The extensible data type system of Dynamic Server allows users to define new data types and to define the behavior of these new data types to the database server. This section explains how these types are handled in GLS processing.

An opaque data type is fully encapsulated to client applications; that is, its internal structure is not known to the database server. Therefore, the database server cannot automatically perform locale-specific tasks such as code-set conversion for opaque types. All GLS processing (code-set conversion, localized collation order, end-user formats, and so on) must be performed in the opaque-type support functions.

When you create an opaque data type, you can write the opaque-type support functions as C UDRs that can handle any locale-sensitive data. For more information, see Locale-Sensitive Data in an Opaque Data Type.

Dynamic Server also supports complex data types:

• Collection data types: SET, MULTISET, and LIST
• Row data types: named row types and unnamed row types

Any of these data types can have members with char, date or time, or numeric data types. The database server can still handle the GLS processing for these data types when they are part of a complex data type.

A distinct data type has the same internal storage representation as its source type but has a different name. Its source type can be an existing opaque data type, built-in data type, named row type, or another distinct data type. Dynamic Server handles GLS considerations for a distinct type as it would for the source type.

A smart large object can store text or images. Smart large objects are stored and retrieved in pieces and have database properties such as recovery and transaction rollback. Dynamic Server supports the following two smart-large-object data types:

• The BLOB data type stores any type of binary data, including images and video clips.
• The CLOB data type stores text such as PostScript or HTML files.

You can seek smart large objects in bytes but not in characters. Therefore, you need to manage the byte offset of multibyte characters when you search for information in smart large objects. You can use the functions of Informix GLS to assist you in this task. For more information, see the Informix GLS Programmer's Manual.

To access smart large objects through a client application, you must use an API, such as ESQL/C or DataBlade API. Because GLS does not support direct access to smart-large-object data through SQL, GLS does not automatically handle the data (no automatic code-set conversion, localized collation order, end-user formats, and so on). All support must be done within an API.

When you copy CLOB data from a file, Dynamic Server performs any necessary character-set conversions. If the client or server locale (when it copies from client and server files, respectively) differs from the database locale, Dynamic Server invokes the routines to convert to the database locale.