Transactions in Open SQL/SQLJ (SAP Library

Transactions in Open SQL/SQLJ

Use

A transaction is a set of SQL statements that defines a logical unit of work that should be executed completely, or not at all.

When executing your SQLJ code in the J2EE Engine, you can choose to use the transaction management functions that the server provides. However, you may also demarcate boundaries of a local transaction programmatically using the SQLJ connection context.

Activities

To demarcate a local transaction in your SQLJ code, first you need to disable the autocommit mode of the underlying JDBC connection, which you use to communicate with the database. You can get the connection object from the SQLJ connection context. For more information, see Combining SQLJ and JDBC.

Example

#sql context DemoCtx with (dataSource = "jdbc/DEMO");

DemoCtx ctx = new DemoCtx();

Connection conn = ctx.getConnection();

conn.setAutoCommit(false);

Disabling the connection autocommit mode. The invocation of the setAutoCommit(false) method notifies the database transaction manager that the following SQL statements should not be committed to the database separately, but as a part of a single transaction.

To complete the transaction and save all changes on the database, you must issue a commit statement.

Example

#sql [ctx] { COMMIT WORK };

Commit. The pending transaction on the connection context ctx is committed.

To complete the transaction and undo all changes on the database, you must issue a rollback statement.

Example

#sql [ctx] { ROLLBACK WORK };

Rollback. The pending transaction on the connection context ctx is rolled back.