Uniface 10.4 and Informix: Simple Guide to Transaction Control 💾

#uniface #informix #transactions #database

This article gives a simple overview of how transaction control works when you use Uniface 10.4 with an Informix database. 😊

This post was created with the help of an AI assistant and is based on the official Uniface 10.4 documentation about “Transaction Control on Informix”.

Why transactions matter 💡

Transactions help you keep your data consistent.

Either all changes are saved, or none of them are saved, which is very important in financial, medical, or other critical systems.

A typical example:

You insert an order into an ORDERS table.
You insert order lines into an ORDER_LINES table.
You update the customer’s credit balance.

All these actions should be part of one transaction. If something fails, you want to roll back everything.

Transaction logging in Informix 🧱

Informix can work with or without transaction logging.

If there is no transaction logging, the database does not manage transactions in a way that supports safe commit and rollback operations; changes are effectively written without the option of a full transactional rollback.
If transaction logging is enabled, Informix supports both commit and rollback, so Uniface can use transactions safely.

So, if you want real transaction control, make sure that logging is turned on for your Informix database.

Never commit inside SQL strings 🚫

Uniface lets you send SQL directly using the sql ProcScript statement.

However, you must not put commit or rollback into the SQL string when you use the single connection mechanism.

Why?

In the single connection mechanism, a logon path can be shared by multiple logical paths.
If you end a transaction on that logon path, you end the transaction for all paths that use the same connector.

Example of what not to do:

; ❌ bad practice in single connection sql "update orders set status = 'PAID' where order_id = 123" sql "commit"

Better: keep your business SQL and your transaction control separate:

; ✅ better: let Uniface control the transaction sql "update orders set status = 'PAID' where order_id = 123" if ($status >= 0) commit ; Uniface commit else rollback ; Uniface rollback endif

In this version, Uniface manages the transaction on the logon path correctly.

Multiple connections vs. global transactions 🔀

Uniface and Informix also support a multiple connection mechanism.

In this mode, you can have several open connections, and each connection has its own transaction.

Multiple concurrent transactions are supported: each connection can start, commit, and roll back its own transaction.
Global transactions, where a single transaction spans several databases over multiple connections, are not supported by Informix as a native feature in this Uniface connector context.

Example scenario:

Connection A uses database FINANCE.
Connection B uses database LOGGING.

You can:

Start a transaction on A, do some updates, and commit A.
Start a transaction on B, do other updates, and roll back B.

But you cannot have one atomic transaction that covers both FINANCE and LOGGING at the same time just by using normal Informix transactions; for real distributed transactions you need a transaction manager and XA support.

Two-phase commit support ✅

Informix supports Two-Phase Commit (2PC) for XA-compliant environments, and this is handled largely transparently by the database and the transaction manager.

Two-Phase Commit is a protocol used to make sure that a distributed transaction either commits everywhere or rolls back everywhere.
In a setup with a transaction manager, Informix takes part in this process, while Uniface still uses its usual commit and rollback ProcScript statements to signal transaction boundaries.

The important point: you do not need special commit SQL statements inside your Uniface SQL strings to “enable” 2PC; the infrastructure around Informix handles that.

Simple practical tips 🧰

Here are some simple rules you can follow in daily work:

Make sure transaction logging is enabled if you need safe commit and rollback.
Do not put commit or rollback inside SQL strings when using the single connection mechanism; use Uniface’s commit and rollback instead.
When using multiple connections, remember that each connection has its own transaction, and Informix does not provide one global transaction across several databases in this setup.
If your environment uses Two-Phase Commit, let Informix and the transaction manager do the heavy lifting; keep your Uniface code clean and simple.