14.3.1.3 Transaction handling

A transaction in SQL is defined as starting from the connect , or from a commit , rollback or data-dictionary update and lasting until a commit , rollback , data-dictionary update or a disconnect command. Applications should perform all database update operations in a with-transaction form in order to safely commit their changes.

This allows your code to cope with the fact that transactions are handled differently in the different vendor implementations. For example, in Oracle, there is no explicit "begin-transaction," but in Informix there is. This difference is transparent if all update operations are done within a with-transaction form, which is recommended.

The following example shows a series of updates to an employee table within a transaction. This example would commit the changes to the database on exit from with-transaction . This example inserts a new record into the emp table, then changes those employees whose department number is 40 to 50 and finally removes those employees whose salary is more than 300,000.

(with-transaction

 (insert-records :into [emp]

                 :attributes '(empno ename job deptno)

                 :values '(7100 "ANDERSON" "SALESMAN" 30))

 (update-records [emp]

                :attributes [deptno]

                :values 50

                :where [= [deptno] 40])

 (delete-records :from [emp]

                 :where [> [sal] 300000]))

with-transaction

Macro

Performs body within a transaction for database . The transaction is committed if the body finished successfully (without aborting or throwing), otherwise the database is rolled back.

rollback

Function

Rolls back changes made in database since the last commit.

commit

Function

Commits changes made to database .

Sometimes it is necessary to execute vendor-specific SQL statements and queries. For these occasions we provide the two functions below. They can also be used when the exact SQL string is known in advance and thus the square bracket is not required.

query

Function

Basic SQL query function which queries database with query-expression and returns a list of values as per select . This and execute-command should be used to execute non-standard vendor specific SQL code.

execute-command

Functions

Basic function which executes the sql-expression . The sql-expression may be any SQL statement other than a query.