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.
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.
(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 &key database &body bodyPerforms 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 &key databaseRolls back changes made in database since the last commit.
commit &key databaseCommits 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 query-expression &key database 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 sql-expression &key database Basic function which executes the sql-expression . The sql-expression may be any SQL statement other than a query.