Transaction Processing System Functions

Understanding Transaction Processing System Functions

EnterpriseOne uses the following system functions for transaction processing:

• Begin Transaction
  
• Commit Transaction
  
• Rollback Transaction
  

The following screen shows you the transaction processing system functions that display when you choose to attach a system function to an event rule:

Figure 25-1 Transaction Processing System Functions

To use transaction processing in reports, you must enable the Transaction Processing property.
To enable Transaction Processing:

1. Report Design Aid, open the report for which you want to use transaction processing.
   
2. Click the File menu, select Report Properties, and then select the Advanced tab.
   

Figure 25-2 Properties: Advanced Tab

Ensure that the Transaction Processing option is selected.

25.1.1 Begin Transaction

Call the Begin Transaction system function to start a manual transaction. Calling this system function initiates the beginning of the table updates that are part of the transaction. This system function has one MathNumeric type parameter named Transaction ID. You can use a Report Level Math Numeric variable to pass into the Transaction ID parameter. Even though you must pass in a value for Transaction ID, the value is not currently being used by the system. This is because at any one time, only one transaction can be started from Event Rules.
After calling the "Begin Transaction" system function, the report can use Table I/O statements or Business Functions to conduct database table operations.
To include the Table I/O statements in the current transaction.

1. Click the system function you have assigned to the event rule.
   
2. Click the Table I/O icon.
   
3. On Insert Table IO, in the Advanced Operations section, select Open, and then click Next.
   
4. On Data Source, click the Advanced Options button.
   
5. On Advanced Options, click the Include in Transaction option, and then OK.
   

Figure 25-3 Advanced Options: Include in Transaction Option

25.1.2 Commit Transaction

Call the Commit Transaction system function to commit all the database operations started since the Begin Transaction call. Similar to Begin Transaction, this system function takes in a Transaction ID parameter. You can use the same variable as the one used in Begin Transaction system function.
EnterpriseOne will commit all database operations started between the Begin Transaction call and the Commit Transaction call. If the Commit Transaction fails, EnterpriseOne automatically rolls back the database operations so that none of the database operations will be committed. There is no need for Event Rules (ER) to call Rollback Transaction after Commit Transaction.
You need to carefully define the location of the Begin Transaction and Commit Transaction calls. The transaction boundary must be just big enough to include all relevant database operations and not too large that it blocks other code from accessing the same database records.
The example below shows how to use transaction processing through Event Rules:

// Start Example
Begin Transaction (transaction ID)
 
Table IO_Open Table 1
 
Table IO_Insert Table 1
 
Table IO_Open Table 2
 
Table IO_Open Table 2
 
Commit Transaction (transaction ID)
// End Example

25.1.3 Rollback Transaction

Call this system function to cancel all the database operations started since the Begin Transaction and the End Transaction. This system function takes a MathNumeric type parameter called Transaction ID. Use the same variable as used in Begin Transaction. The EnterpriseOne will end the transaction without committing any database operations started between the Begin Transaction and Rollback Transaction calls. You should only use this system function when absolutely necessary.
System Variable - SV TP Commit Status
This system variable tells you the status of the Commit Transaction or Rollback Transaction call. It may contain one of the four valid values:
CO TP_ACTION_FAIL
If SV TP Commit Status is equal to CO TP_ACTION_FAIL, then the last transaction action has failed. The last transaction action can be either Commit Transaction or Rollback Transaction. If the last transaction is Commit Transaction then the Commit Transaction has failed and no database operation has been committed. If the last transaction is Rollback Transaction, then the rollback operation has failed. No database records have been committed.
CO TP_ACTION_SUCCESS
If SV TP Commit Status is equal to CO TP_ACTION_SUCCESS, then the last transaction action has succeeded. The last transaction action can be either Commit Transaction or Rollback Transaction. If the last transaction is Commit Transaction, then the commit has succeeded and all database operations have been committed. If the last transaction is Rollback Transaction, then the rollback operation has succeeded.
CO TP_IN_TRANSACTION
If SV TP Commit Status is equal to CO TP_IN_TRANSACTION, then the transaction has been started, but no Commit Transaction or Rollback Transaction system function has been called yet.
CO TP_NO_TRANSACTION
If SV TP Commit Status is equal to CO TP_NO_TRANSACTION, then the no transaction has not been started or completed by the Transaction Processing system functions.
Event Rules Sample Using System Variable TP_Commit_Status

Begin Transaction (transaction ID)
 
Table IO_Open Table 1
 
Table IO_Insert Table 1
 
Table IO_Open Table 2
 
Table IO_Open Table 2
 
Business function inserts into Table 3 in a separate transaction.
 
Commit Transaction (transaction ID)
 
if (SV TP_Commit_Status is equal to CO TP_ACTION_FAIL)
{
        // this means the transaction failed (system has rolled it back)
// call another Business Function to roll back Table 3 since Table 3 Insert 
// is done outside of current transaction
}
else 
{
        // notify user transaction successful