Skip to main content

Customizing the order of posting changes to database

There are some use cases where you may need to control the order of posting changes to database( in other words, control the order in which the entity objects in the 'pending changes list' are processed ). This situation may arise when you want to delete a specific record and modify another record with deleted records attribute values for which 'uniqueness constraint' is defined at database level. Please note that by default, when you commit the transaction the entity objects in the pending changes list are processed in chronological order, in other words, the order in which the entities were added to the list( Read more... ).
All goes well when user opts to delete a record first, and as next step modify an existing records with deleted records attribute values. In this case deleted record gets added to the pending changes list first, followed by the modified one. There are some interesting scenarios where you may need to outsmart the business users to make it work. Please see the below shown scenario, where a business user updates employees records in a specific sequence.

1. User modifies name field of an employee record with id=10 .
2. He/She selects employee record with id=20 and delete the same .
3. Navigate back to employee record id=10 and amend the email column with the email of the deleted record (note : email column is having 'unique constraint' at db level)
4. Tries to commit the changes. This operation throws 'uniqueness constraint' violation error as the transaction manager tries to perform the update operation on table before delete.

A bit about the implementation

A possible solution for the use case is to control the post order of entities, i.e. post the deleted entities first, followed by the new/modified entities. You can customize the post changes by adding your own customized oracle.jbo.server.DBTransactionImpl class. Idea is to reorder the TransactionPostListenersList(pending changes list) to keep the deleted entities at the beginning.

To hook your version of DBTransactionImpl, you may need to create DatabaseTransactionFactory which in turn should return the customized DBTransactionImpl class. As last step, modify the bc4j.xcfg file to enable the ApplicationModule to use the custom DatabaseTransactionFactory. Please refer this link - Creating and Using a Custom DBTransaction Implementation - to learn more on this topic.

<?xml version = '1.0' encoding = 'UTF-8'?>
<BC4JConfig version="11.1" xmlns="">
   <AppModuleConfigBag ApplicationName="model.AppModule">
      <AppModuleConfig name="AppModuleLocal" ApplicationName="model.AppModule" 
          DeployPlatform="LOCAL" JDBCName="HRConn" jbo.project="model.Model">
         <Security AppModuleJndiName="model.AppModule"/>

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS2 + HR Schema]


JCFlack said…
Of course, in Oracle RDBMS, you could make the unique constraint INITIALLY DEFERRED, so it isn't checked until the COMMIT.
Jobinesh said…
thanks JCFlack for pointing it out ! Yes, that works with those RDBMS which supports Deferred Constraints. However, objective of this post was to give an insight on the customization of 'posting order' :) [which need not be focused on this specific case alone]

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…