Transactions

Some database operations involve carrying out a sequence of database commands. For example, information in two different tables may need to be updated. In these cases it may be very important that if one update is carried out, the other is also. If only one is done, it may leave the data inconsistent. You can use database transactions to ensure that all the operations are carried out. In addition, you can use transactions as a way of backing out of the middle of a sequence of operations. This section will demonstrate how to use transactions. If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the DatabaseExamples` package.

Functions for executing SQL transactions.

Load DatabaseLink and connect to the demo database.

In[28]:=

This creates a table to use for testing.

In[30]:=

This uses SQLSelect to view data in the TEST table. There is one row.

In[7]:=

Out[7]//TableForm=

SQLBeginTransaction is used to start a transaction.

In[8]:=

Next, two different insert operations are carried out.

In[9]:=

This shows that two rows have been inserted.

In[11]:=

Out[11]//TableForm=

If SQLRollbackTransaction is used, the database is returned to the point before the transaction began. The two rows are no longer present.

In[12]:=

Out[13]//TableForm=

A transaction is closed when it is rolled back. If any more transactions are required, a new transaction must be started. Here, a new transaction is started and the two rows are reinserted.

In[14]:=

This uses SQLCommitTransaction to commit the data permanently.

In[17]:=

Out[18]//TableForm=

A transaction is closed when it is committed. If any more transactions are required, a new transaction must be started. In addition, once a transaction has been committed, it cannot be rolled back. Transactions may be split up using an SQLSavepoint, a rollback can be made to a specific savepoint.

The following begins a transaction and inserts some data.

In[19]:=

A savepoint is created.

In[21]:=

Out[21]=

Here some more data is inserted into the database.

In[22]:=

Out[23]//TableForm=

The transaction is rolled back to the savepoint using SQLRollbackTransaction.

In[24]:=

This shows that the last insert has not taken place.

In[25]:=

Out[25]//TableForm=

This drops the TEST table and closes the connection.

In[26]:=