Using Transactions

You use transactions with ADS to guarantee that changes that you want to apply to two or more records are performed in an all or none fashion. You begin a transaction using the BEGIN TRAN or BEGIN TRANSACTION keywords. Once the transaction has been started, you execute one or more queries to make changes to the data in your tables.

Note

The Advantage Local Server (ALS) ignores transaction-related instructions. In order to use transactions, you must use ADS.

If the changes can be applied successfully, you issue the COMMIT WORK statement to make those changes permanent and to end the transaction. If at least one of the SQL statements failed, you issue a ROLLBACK WORK statement. ROLLBACK WORK restores any changes made during the transaction and ends the transaction.

From the perspective of ADS, it does not matter whether the transaction is controlled using SQL or calls to the Advantage Client Engine (ACE) API—both are equivalent. For example, you can begin a transaction using BEGIN TRAN, and end a transaction by calling the AdsCommitTransaction function of the ACE API.

For most developers, there are two approaches that you can take when using transactions in SQL. The first is to use a single SQL script, and the other is to use multiple SQL statements, executed one after the other.

When using a single SQL script, you begin the transaction, perform one or more queries, and then commit the work. The following is an example of how a script like this might look:

BEGIN TRAN; INSERT INTO TEMP1 VALUES('Gordon Hall', NULL, 1000, True); INSERT INTO TEMP1 VALUES('Marti Schultz', NULL, 1000, True); COMMIT WORK;

So long as the transaction is begun within the SQL script, if an error occurs within the script, the transaction is automatically rolled back. If no errors occur, the COMMIT WORK statement completes the transaction.

If you do not use a SQL script where the work is performed under a transaction and committed, it is the responsibility of your client application to test whether each of your individual SQL statements work, and to explicitly commit or roll back the transaction, depending on whether or not an error is encountered.

For example, in Delphi, Visual Basic.NET, or C#, you trap for any exceptions raised during the attempt to execute SQL statements after initiating a transaction. If an exception is caught, your code should send a ROLLBACK WORK SQL command to ADS. This looks like the following:

ROLLBACK WORK

If none of the queries that you execute following the beginning of the transaction raise an exception, your code needs to submit a COMMIT WORK instruction, as shown here:

COMMIT WORK

You can use SET TRAN or SET TRANSACTION to automatically start a transaction whenever an INSERT, UPDATE, or DELETE statement is sent in the transaction. You follow SET TRAN with one of three autocommit settings: AUTOCOMMIT_ON (automatically begins transaction and commits completed transaction), AUTOCOMMIT_OFF (automatically begins transaction, but you must then commit or roll back the transaction), and EXPLICT (returns SET TRAN to default state in which you must explicitly begin and commit transactions). For example:

SET TRAN AUTOCOMMIT_ON; INSERT INTO TEMP1 VALUES('Sue Alan', NULL, 1000, True);

There are a couple of important points to note about transactions. First, you cannot initiate a transaction on the data dictionary administrator’s connection (ADSSYS). Attempting to initiate a transaction from the administrative connection will cause an error.

Second, ADS permits only one transaction per connection. In other words, once you begin a transaction you must commit it or roll it back before you can initiate another transaction.

Finally, it is important to keep in mind that transactions should be kept brief. Never wait for user input after beginning a transaction. If you need user input, get it before you initiate the transaction, so that your code can start the transaction, apply changes, and commit or roll back as quickly as possible.

In the next chapter you will learn how to access metadata, as well as perform maintenance tasks on your data dictionary using SQL.