Transactions handling

Informix^® and SQLite have similar commands to begin, commit or rollback transaction. There are however some important differences you must be aware of.

With SQLite, DDL statements can be executed (and canceled) in transaction blocks, as with Informix.

Informix version 11.50 introduces savepoints with the following instructions:

SAVEPOINT name [UNIQUE]
ROLLBACK [WORK] TO SAVEPOINT [name] ]
RELEASE SAVEPOINT name

SQLite supports savepoints too. However, there are differences:

SAVEPOINT can be used instead of BEGIN TRANSACTION. In this case, RELEASE is like a COMMIT.
The syntax of a rollback to the savepoint is ROLLBACK [TRANSACTION] TO [SAVEPOINT] name .
The syntax of a release of the savepoint is RELEASE [SAVEPOINT] name .
Rollback must always specify the savepoint name.
You cannot rollback to a savepoint if cursors are opened.
In SQLite versions prior to 3.7, you cannot rollback are transaction if a cursor is open.

Solution

Regarding transaction control instructions, BDL applications do not have to be modified in order to work with SQLite. The BEGIN WORK, COMMIT WORK and ROLLBACK WORK commands are translated the native commands of SQLite.

Note: If you want to use savepoints, always specify the savepoint name in ROLLBACK TO SAVEPOINT and do not open cursors during transactions using savepoints. If you are using an SQLite versions prior to 3.7, it is not possible to perform a ROLLBACK WORK if a cursor (with hold) is currently open.