14.3.1 Functional Data Manipulation Language (FDML)

14.3.1.3 Transaction handling

A transaction in SQL is defined as starting from theconnect, or from acommit, rollback or data-dictionary update and lasting until acommit,rollback, data-dictionary update or adisconnect command. Applications should perform all database update operations in awith-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 awith-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 fromwith-transaction. This example inserts a new record into theemp table, then changes those employees whose department number is 40 to 50 and finally removes those employees whose salary is more than 300,000.

(connect "personnel")

(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

with-transaction &key database &body body

: 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

rollback &key database

: Rolls back changes made in database since the last commit.

commit

Function

commit &key database

: 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

query query-expression &key database

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

execute-command

Functions

execute-command sql-expression &key database

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