Administration and Programming Guide for OS/400

Managing Transactions in a Net.Data Application

When you modify the content of a database using insert, delete, or update statements, the modifications do not become persistent until the database receives a commit statement from Net.Data. If an error occurs, Net.Data sends a rollback statement to the database, reversing all modifications since the last commit.

The way in which Net.Data sends the commit and possible rollback depends on how you set TRANSACTION_SCOPE and whether you specify the commit explicitly in the macro. The values for TRANSACTION_SCOPE are MULTIPLE and SINGLE.

MULTIPLE

Specifies that Net.Data will execute all SQL statements before a commit and possible rollback statement is issued. Net.Data sends the commit at the end of the request, and if each SQL statement is issued successfully, the commit makes all modifications in the database persistent. If any of the statements returns an error, Net.Data issues a rollback statement, which sets the database back to its original state. MULTIPLE is the default if TRANSACTION_SCOPE is not set.

To activate this commit method set TRANSACTION_SCOPE to MULTIPLE.

For example:

@DTW_ASSIGN(TRANSACTION_SCOPE,"MULTIPLE")
 

SINGLE

Specifies that Net.Data issues a commit statement after each successful SQL statement. If the SQL statement returns an error, a rollback statement is issued. Single transaction scope secures a database modification immediately; however, with this scope, it is not possible to undo a modification using a rollback statement later.

To activate this commit method, set TRANSACTION_SCOPE to SINGLE. For example:

@DTW_ASSIGN(TRANSACTION_SCOPE,"SINGLE")
 

You can issue a commit statement at the end of any SQL statement in your macro by using the COMMIT SQL statement. By leaving TRANSACTION_SCOPE set to MULTIPLE and issuing commit statements at the end of those groups of statements that you feel qualify as a transaction, you the application developer maintain full control over the commit and rollback behavior in your application.

To issue an SQL commit statement, you can define a function that you can call in at any point in your HTML block:

%FUNCTION(DTW_SQL) user_commit() {
  commit
%}
 
...
 
%HTML {
  ...
  @user_commit()
  ...
%}