Java Programming with Oracle SQLJ

3.4. Transactions

In the previous chapter, I explained the concept of database transactions and how to use the SQL COMMIT statement to make permanent any changes you make to the database. I also showed how to use the ROLLBACK statement to undo changes made in a transaction. The same concepts apply to SQL statements executed from your SQLJ programs.

To issue the SQL COMMIT and ROLLBACK statements in a SQLJ statement, use the following syntax:

#sql { COMMIT [WORK] };
#sql { ROLLBACK [WORK] };

The syntax elements are as follows:

COMMIT

Commits a transaction, making the changes permanent.

ROLLBACK

Rolls back a transaction, returning the database to the state it was in when the transaction first began. The effects of all SQL statements issued during the transaction will be erased.

WORK

An optional word that is part of the supported SQL syntax.

This example performs an INSERT statement followed by a ROLLBACK statement:

#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    ('7', 'John', 'Smith', '01-JAN-1970', '650-555-1212')
};

#sql { ROLLBACK };

Here, one row is inserted into the customers table, and that insert is undone as a result of the ROLLBACK statement.

3.4.1. Auto-Commit

By default, you must issue a COMMIT statement at the end of each transaction to make the changes permanent. However, it is also possible to have SQLJ automatically perform a commit after each DML statement. This can be convenient if your transactions consist of only one ...