Uniface 10.4 + Informix: Simple Guide to Table Management 📊

✨ This blog post was created with the help of AI and is based on the official Uniface 10.4 documentation.

If you work with Uniface and Informix, you will sooner or later deal with database tables. In this post, we look at how Uniface 10.4 handles table management on Informix in simple terms, with examples and a few tips. 😊

Tables in Uniface and Informix

In Uniface, tables are created “on the fly”, which means they can be created automatically when needed, for example when you first store data for an entity. In addition, Uniface offers a Create Table utility that can generate SQL scripts to create tables manually with default row locking in the database.

These scripts are useful if you want more control over when and how tables are created, for example in a deployment process or in a CI/CD pipeline.

Table Names: What Is Allowed? 🔤

Uniface allows letters, numbers, and underscores in table names, but the first character must always be a letter. This keeps table names compatible with most databases, including Informix.

Informix lets you access tables from other users and even from other databases. To do this, you can use a qualified name in the form {DatabaseEnv:}{OwnerName.}TableName, where DatabaseEnv is a logical database environment from your Uniface path, OwnerName is the database user (schema), and TableName is the actual table name.

Example: Accessing a Table in Another Database

Imagine you have a Uniface path like this (simplified):

USYS$INF_PARAMS  $DBMSSQL = myinformixenv:

If your Uniface entity uses the table name customers and the path refers to myinformixenv, the generated SQL will use something like:

myinformixenv:customers

If you want to access a table that belongs to another user (owner), you add the owner prefix in the assignment file, for example:

ENTITY.CUSTOMERS.TABLE = SALES.customers

In this case, the resulting fully qualified name could look like myinformixenv:SALES.customers in the SQL statement.

Case Sensitivity: Lowercase vs Uppercase 🔠

Uniface converts the table name, including the owner prefix, to lowercase before sending it to Informix. Informix, however, treats OwnerName as uppercase by default unless it is wrapped in double quotes. This means that the user name in the database should be either all uppercase or all lowercase, but never mixed case.

Example: If your database user is SALES, you should consistently use SALES or sales, but not SaLeS. If you need mixed case, you must use double quotes in Informix, which can lead to complications and should usually be avoided.

What Are Overflow Tables? 🧺

Sometimes a single row in a table is not enough to store all data for a record, especially when you have long text or binary fields. In these cases, Uniface uses overflow tables. An overflow table is a second table that stores the extra data that does not fit in the main table.

The overflow table has the same name as the main table, but with a prefix O. For example, if your main table is customer_data, the overflow table will be called Ocustomer_data. The primary key fields are copied into the overflow table so that the extra data can be linked to the correct main record.

When Does Uniface Create Overflow Tables?

Overflow tables are created when the table contains one of the following Uniface data types:

• C* – Long character fields
• VC* – Variable-length character fields
• VU* – Variable-length Unicode fields
• S* – Long string fields
• VS* – Variable string fields
• R* – Raw binary fields
• VR* – Variable raw binary fields

These data types can become large, so Uniface uses the overflow table to store their content efficiently without blowing up the main table.

Example: Entity with Long Text Field

Let’s say you have an entity ORDER_NOTE with a primary key ORDER_ID and a large text field NOTE_TEXT defined as a VC* field in Uniface. The main table might be called order_note, and Uniface will create an additional overflow table Oorder_note to store the long text content of NOTE_TEXT.

The primary key ORDER_ID will exist in both tables so Uniface can match the main record with the corresponding overflow data.

Why Overflow Tables Cannot Always Be Created “On the Fly” ⚠️

There is an important limitation: overflow tables cannot be created on the fly if referential integrity is managed by Informix itself. Referential integrity means that the database enforces rules like foreign keys, so that related tables stay consistent.

In this case, Uniface uses the referential integrity features of the DBMS (Informix). These database constraints cannot be created automatically during runtime when Uniface first touches the table.

What You Need to Do Instead

If you need overflow tables and Informix is responsible for referential integrity, you must:

• Use the Uniface Create Table facility, and
• Use the Create Script facility.

These tools generate SQL scripts that create both the main tables and the corresponding overflow tables, including all required referential integrity controls. You then run these scripts in your Informix database before your application tries to use the tables.

Practical Tip for Projects 🛠️

• Plan all entities with long data types early and decide whether you want Informix to handle referential integrity.
• If yes, integrate the generated SQL scripts into your database deployment process (for example in your DevOps pipeline).
• Test the store and retrieve of large data (long text, documents, etc.) in both development and test environments to avoid surprises in production.

Small End-to-End Example 🌐

Imagine you build a module to store customer comments with Uniface and Informix.

1. You define an entity CUSTOMER_COMMENT with a primary key CUST_ID and a VC* field COMMENT_TEXT.
2. Because of VC*, Uniface needs an overflow table. The main table becomes customer_comment, the overflow table becomes Ocustomer_comment.
3. Your database team wants Informix to enforce foreign keys between CUSTOMER and CUSTOMER_COMMENT.
4. Therefore, you use the Uniface Create Table and Create Script utilities to generate all required SQL statements with referential integrity and run them on the Informix server before go‑live.
5. After that, your Uniface application can store and read long comments without problems, and the database keeps the relationships consistent.

This way, Uniface and Informix work together: Uniface handles the application logic and overflow table structure, and Informix ensures the data consistency at database level. 💪

Happy coding with Uniface and Informix! 🚀