Data Types

The database server uses data types to determine how to store and retrieve different kinds of data.

The following table lists the categories of data types available to you when you create a DataBlade module. You must define some of these data types; others you can use just as they are.

An extended data type is a data type that is not built into your Informix database server. Extended data types include opaque data types, distinct data types, collection data types, and row data types. Extended data types are described in the IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Collection and row data types are extended data types built from a combination of other data types; their components are accessed through SQL statements.

Built-in Data Types

Built-in data types include character, numeric, time, large object, and Boolean data types. You can use built-in data types as building blocks in opaque, distinct, row, and collection data types.

Built-in data types are automatically included in your DataBlade module project file as imported objects.

For a complete list and descriptions of built-in data types, see the IBM Informix: Guide to SQL Reference manual.

Qualified Built-in Data Types

A qualified data type is a built-in data type that has an added qualification that specifies information about the storage size, range of values, or precision of the type. For example, the DECIMAL(p,s) data type can take qualifiers for precision (the total number of digits) and scale (the total number of digits to the right of the decimal point).

You must define a qualified data type by specifying its qualifications.

Example

The DECIMAL(6,3) data type has six digits, with three digits to the right of the decimal point; for example, 123.456.

More Information

For a complete list of qualified data types and their parameters, see the IBM Informix: Guide to SQL Reference manual.

Distinct Data Types

A distinct data type is an existing data type to which you assign a unique name. The distinct data type inherits all routines from the source data type, but it cannot be directly compared to the source data type without an explicit cast.

Why Use a Distinct Data Type? Use a distinct data type if you want to create routines that do not work on the source data type. You can use a distinct data type to control how the data type is cast, or converted, to other data types.

You can use distinct data types to create inheritance hierarchies, which allow you to write very selective routines. A distinct data type can be passed to all routines defined for the source; however, the source data type cannot be passed to routines defined for the distinct data type.

Example

You can create two distinct data types based on the MONEY type: lira and us_dollar. To determine the dollar value of a specified lira value, you can create an explicit cast between lira and us_dollar by writing a function that multiplies the value of lira by the exchange rate. Using the cast allows you to perform arithmetic operations involving both distinct data types and to return a meaningful result.

More Information

For a description of distinct data types, see the IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Collection Data Types

A collection data type is a group of values of a single data type in a column. Each value is referred to as an element. A collection data type is defined using a type constructor and an element data type. Type constructors determine whether the database server checks for duplicate elements or orders the elements. The following table describes the collection type constructors.

For a SET, the database server prevents insertion of duplicate elements. For a MULTISET, the database server takes no special actions. For a LIST, the database server orders the elements.

Elements can be almost any data type, including other extended data types and built-in data types such as smart large objects. You can access any element in a collection individually through SQL statements.

The number of elements in a collection is not mandated. You can change the number of elements in a collection without reinserting it into a table, and different rows can have different numbers of elements in their collections.

What Does a Collection Look Like? The following diagram illustrates a collection data type using a SET constructor and the LVARCHAR data type in a column called Dependents.

Figure 2. Sample Collection Data Type

Instead of putting information on dependents in a separate table, all the information is contained in one row, using a collection data type. You can add or remove elements without altering the table's columns.

Why Use a Collection Data Type? You can use collection data types to reconfigure a table with awkwardly long rows by grouping data into a single column. Use a collection if you have data of the same data type that can be naturally grouped into a single column. You can group data even further by creating a collection of row types or other collections.

Collections are also useful as returned values: for example, a group of values from many rows in a column or fields in a row type. For example, if you want to obtain a list of every city in which your employees live from the sample collection data type in Figure 2, you could create a collection on the Location column to return a set of values.

The following function types can return collections:

• A user-defined function that returns a collection
• An iterator function that returns a single value at a time but is called repeatedly to assemble a collection

More Information

For a description of collection data types, see the IBM Informix: Guide to SQL Tutorial.

Row Data Types

A row data type can be thought of as a row of columns, of varying data types, stored in a single database table column. Row data types follow essentially the same rules as database tables. The columns within a row data type are called fields. They can be almost any data type, including other extended data types and built-in data types, such as smart large objects. You can access fields individually using SQL statements.

To create a row data type, you specify:

• a unique name for the whole row type
• a unique name for each field
• a data type for each field.

What Does a Row Data Type Look Like? The following diagram illustrates a row type named address_t in a column named Address.

Figure 3. Sample Row Data Type

Instead of having additional columns in the Address table, the row data type groups data that is most often accessed together in one column. The table Address consists of the columns Name(LVARCHAR(30)), Address(address_t), and Dependents(SET(LVARCHAR)). The row data type address_t consists of the named fields Street(LVARCHAR(20)), City(LVARCHAR(20)), State(CHAR(2)), and Zip_code(INTEGER).

Why Use a Row Data Type? Like collection data types, row data types allow you to reconfigure your database table. Use a row type if you have data of differing data types that group naturally into a single column. You can further group your data if you include a collection or another row data type as a field within your row data type.

Row data types can be useful for handling smart large objects. For example, if a row data type has a field that is an opaque data type containing an image in a smart large object, the other fields of the row data type could contain additional information about the image.

For best performance, use row data types if most user queries access all or most of the row data type's fields.

You can use row data types to create inheritance hierarchies, allowing you to write very selective routines. A child row data type inherits its parent's fields and can be passed to all routines defined for the parent; however, the parent data type cannot be passed to routines defined for the child data type.

More Information

For a discussion on row data types, see the IBM Informix: Guide to SQL Tutorial.

Opaque Data Types

An opaque data type is a user-defined data structure. To successfully interpret opaque data types, the database server requires that the DataBlade module provide opaque data type support routines. You must provide support routines for your opaque data type.

BladeSmith generates boilerplate code for opaque data type support routines. You can write additional code in C or Java to implement the functionality your opaque data type requires. If you provide ActiveX value objects as a client-side interface to your opaque data types, you can write the underlying support routines for the opaque data type in C++. See Opaque Data Type Support Routines for more information.

Opaque data types typically contain more than one member, so they are similar to row data types, except that the database server can only operate on the individual components with support routines you define in the DataBlade module.

What Does an Opaque Data Type Look Like? The following diagram illustrates an opaque data type called circle, based on a structure called circle_t, in a column called circle_col.

Figure 4. Sample Opaque Type

The table circle_tab consists of the columns circle_id(SERIAL), color(VARCHAR(15)), and circle_col(circle). The opaque data type circle is defined as a C structure, circle_t, which contains a radius member and another structure, point_t. The point_t structure contains x and y members. To the database server, however, the whole circle_t structure is indivisible, unless you provide accessor functions.

Why Use an Opaque Data Type? Use an opaque type to extend SQL to address fundamentally new data types and allow operations on them. Such data types are typically indivisible or made up of components to which you want to control access.

For example, geographic data and many sorts of rich media data (images, audio, text, and so on) are have been represented by the use of opaque types. Opaque data types often contain smart large objects. Smart large objects are logically stored in a column in a database table but physically stored in a file. For example, an opaque data type for images could have a smart large object member containing the image and other members containing metadata about the image.

Opaque types require more work to create than other data types because you must write all the routines that support them.

Opaque Data Type Support Routines

BladeSmith enables you to generate support routine code for opaque data types. You might have to add code to implement the functionality your opaque data type requires.

The following table describes the support routines you can create and indicates the categories of opaque types for which they are recommended.

More Information

For a description of creating opaque types and their support routines, see the IBM Informix: DataBlade Developer's Kit User's Guide or IBM Informix: User-Defined Routines and Data Types Developer's Guide.