ASA SQL Reference
  SQL Statements

PREPARE TO COMMIT statement

Description 

Use this statement to check whether a COMMIT can be performed successfully.

Syntax 

PREPARE TO COMMIT

Usage 

The PREPARE TO COMMIT statement tests whether a COMMIT can be performed successfully. The statement will cause an error if a COMMIT is impossible without violating the integrity of the database.

Permissions 

None.

Side effects 

None.

See also 

COMMIT statement

ROLLBACK statement

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported in Adaptive Server Enterprise.

Example 

The following sequence of statements leads to an error because of foreign key checking on the employee table.

EXECUTE IMMEDIATE
   "SET OPTION wait_for_commit = 'on'";
EXECUTE IMMEDIATE "DELETE FROM employee
   WHERE emp_id = 160";
EXECUTE IMMEDIATE "PREPARE TO COMMIT";

The following sequence of statements does not cause an error when the delete statement is executed, even though it causes integrity violations. The PREPARE TO COMMIT statement returns an error.

SET OPTION wait_for_commit= 'ON';
DELETE
FROM department
WHERE dept_id = 100;
PREPARE TO COMMIT;