SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-47
Committing Database Changes if No Errors Occur
database changes are rolled back. For more information about handling exception
conditions, see Handling Exception Conditions on page 3-60.
Committing Database Changes if No Errors Occur
The COMMIT WORK statement permanently commits changes made to the database
within the current transaction and ends the transaction. It frees resources held by the
transaction, such as row or table locks.
#sql {COMMIT WORK};
If the SQLJ program changes the database, either issue a COMMIT WORK statement
at the end of each transaction or turn on autocommit when you establish a JDBC
connection. If you quit a program without committing a transaction, database changes
are automatically rolled back.
For the syntax of the COMMIT WORK statement, see the SQL/MX Reference Manual.
Undoing Database Changes if an Error Occurs
The ROLLBACK WORK statement undoes changes made to the database within the
current transaction and ends the transaction. It frees resources held by the transaction,
such as row or table locks.
Within the catch block that handles SQL exceptions from the transaction, use one of
these approaches for rolling back database changes:
•
Nest try and catch blocks for the ROLLBACK WORK statement:
catch (SQLException se) { /* Catch SQL exceptions */
try {
#sql {ROLLBACK WORK}; /* Roll back database changes */
}
catch (SQLException se) { /* Catch SQL exceptions from
ROLLBACK WORK statement */
System.out.println("Failure to roll back changes: " +
se.getMessage());
}
System.out.println("SQL exception: " + se.getMessage());
}
The nested try block contains the ROLLBACK WORK statement, and the nested
catch block handles SQL exceptions from the ROLLBACK WORK statement.
•
Invoke a method that rolls back database changes:
rollbackWork();
The method contains a ROLLBACK WORK statement. For details, see
Example 4-1 on page 4-42.
For the syntax of the ROLLBACK WORK statement and more information about
transaction management, see the SQL/MX Reference Manual.